Database Simplified Headline Animator

Database Simplified

Friday, 28 October 2011

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).

No comments:

Post a Comment