Password Protect a backup file | SansSQL

Saturday, January 5, 2013

Password Protect a backup file

Password protection of databases backups helps a lot in protecting the database backup from misuse.
Once such case is, when you are sending a backup of database physically through disks which has very sensitive and critical data to a different office or data center.

To password protect backup file, you have include WITH PASSWORD option when backing up the database
-- Full Backup with password
BACKUP DATABASE SansSQL TO DISK = 'D:\Backup\SansSQL_FullBackup.bak' 
WITH PASSWORD = 'Password123'
Now, once the backup is taken with a password, the same password has to be provided while restoring for matching the decryption sequence.
--Restore Full backup with no recovery
RESTORE DATABASE SansSQL FROM DISK = 'D:\Backup\SansSQL_FullBackup.bak' 
WITH RECOVERY, PASSWORD='Password123'
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 ***"

4 comments:

Anonymous said...

1.This Password is case sensitive ??
2. is there any limited times on wrong password ??
3. what is the nest step / process to restore this back up in case of forgot the password ??

Genious Person said...

Positive site, where did u come up with the information on this posting? I'm pleased I discovered it though, ill be checking back soon to find out what additional posts you include. PC PC Folder Zona Software - Don't Devote Time Searching, Read through About Cellular Desktops About this webpage how to encrypt a attachment

Genious Person said...

Your article has piqued a lot of positive interest. I can see why since you have done such a good job of making it interesting. PC Secure Folder With Password - Don't Spend Time Seeking, Study About PC Desktop computers The following free usb password protection

Genious Person said...

This article is an appealing wealth of informative data that is interesting and well-written. I commend your hard work on this and thank you for this information. You’ve got what it takes to get attention. Free Folder Locking mechanism For PC PC - Don't Waste Time Looking, Read through Supposed to be about Mobile Desktops Here free usb password protection software

Post a Comment

Ads