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 3^rd 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

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!