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

1 comment:

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

Post a Comment