Problem
One issue when creating backups for SQL Server databases is that these backup files can be restored to any other SQL Server as long as the version of SQL Server supports the backup file that you are trying to restore. In most cases this is not an issue unless you are trying to restore a backup from 6.5 or earlier onto a SQL Server 7.0 or later installation. Because of this ability to restore the backups to any other SQL Server this exposes your data to potential theft or misuse of your data. Most of the data in backup files is highly compressible, so a backup that maybe 1GB can easily be compressed to about 200MB. So even your largest backup files could be vulnerable to someone copying them to their laptop or burning them to a DVD and then using the data in a way that you never intended.
Solution
One option that native SQL Server backups has is the use of password protected backup files. When you create your backup you can specify a password for the backup file. Then when you want to restore the backup, the password must be specified otherwise the backup fails. One problem with using this feature to protect your backups is that the GUI, both Enterprise Manger and SQL Server Management Studio do not support this option. Also, when you create backups using maintenance plans this is not a supported option either. So the only way to create password protected backups is by using the T-SQL commands for both backup and restore functions.
To do this it is very straight forward, here is the T-SQL command to backup your database:
BACKUP DATABASE Northwind TO DISK='C:\Northwind.BAK' WITH MEDIAPASSWORD='mssqltips'
Once the backup has been create we can run restore commands to see that the file can not be accessed without the password.
RESTORE FILELISTONLY FROM DISK='C:\Northwind.BAK'
RESTORE HEADERONLY FROM DISK='C:\Northwind.BAK'
RESTORE VERIFYONLY FROM DISK='C:\Northwind.BAK'
RESTORE DATABASE Northwind FROM DISK='C:\Northwind.BAK'
If we specify the password for any of these commands the commands work without issue.
RESTORE FILELISTONLY FROM DISK='C:\Northwind.BAK' WITH MEDIAPASSWORD='mssqltips' RESTORE HEADERONLY FROM DISK='C:\Northwind.BAK' WITH MEDIAPASSWORD='mssqltips' RESTORE VERIFYONLY FROM DISK='C:\Northwind.BAK' WITH MEDIAPASSWORD='mssqltips' RESTORE DATABASE Northwind FROM DISK='C:\Northwind.BAK' WITH MEDIAPASSWORD='mssqltips'
When trying to use the GUI to do a restore, if the backup is password protected you will get these error messages:
SQL Server 2000
SQL Server 2005, 2008, 2008R2
Take a look at using passwords to protect your backup files. Although it does not offer the highest degree of security it does at least offer one additional hurdle someone will need to overcome in order to do the restore.
Note: Beginning with SQL Server 2012, the PASSWORD and MEDIAPASSWORD options are discontinued for creating backups. It is still possible to restore backups created with passwords.
Next Steps
- Although this does add a level of security if someone really wants to crack the passwords they will find a way, so look for additional ways to secure your data
- You should make sure your backup files are not in a location that is easily accessible to people that should not have access to these files
- Make sure that people do not have the ability to create backup files if they are not supposed to. The password option has be issued, so if someone can create a backup without the password and they can easily do a restore too.
- Keep your passwords in a secure place. If you don’t remember the password you will not be able to restore the backup file.
