Using Database Repair for Disaster Recovery

Every DBA will see corruption at some point in his or her career. Hopefully when you do, you’ll have a comprehensive set of backups that let you perform a fast, targeted set of restores with minimal downtime and data loss. Alternatively, if the most important business requirement is to minimize downtime, you might fail over to a synchronously updated, redundant system.

But what if you can’t do either? What if your backups are corrupt, your backups have been failing for months and no one noticed, or the piecemeal restore that you want to perform isn’t possible because you’re using the SIMPLE recovery model? There have been many articles and blog posts written about using backups to recover from corruptions (see my blog category Backup/Restore for lots of useful links), but very few about what to do when you don’t have backups or when your restore ­operations fail. In this article I’m going to walk you through an example of using the repair functionality built into DBCC CHECKDB, plus older backups, to attempt to salvage some data.

One thing to keep in mind is that there are an infinite number of scenarios involving database ­corruption, and at every stage in the repair process something could crop up that you haven’t seen before. It’s not possible to cover every eventuality in this article, but it will give a good grounding in the techniques used when performing a repair operation.

Scenario Setup

To practice these repair techniques, I ­created a simple example database that you can download. The database is called SQLMag2010 and simulates a simple sales database. I created the database, performed a full database backup, corrupted the database, and then performed another full database backup. The corruption is on one of the data pages in the sales table’s clustered index. Figure 1 shows the timeline of this scenario.

Figure 1: Timeline of a disaster scenario

The WinZip archive contains the two database backups. To set up the scenario, simply download and unzip the archive and restore the corrupt version of the database using the ­following code:

RESTORE DATABASE SQLMag2010

FROM DISK = N'C:\SQLskills\

            CorruptSQLMag2010.bak'

WITH REPLACE;

GO

This backup restores without errors because the backup wasn’t performed using the WITH CHECKSUM option. This option (introduced in SQL Server 2005) lets backup and restore operations test page checksums on pages in the database being backed up and restored, but without this option, no checks are performed. In production, it’s advisable to use this option when performing backups.

Also note that the database was created using SQL Server 2005, but you can restore it on SQL Server 2008 R2 and SQL Server 2008, and everything I describe in this article works the same in all three versions.

Using Database Repair

After you restore the database, run the following
query against the sales table to expose the corruption:

SELECT SUM (salesAmount)

FROM SQLMag2010.dbo.sales

WHERE salesAmount < $1.00;

GO

The error that's returned is shown in Web Figure 1.

Web Figure 1

In the case of a real corruption in your database, you should have some kind of monitoring in place so that you’re alerted when a user or application hits a high-severity error similar to the one shown in Web Figure 1. For example, you can create a SQL Agent alert based on the error severity. (See "Easy Monitoring of High-Severity Errors: Create Agent Alerts," for more ­information about how to do so.) You don’t want to rely on users notifying you or having to periodically look through the SQL Server error log or Windows event log to see these errors. Also, the sooner you know that you have corruption, the more likely you can recover with minimal downtime and data loss.

Generally, the first thing to do when you encounter corruption is determine how much corruption you’re dealing with. Only one tool will do that for you: DBCC CHECKDB. You need to determine the extent of the corruption so that you can figure out what your options are in terms of restore or repair (or potentially just fail over and deal with the corruption offline).

In any disaster recovery situation, you want only information about the corruptions, and you want to know about all the corruptions, so instruct DBCC to provide all this information using the ­following command:

DBCC CHECKDB (SQLMag2010) WITH ALL_                              ERRORMSGS, NO_INFOMSGS;
GO

The results from this command are shown in Web Figure 2. (Note that I removed some of the output to save space in the article. When you run it, you’ll see the full output.)

Web Figure 2

From the errors, it looks like a single page is damaged. At this point, you might choose to examine the page using the DBCC PAGE ­command (which, although undocumented, is perfectly safe on ­production systems—I wrote that code, and I told customers the same thing when I worked at Microsoft). Use the following code to produce a dump of the whole page:

DBCC TRACEON (3604);

DBCC PAGE (SQLMag2010, 1, 158, 2);

GO