Day 4 of 31 Days of Disaster Recovery: Back That Thang Up

Day 4 of 31 Days of Disaster Recovery: Back That Thang Up

Here we are at day 4 in my series focusing on disaster recovery. It’s Friday, so this will be a quick one for today. I want to talk about the importance backing up everything that needs it and point out some of the often overlooked things that you should be backing up.

If you missed any of the earlier posts in the series, you can check them out here:

31 Days of disaster Recovery
1. Does DBCC Automatically Use Existing Snapshot?
2. Protection From Restoring a Backup of a Contained Database
3. Determining Files to Restore Database

System Databases

I’ve said it before (T-SQL Tuesday #19 – Beyond Backups) and I’ll say it again. Back up your system databases: master, model, msdb, and distribution. You should also be backing up your resource DB as well. You can back up the resource DB (mssqlsystemresource.mdf and mssqlsystemresource.ldf files) by just copying them to your backup location. This can save you from losing a lot of critical data. If you lost master database and you have to rebuild master, you will lose everything in msdb and model as well. It can turn a difficult situation into a terrible one. Being a lazy an efficient dba means doing simple things (like backing up the system databases) to save yourself a lot of work later down the line.

Certificates and Master Keys

If you are using certificates for creating asymmetric keys, authorizations, or securing securables (like procedures), then you need to consider the ramifications if you need to re-apply the certificate. For example, if you need to move a database with encrypted data to a new server, you may need to decrypt and re-encrypt the data. You will need the original certificate to do perform this action. You can export the certificate to a file using the BACKUP CERTIFICATE command. Likewise, you should export the database master key to a file using the BACKUP MASTER KEY command.

The backed up certificates and master keys should be immediately removed from the server and stored in a safe, secure location. I like to store them in source control that is securely protecting (in a tree only the DBAs have access to) and is itself getting backed up. Additionally, if you use a password to secure the exported files, those passwords should be stored securely using whatever secure method you are using to protect service account passwords. Preferably in something that is secure and gets backed up in it’s own right.

SSRS Encryption Key

If you lose your SSRS instance and you need to connect a new one to the existing databases, you will need to import the original encryption key to be able to read sensitive data. If you encryption key is not available, you can generate a new encryption key, but it will wipe out all existing sensitive data. I was called in to consult on a scenario where someone had done this, and their reporting server had over 600 subscriptions added by users. They lost all of the user information related to those subscriptions. They had to track down the users and then manually re-enter their email addresses to fix the subscriptions. They also lost the passwords to all of their data sources for the reports, but that was minor to fix compared to the broken subscriptions.

So please ensure that you are exporting the encryption key to a file and storing it securely. Don’t leave it on the server itself. Move it to a secure location that gets backed up like source control.

SSAS Databases

Yes, there are databases inside Analysis Services, and yes, you can AND SHOULD back those up. You use XMLA to back up the SSAS databases. To automate this process, create a SQL job in the regular database engine that connects to SSAS and executes the XMLA code. It’s actually very simple to do.

Here are sample backup and restore commands in XMLA for reference:

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <Object>
        <DatabaseID>Adventure Works DW 2012</DatabaseID>
    </Object>
    <File>c:\bak\Adventure Works DW 2012.abf</File>
</Backup>

<Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <File>c:\bak\Adventure Works DW 2012.abf</File>
    <DatabaseName>Adventure Works DW 2012</DatabaseName>
    <AllowOverwrite>true</AllowOverwrite>
    <Security>IgnoreSecurity</Security>
    <DbStorageLocation xmlns="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\DATA\</DbStorageLocation>
</Restore>

Summary

The key point of today’s post was to get you thinking about more than just backing up your main user databases. Yes, those are key in terms of data loss potential, but keeping everything else that is critical is also key in how long you will be down if there is an outage and key in the amount of effort involved with making your system whole again. Please make sure you are backing up the system databases, certificates, master keys, encryption keys for SSRS, and the SSAS databases. If you’re lucky, an outage might not even get noticed. If that happens, you know you’re doing your job right.