Restoring An Encrypted Backup Of [master]

Restoring a backup of [master] from one instance to another is challenging. The source and target instances have to be the same version. And after [master] is restored, the instance won't start if it can't find the files for [model] or the path for [tempdb] files. Those are just a few concerns that I can think of off the top of my head. Here's another one I've added to my list: encryption. No, I'm not using TDE, but I am using native backup encryption.

To restore [master], I would normally start SQL Server in single user mode:

sqlsvr.exe -c -m -s [InstanceName]

Then I would start the SqlCmd utility and run my restore database statement:

SqlCmd –S [server] –E

RESTORE DATABASE master
FROM DISK = 'D:\Backup\ATLB\master.1.bak',
 DISK = 'D:\Backup\ATLB\master.2.bak'
WITH REPLACE, RECOVERY;
GO

But with an encrypted backup, the restore fails: SQL can't find the certificate needed to decrypt the backup.

This strikes me as an odd chicken-and-egg problem. I'd need to create the certificate to decrypt the [master] backup on the instance I'm restoring [master] to...and the certificate is stored in [master], which I'd be overwriting. As weird as it sounds, this is exactly what needs to happen. Maybe it's not as complicated as it sounds.

It's A Good Thing

It may take me a while to become comfortable with the process, but in the end, it's what I need to keep my data secure. To (ahem) "acquire" my data and restore it elsewhere, someone would need several things:

1. A database backup (which is encrypted).
2. The certificate file (on disk).
3. The private key file (on disk).
4. The private key decryption password.

I keep the certificate file and private key file separate from the database backups, for reasons that are (hopefully) obvious. Sure, even if an attacker had both of those files, they'd still have to come up with the password somehow. I'm not going to do them any favors, though.

What would happen if I couldn't restore an encrypted backup of [master]? I'd have to start taking unencrypted backups of [master]. And that's the database where the certificate resides. If an attacker nabbed a copy of the unencrypted [master] backup, they wouldn't need 2, 3, and 4. They could generate those themselves by exporting the certificate to file, along with a private key, using any password they wish. That would essentially render encryption of all my other backups useless. To prevent that hypothetical situation, I'd have the burden of keeping [master] backups isolated away from all of the other backups. Thankfully, I don't have to do that.

Yep, the process is a little weird. And I'm grateful for it.