This month’s T-SQL Tuesday is being hosted by Kenneth Fisher (t | b). T-SQL Tuesday (#TSQL2SDAY) is a blog party founded by Adam Machanic (t | b). Each month a member of the community hosts the party and selects the topic for us to write about.
This month’s topic is backup and recovery. Backups are one of the most common things DBAs discuss, and they are at once one of the simplest and most complicated parts of our whole job. So let’s hear it for backup and recovery!
As SQL DBAs, most of us are tasked with setting up database backups. There are many ways to skin a chicken and just as many ways to back up your SQL Server databases. You can use SQL Server backups, TSM, Backup Exec, or many other tools. And within each of these options you can create full backups, transaction log backups, differential backups, and bulk log backups.
It is almost an art setting up the appropriate backup solutions for your SQL databases. Each server, instance, and database is unique and backup solutions need to be created according to the data needs. Does the data change a lot? How critical is the data? Will you lose business if you lose data? These are just a few questions DBAs address when setting up their backup plans.
Some DBAs spend a lot of time developing and tweaking backup schedules for all of their databases. However, creating the best backup schedule will get you nowhere unless you periodically restore your databases and verify your backups. You must test restoring your backup files on a regular basis to make your backup solution is complete and that you will be ready whenever a disaster happens. Would you replace the batteries in your flashlight then not turn on the flashlight to make sure it worked? No…probably not. Verifying the restoration of your database is like turning on the flashlight. It is the final step in the backup process but a step that many DBAs do not complete.
“Your backups are only as good as your last restore.”
You might have 30 days of backups (full, transaction log, differential, etc) on hand but when the time comes that you need to restore a database, if there is a problem, the number of days back you go will be insignificant. All you are doing is spending money on storage for something that does not work. Turn on the flashlight, take that final step.
You need to schedule your database restore testing throughout the year, restoring your databases once every 6-12 months. Your database restore test schedule will have a direct correlation to how critical the data is to your business. A good starting point is to test each production databases at least once every 6 months, but the schedule you create will depend on the importance of the data and if a loss of data would create a loss of business. I have heard of some DBAs who test the database restore every two weeks for hyper critical databases. Set up a regular schedule according to your needs and continue testing.
It is hard to balance the time spent restoring databases with your other daily tasks, so create a recurring schedule that makes you, your boss, and/or the data owners comfortable. “The restore worked two years ago when I tested it” or “the backup ran last night and we have backups for the last 30 days” probably won’t go over well with your boss when you are in the middle of a disaster and you can’t restore the database from the backup. A schedule should be developed, agreed upon, followed, and documented.
Restoring data is the job of a DBA. A good DBA will verify all of the steps for data backup and recovery. A great DBA will have a schedule for verifying the steps and will continuously test their database restores and prepare for that day we all hope will never come. Or, as I like to say, backup, restore, repeat.