SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL, Sandy, Backup & Recovery - Have a Restore Plan

Reporting from NY, East Coast, USA:  In the wake of hurricane Sandy, or as re-classified, post-tropical cyclone Sandy (whatever that is), we slowly begin to recover from the devastated regions.  My thoughts and well-wishes go out to all those in my local area and other areas affected by this monster storm. (Including another “post-tropical cyclone” in Southern India)

Listening to all the news on the storm as it occurred; one New York hospital thought it was well-prepared with backup generators.  Unfortunately, in the time of disaster those generators did not come online, and failed.  Therefore, they needed to be evacuated to another facility.

Let me talk business continuity for a moment.  It is a simple concept often overlooked, and entails procedures and processes to ensure the continuance of business, usually in a disaster.  The technology employed to achieve business continuity encompasses a degree of both High Availability and Disaster Recovery.

While I won’t drill down on HA/DR in-depth in this blog, clearly there are a number of options available in SQL Server 2012 (and earlier) to achieve this.  Out of the box, you will find Mirroring, Replication, Log Shipping and Availability Groups.  Failover clustering, SAN-to-SAN replication and Virtualization all are technologies that can be employed in your High Availability/Disaster Recovery plan as well.

However, when all else fails, remember backups are the last line of defense.  Backup and Restore is the classic fallback to any disaster recovery situation.

It’s easy to create a backup maintenance plan, and never restore it – until – disaster strikes.  This is not an optimal position to be in as a DBA, nor a very good career move.  Therefore, don’t just have a backup plan, have a restore plan as well.  The restore plan must be incorporated into your overall backup strategy.

So, as related to the SQL Server world, it’s not enough to have a backup of your database(s), but you must test make sure they can be restored successfully.  You also want to ensure that you database backups are not corrupt.

Just because your backup jobs are completing successfully, it doesn’t necessarily mean you have a good backup.  So, it is important to remember, a completed backup, does not mean a backup that is free of corruption.  In a situation where you must restore from backup, “oops, sorry the backup is corrupt” is not a valid response to your manager, boss, or end-users.

Sadly, most folks do not validate their backups.  At the recent Red Gate SQLIntheCity event in New York, a city badly hit by super storm Sandy, I spoke about business continuity and the need to ensure your backups can successfully be brought online.  This means not only ensuring the integrity of your backups, but restoring them on a regular basis. Verification is one of the most crucial steps in any DBA’s backup plan.

So, how do we ensure that our backups are corruption free?  As of version 2005, SQL Server introduced a new improved option over torn page detection called page checksum.   When checksum is enabled on databases SQL Server computes the checksum of the page whenever a page is read from disk to memory or written to disk from memory.

When SQL Server reads the page, it re-computes the checksum comparing it with the page header values. If the checksum value matches then it is assumes the page did not get corrupted during the read/write process.

BACKUP CHECKSUM will compute a combined CHECKSUM of all pages and stored on the the backup media. This value can be used to re-compute the CHECKSUM before restoring to make sure the backup itself is valid. It’s straightforward to tell if the backup has any corruption during a restore, or using a restore verify only.  Backup checksum uses same algorithm as used by page checksum.  You can choose to generate backup checksum during a backup which can then later be used during restore to validate that the backup is not corrupt. Additionally, the page checksum, if available, can be verified during backup and recovery.

However, there is a caveat of using backup checksums that BOL (BooksOnLine) talks about: “Due to the overhead verifying and generating backup checksums, using backup checksums poses a potential performance impact. Both the workload and the backup throughput may be affected. Therefore, using backup checksums is optional. When deciding to generate checksums during a backup, carefully monitor the CPU overhead incurred as well as the impact on any concurrent workload on the system. ”

 Without checksum, checking the integrity of your database requires running DBCC CHECKDB.  This operation can also be quite I/O intensive with performance overhead on your production databases.  Therefore, the recommendation is to create a process, whether automated or manual, to restore your database, and run DBCC CHECKDB against it.  In an article I wrote on MSSQLTips.Com, I discuss specific ways to Minimize Performance Impact of SQL Server DBCC CheckDB.  This includes discussion on backup and restore, backup with checksum and other options.

Full backup verification requires running a full restore + DBCC CHECKDB. This can be time-consuming and resource-heavy, and is often easy to put off, despite the potentially negative consequences.

There are many 3rd party tools that can help automate verification.  One that I worked with, RedGate’s SQL Backup Pro 7 streamlines the process of backup, restore, and verification. Offering step-by-step scheduling wizards, the new features make verification a natural, easy step in your normal backup procedure, which is fully integrated.

With SQLBackup Pro, you can quickly and easily set up regular restores from SQL Backup that will automatically restore the latest backup (to another server) which allows you to Offload resource-intensive DBCC CHECKDB.: restore your backups to any SQL Server for verification, to take the load off production.

Finally, if you’re looking for a great resource on Backup and Restore, then you need to check out and get a great new book by my friend Tim Radney (blog|twitter), and a great addition to the  Joes2Pros Series, SQL Backup and Recovery Joes 2 Pros: Techniques for Backing up and Restoring Databases in SQL Server.  As per Tim, “Backups and restores are the core foundation of a DBA’s job”, and this reference will provide great help DBAs everywhere. Available now on Amazon, Tim’s book is one that all DBAs should have at their fingertips.


I’m SQL Server MVP, Robert Pearl, and ask you to follow me on twitter @Pearlknows, and keep watch for a very exciting campaign, no not the 2012 election, but one that is designed to build awareness in the SQL Server community to keep your servers humming.  Please contact us at
rsp05@pearlknows.com, to learn how you can get your 15-point SQL Server HealthCheck report!


No comments.

Leave a Comment

Please register or log in to leave a comment.