Password Protected Backup In SQL Server

Password protected database backup : Setting backup media password are important and useful for securing misuse of data. If database backup is password protected then one will not be able to restore and see details from backup file using RESTOREFILELIST or HEADERONLY command.

How to take password protected database backup?

Create A Database

If DB_ID('DBS_Test') Is null 
Create Database DBS_Test
Go

Backup Database Backup With Password

Backup Database DBS_Test To Disk ='D:\DBS_Test.BAK' With MediaPassword='password'
Go

Drop The Existing Database

Drop Database DBS_Test
Go

Try To Get FileList Info From Backup

Restore FILELISTONLY  From Disk='D:\DBS_Test.BAK' 

Output :

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 FILELIST is terminating abnormally.

Throws error since no password has been supplied with query.

Try To Get DB Info From Backup

Restore HEADERONLY  From Disk='D:\DBS_Test.BAK' 

Output:

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.

Same as above throws error since no password has been supplied with query.

Now Try To Restore Database With Backup File

Restore Database DBS_Test From Disk='D:\DBS_Test.BAK'

Output:

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 DATABASE is terminating abnormally.

So, you have seen that no details or info can be taken from password protected database backup.

How to Get Info and Restore Password Protected Database Backup ?

Get File List Info From Password Protected Database Backup

Restore FILELISTONLY  From Disk='D:\DBS_Test.BAK' 
With MEDIAPASSWORD='password'

Output: See Here

Get Database Info From Password Protected Database Backup

Restore HEADERONLY  From Disk='D:\DBS_Test.BAK' 
With MEDIAPASSWORD='password'

Output : See Here

Restore Database From Password Protected Database Backup

Restore Database DBS_Test From Disk='D:\DBS_Test.BAK'
With MEDIAPASSWORD='password'

Output :

Processed 168 pages for database 'DBS_Test', file 'DBS_Test' on file 1.
Processed 2 pages for database 'DBS_Test', file 'DBS_Test_log' on file 1.
RESTORE DATABASE successfully processed 170 pages in 0.214 seconds (6.197 MB/sec).