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

8 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 ??

Muhammad Hassan 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

Muhammad Hassan 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

Muhammad Hassan 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

Pawel Co Faddey said...

I am glad that I saw this post. It is informative blog for us and we need this type of blog thanks for share this blog, Keep posting such instructional blogs and I am looking forward for your future posts.
Cyber Security Projects for Final Year

JavaScript Training in Chennai

Project Centers in Chennai

JavaScript Training in Chennai

Post a Comment

Ads