Sometimes you need to protect your precious backups. Creating encrypted backups isn’t very difficult, but it is a bit hard to scale unless you have a 3rd party solution. Let’s check out this feature that has been available for SQL Server Standard, Enterprise and BI edition since 2014. You can do restore of encrypted backups to any edition.

The lab I work in is fairly simple. Two SQL servers (LAB01-SQL01 & LAB02-SQL01) and the WideWorldImporters test database placed in one of the servers. I have written some text for you if you want to create labs quickly.

Create certificate

Before you can encrypt backups you have to create a certificate, which is a fairly straight forward task. There are plenty of blogs to provide excellent descriptions on how to do this, and I will provide a few posts in the sources below. If you put something like the TSQL lines below together you create a master key and a certificate.

USE [master];
GO

--Create a database master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'SomePass1'; 
GO

--Create a backup certificate
CREATE CERTIFICATE [DatabaseBackupCertificate]
WITH SUBJECT = N'Database Backup Encryption Certificate'; 
GO
You can adore your newly created certificate in SQL Server Management Studio if you have some time to spare.

If you immediately perform a backup with encryption, you will get your backup without a hitch, and both TSQL and SSMS will do just fine.

BACKUP DATABASE [WideWorldImporters] TO DISK = 'X:\BackupShare\WWImport-Encrypted.bak' 
WITH COMPRESSION, ENCRYPTION ( ALGORITHM = AES_256, SERVER CERTIFICATE = [DatabaseBackupCertificate]), STATS=10; 

Your backup will be created as you probably expect, but you will also get a warning which I recommend you take proper note of:

Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.

So as this message points out you will be able to do restore to the same server without much hassle, but when you try to restore the database to other servers you will be in pain (if you haven’t exported the right things).

When doing a restore I hope you never see this error after a server has gone belly up…

To avoid problems in the future, lets quickly export the certificate and the private key and store them somewhere secure and safe.

BACKUP CERTIFICATE [DatabaseBackupCertificate] 
TO FILE = 'X:\Backupshare\DatabaseBackup.cert'
WITH PRIVATE KEY (
FILE = 'X:\Backupshare\DatabaseBackup.key',
ENCRYPTION BY PASSWORD = '1ComplexPasswordThatYouRemember.')

There. Now we can lower our sholders and breathe normally again.

Good day, mr. Hallengren!

I am assuming that you already are using one of the tools I advocate that every DBA should have in their tool belt, namely Ola Hallengren’s fantastic maintanance solution. I will not go into great (or really any) detail on how to use the solution, but Ola Hallengren has all the documentation you could ever need on his page and you can even check his code out in Github. Be sure that this is not the last I talk of this magnificent beast of a tool.

To “install” the maintenance solution on an instance, you just run the script you got from Ola Hallengren’s web page. The SQL Agent jobs that are set up by the maintenance solution script just needs a new schedule set up to run regularly. For encryption of the backup you need to add a few options to the default job, as shown below. When this is done, you are sending encrypted backup data to your default backup location (on a different drive, preferably on a different server and storage) in a regular fashion. Neat! The agent job should have a step that look something like this:

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@Directory = NULL,
@BackupType = 'FULL',
@Verify = 'Y',
@CleanupTime = NULL,
@CheckSum = 'Y',
@LogToTable = 'Y',
@Encrypt = 'Y',
@EncryptionAlgorithm = 'AES_256',
@ServerCertificate = 'DatabaseBackupCertificate'

And here are some pictures of how to make the new schedule and make sure you edit the step to add the encryption details.

Restore as usual

As illustrated earlier, backups are not worth the bytes occupied unless you can run a successful restore with said bytes. In other words: test your backups and make sure your recovery plan actually works. It is easier to follow a numbered list when you are stressed out – than it is to try to Google (or Duck Duck Go) while trying to desperately recall what the next steps might be.

In our case the recovery plan would be something like:

  1. Check if we can gather the tail of the transaction log. If the database is in simple recovery mode, you can safely skip this step, and assume some dataloss (prepare for outcome in step 5).
  2. Find the certificate and the private key you exported from the server and make sure you know (or can find where you stored) the password you set on the private key.
  3. Import the certificate and the private key to the server where you want to restore database
  4. Run the restore as you normally would
  5. Smile as you bathe in glory for saving the day – unless of course the database was supposed to have full recovery model. Then I suggest you have a distraction maneuver ready for when the “Why did we have dataloss”-question arise. And perhaps a small smoke bomb?

In TSQL your plan could look like this:

-- Create master key if not already created
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'AnotherComplexPassword!';
GO

-- Restore certificate (which you copied to this location)
CREATE CERTIFICATE BackupCertFromLab01
FROM FILE = 'C:\Lab\DatabaseBackup.cert'
WITH PRIVATE KEY (FILE = 'C:\Lab\DatabaseBackup.key',
DECRYPTION BY PASSWORD = '1ComplexPasswordThatYouRemember.');
GO

-- Restore database
RESTORE DATABASE [WWI-RestoreDB] FROM DISK = 'C:\Lab\WWI-Encrypted.bak'
WITH RECOVERY, STATS=10, 
MOVE 'WWI_Primary' TO 'C:\...longpath...\WWI_Primary.mdf',
MOVE 'WWI_UserData' TO 'C:\...longpath...\WWI_UserData.ndf',
MOVE 'WWI_Log' TO 'C:\...longpath...\WWI_Log.ldf',
MOVE 'WWI_InMemory_Data_1' TO 'C:\...longpath...\WWI_InMemory_Data_1';

Of course this immediately becomes a bit more complex as soon we have full recovery model and maybe hundreds of transaction log backups. Luckily we have some tools to fix that too, which I will look into next time.

Sources