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' 
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 ***"


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

Alex jon said...

That’s a nice site you people are carrying out there. chrome password manager

Kevin Dellinger said...

Hi, I think that I saw you visited my weblog so I came to “return the favor”. I'm attempting to find things to enhance my site! I suppose its ok to use some of your ideas!! Check this link right here now: How Not To Password Protect Folder.

Kevin Dellinger said...

Highly energetic post, I liked that bit. Will there be a part 2? Visit our website: How To Really Password Protect Folder

Post a Comment