Backups are probably the first, most important thing a DBA can do for his company’s data. Without backups, any number of problems can cause data loss, and significant data loss can be devastating to a company. Of course, without testing those backups, they’re just bits on a disk, and may or not be useful. Testing those backups regularly allows the DBA the practice of restoring so the steps are ingrained when they’re needed for a real recovery, plus it ensures that the backups themselves are good.
Sensitive data in the databases, of course, needs to be encrypted, so that only authorized users have access to that data. We’ve seen too many cases of the wrong people getting access to personal information that leads to fraud and identity theft, so encrypting that data is also critical to a company’s success.
Testing restores of database backups, with encrypted data in the database, becomes a bit more complicated, and it’s important to remember a few things before attempting to restore from backups with encrypted data in SQL Server.
First, and this is really important, back up the service master key and database master key for each database which uses SQL Server encryption. It’s not hard – here are the steps:
use master GO BACKUP SERVICE MASTER KEY FROM FILE = 'C:\MyDirectory\ServerSMK.key' ENCRYPTION BY PASSWORD = 'UD58ss6r' GO use MyDatabase GO BACKUP MASTER KEY FROM FILE = 'C:\MyDirectory\MyDatabaseMK.key' ENCRYPTION BY PASSWORD = 'UD58ss6r' GO
Copy these files to somewhere safe, where you know you’ll be able to find them should you find you need to recover the data in these databases.
Now, should you find that you need to recover the database, you can restore the database from backup, then issue the following commands to restore full access to the encrypted data:
use master GO RESTORE SERVICE MASTER KEY FROM FILE = 'C:\MyDirectory\ServerSMK.key' DECRYPTION BY PASSWORD = 'UD58ss6r' GO use MyDatabase GO RESTORE MASTER KEY FROM FILE = 'C:\MyDirectory\MyDatabaseMK.key' DECRYPTION BY PASSWORD = 'UD58ss6r' ENCRYPTION BY PASSWORD = 'ccH4QvQCp8Ry6nYSsVxZ5oU' FORCE GO
Disaster recovery without testing doesn’t amount to much, so it’s a good exercise for many reasons.