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; GOBut 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:
- A database backup (which is encrypted).
- The certificate file (on disk).
- The private key file (on disk).
- The private key decryption password.
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.