Backups are something everyone says they are doing, but what good are backups if you are unsure whether or not you can actually restore from them?
While a scenario such as losing your entire environment from a mass hardware failure, natural disaster, or malicious actor is rare, that is certainly not the time that you want to discover your backup strategy is incomplete or insufficient.
The question then is how do you know your backup strategy will successfully, and wholly, get your business-critical environment operational when all you have is a product of your current backup strategy? Easy: backup restore tests.
In this blog post, I want to focus specifically on your SQL Server database backups. By implementing a regular practice of restoring your databases from backups, you can check to ensure your backups are sound and keep your I.T. team apprised of the steps required to restore databases from backups so disaster recovery is quick and accurate when you need it most.
Typically, a backup operation in SQL Server simply runs every bit of data from the database out to a file that can restore the database should no traces of the original database exist. This is a simple and common full backup operation; however, despite turning on verify and checksum options, you cannot truly know if everything will restore flawlessly until you restore the database from this backup. While today’s virtualization technologies offer a decent layer of abstraction as well as fault tolerances at the storage layer, it is still possible that a backup file created last week may have gotten corrupted between then and when you need it to restore.
It is best practice to restore your databases from backups at least once a week. You might even consider planning on mock restoring a copy of your entire production environment every so often and ensuring your applications will work based on the steps you follow in your disaster recovery documentation. By going through this exercise, you can easily identify gaps in your recovery documentation; furthermore, your I.T. team will be familiar with how things should play out in the event of an actual disaster.
While restoring an entire environment can be difficult from a hardware perspective, you should at least strive to restore your databases regularly. Another advantage to doing this is being able to spot corruption early and before your backups age out. You do not want to discover an issue you cannot recover from because your means of recovery was deleted due to backup retention policies.
Since a full backup restores the database exactly as it was from the source environment, you can run consistency checks against the restored copy, which may impact performance, it is therefore ideal to perform these tests on a restored backup on your test environment so there is less pressure on your production environment.
A typical backup restore test involves taking a production full backup taken recently and restoring the database to a test environment; a consistency check is performed against this restored copy and any errors found can be reported and remediation of the production instance can be planned & implemented.
Don’t forget to test backups that are stored offsite as well!
