SQL Server TDE - Database backups and post restore behaviour Below is my SQL Server details:


SQL Server 2014 Enterprise SP2 GDR with Availability Group configured.
List item


NOTE: The Databases are FileStream Enabled.

Below are the series of actions I did recently and need your help to understand and correct myself:


Enabled TDE (Structured Data .mdf &amp; .ldf) 
Enabled Encryption File System (EFS) for Unstructured data
After both the configurations , now the Database is on TDE Enabled and EFS Enabled in AOAG .
I took a backup of the Database (From what I have learnt  post TDE &amp; EFS the backup of the Database is encrypted as well using Database Encryption Key Algorith . Is this point correct ?)


I removed the Database from AOAG. Later deleted the database from all AG Replicas and restored it using encrypted file taken above and joined them to AG again.



My Actual question is :


Post taking a backup of the database by using Encrypted file above and restoring it on the same server , the database is now freed from both TDE &amp; EFS , Can some explain me what happened here ? 
Why the Database is not encrypted when I used a encrypted Database Backup file ! 
Does the backup file post restoration gives decrypted files automatically by default behavior ?


Current the Is_encrypted value for the database is now "1" earlier before retore it was "3".

How can I make sure (or) is there way at all when I restore a encrypted backup I can see the database is encrypted as well ? Is my thinking wrong , kindly share some insights on this point?
I believe there is more to the story than we're getting, here.


  
  I took a backup of the Database (From what I have learnt post TDE &amp; EFS the backup of the Database is encrypted as well using Database Encryption Key Algorith . Is this point correct ?)
  


In this case, the database is still encrypted using TDE - however if you've changed the volume that the backup is on which isn't protected by EFS then EFS isn't in play. EFS is like TDE, but for the filesystem - so once you leave the protection boundary, it's no longer protected.


  
  Post taking a backup of the database by using Encrypted file above and restoring it on the same server , the database is now freed from both TDE &amp; EFS , Can some explain me what happened here ?
  


I don't follow and there should be more to this that we're missing. Just restoring the database on the same server would require that the server certificate for TDE be available (which, since it's the same server it should be available) so the restore can take place. If you've chosen to MOVE the file location to a location not protected by EFS then EFS would no longer be in the picture. If you restored over the same database in the same location, then both TDE and EFS should bein effect. Regardless, though, TDE will still be in effect.


  
  Why the Database is not encrypted when I used a encrypted Database Backup file !
  


It is, so we're missing part of the story. If it's saying it isn't, then something else occurred after the RESTORE DATABASE command to disable TDE.


  
  Does the backup file post restoration gives decrypted files automatically by default behavior ?
  


No, that's why something else had to have happened. The database should still be TDE encrypted, whether or not EFS depends on the where the files were restored.


  How can I make sure (or) is there way at all when I restore a encrypted backup I can see the database is encrypted as well ?


I'm not sure I follow, again, as a backup of a TDE database is encrypted. If you restore it, it's still encrypted. Period. If this happens each and every time just running RESTORE DATABASE and nothing else, then more data capture will be needed. If you're using a 3rd party tool, you'll want to ask what they do and their process or grab some tracing during the restore to look into what they are doing.