Password Protected A Database Backup File

            -------Starting With Create a Database---------
Note :-This process possible with only  commands not with (SSMS) wizard.

             [Use Master]
        Create database P_Protected
                       
             --Take a Full Backup with password---
BACKUP DATABASE P_Protected TO DISK = 'D:\P_Protected.bak'WITH PASSWORD = 'Demo'
---------------- Restore Database With Replace or NoRecovery Option---

Restore  DATABASE P_Protected from DISK = 'D:\P_Protected.bak'   With Replace,   norecovery, PASSWORD = 'Demo'
--------------------Last Step  Restore Database With Recovery-------------

Restore  DATABASE P_Protected from DISK = 'D:\P_Protected.bak'   With  Recovery , PASSWORD = 'Demo'

----------------------If You Try with Wrong Password then you get an Error --------
Restore  DATABASE P_Protected from DISK = 'D:\P_Protected.bak'   With Replace,   norecovery, PASSWORD = 'Demo1'


Question 1: What happens if you give a wrong password or try to restore without giving password?

Answer: The restoration will fail with the below error

Msg 3279, Level 16, State 2, Line 1  Access is denied due to a password failure  Msg 3013, Level 16, State 1, Line 1  RESTORE DATABASE is terminating abnormally.

Question 2: How will you come to know if a backup is password protected?
Answer: When you try to restore, it will give an error saying "Access is denied due to a password failure"
And when you try to execute RESTORE HEADERONLY, the file name will be shown as "*** PASSWORD PROTECTED ***"

RESTORE HEADERONLY FROM DISK='D:\PROTECTED.BAK'