Create Backup File with Password protection

Create Backup File with Password protection

Sometime it is necessary to set password in backup file. If backup is password protected than we cannot restore the database or see details without knowing the password. Now to add password protection we have to add "With MediaPassword='DBPassword' " at the end of TSQL where 'DBPassword' is password we set for the backup. Now during restore we have to add this at the end also.


TSQL to backup database with password
Backup Database TEST To Disk ='C:\Temp\TEST.BAK' With MediaPassword='DBPassword'  
GO


Now we cannot restore this database or get details of database from this backup without knowing the password.
We will get the below error:



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

Now to restore the database run below command:

Restore Database TEST From Disk='C:\Temp\TEST.BAK' With MEDIAPASSWORD='DBPassword'  

To resore details of the backup run below command

Restore HeaderOnly From Disk='C:\Temp\TEST.BAK' With MEDIAPASSWORD='DBPassword'