Create a solid recovery plan.
A critical part of your backup process needs to include recovery procedures. You should have a rehearsed recovery plan, so if and when you need to recover any of your databases, following the exact steps is second nature to you. It’s also important to note that there are many other steps than just a database backup, so be sure to put together a comprehensive plan.
Keep tape and disk backups for restoration.
It’s best to keep the latest full set of backups on disk to ensure the fastest recovery of your databases. In most cases, a disk restore is always going to be faster than using tape. The general rule of thumb is: Use disks for recovery and tape for long-term storage.
Keep it current.
If you’re going through the recovery process, it just makes sense to have the most current backups that you can possibly have. When your backups are out of date, you’re likely to have to take more time to search for backups on tape – further dragging out your recovery time. It’s best to keep a full set of backups including the full, differential, and transaction log backups on disk. It’s also a good practice to keep two sets on the disk if possible.
Get your priorities in order.
It’s important to have a rehearsed plan for recovering your databases, but it’s just as important to set a priority order for the restore process. A priority order lets you tackle the most critical databases first. Plus, if there are databases that depend on other databases, they can be completed in an order that allows applications to function. It is impossible to do everything at once, so think through the order in which you will approach things during multiple failures, and set your priorities.
Don’t overlook applications that use multiple databases.
It’s not a common scenario for everyone, But occasionally applications may rely on more than one database to function. If this applies to your organization, you need to think through the recovery process for this type of recovery. Do all databases need to be recovered to the same point in time? If you have databases that are replicated, how does the recovery process impact replication? You also need to be conscious of special steps that might be needed to restore databases if you are using third-party applications that use SQL Server. Always consider your complete recovery and not just one database.
Back up the tail of the transaction log.
A final thing to consider is backing up the tail of the transaction log. This allows you to get additional transactions that have not been backed up yet, enabling the restoration of as much data as possible with absolute minimum data loss. This is an often overlooked step and something you should be familiar with in the case of a failure. It’s just like taking a transaction log backup – This may not be something you can always do, but you should know the steps just in case.
For more information on improving SQL server backup and recovery, read the whitepaper “Top SQL Server backup mistakes (and how to avoid them)”> by Greg Robidoux from MSSQLTips to learn more about improving SQL Server backups.