Salvaging Good Data from a Damaged Database by Using Restoration Techniques

We’ll be frank: once a database is “seriously” damaged, the odds are that you are going to lose! So first and foremost, we should stress that there is no substitute for an appropriate database backup strategy.

A number of DBCC repair commands exist, which we will cover in Chapter 5, “Designing a Strategy to Maintain a Database Solution,” but you should not rely upon them repairing a damaged database. There are no guarantees in life. Likewise, there are no guarantees that DBCC will be able to repair the damage.

So what options do you have of salvaging good data from a damaged database? In this chapter, you will investigate two techniques, both of which are new to SQL Server 2005. The first involves restoring damaged pages. The second involves using the emergency repair mode, with the intention of salvaging as much data as possible.

Note 

Chapter 5 covers the DBCC commands in detail.

Restoring Pages

A new feature of SQL Server 2005 allows you to restore individual pages in your database. This feature can be particularly useful for VLDBs where you would rather not have to restore, for example, a 666GB database for the sake of a single corrupt 8KB page.

Note 

Page restores are available only for databases that are using the FULL or BULK_ LOGGED recovery model.

In fact, you can perform page restores while the database is still online!

Note 

Online page restores are available only in the Developer and Enterprise Edi-tions of SQL Server 2005.

SQL Server 2005 keeps track of damaged pages in the msdb system database in the [suspect_pages] table. SQL Server 2005 can detect damaged pages during any of the fol-lowing operations:

• A user queries data that is on the damaged page.

• A DBCC CHECKDB command is run.

• A DBCC CHECKTABLE command is run.

• A DBCC CHECKFILEGROUP command is run.

• A backup operation is run.

• A restore operation is run.

To restore pages in SQL Server 2005, you need to perform the following actions:

1. Determine the page_id for the damaged page(s) from the [msdb].[dbo].[suspect_ pages] table.

2. Restore the latest database/file group/differential backup set that contains the damaged page using the PAGE = 'file:page [ ,...n ]' option.

3. Restore any differential backups, if available.

4. Restore all subsequent transaction log backups.

5. Perform a transaction log backup of the database.

   Note 

   This transaction log backup should contain the final LSN of the restored page.

6. Restore the log backup made in step 5.

   Tip 

   If a nonclustered index page has been damaged, you should consider rebuilding it instead of initiating a page restore.

Using Emergency Mode Repair

If a database is seriously damaged and you have no up-to-date backups, you can attempt an emergency repair. You do this by setting the database state to EMERGENCY by using the ALTER DATABASE statement and then running the DBCC CHECKDB command using the REPAIR_ ALLOW_DATA_LOSS option.

When a database is in emergency mode, the DBCC CHECKDB command can perform some “special repairs.”

Warning 

The purpose of a repair operation is not to salvage as much data as it can but to get the database into a structurally consistent state for subsequent processing without causing further damage.

The emergency mode repair involves the following actions:

1. Forcibly recovering as much as it can from the transaction log, skipping any errors encountered.

2. If any errors were encountered, the transaction log is rebuilt.

   Warning 

   Rebuilding the transaction log in this fashion will result in a database that is in a transactionally inconsistent state.

3. Running a DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS option.

Essentially, you want the emergency mode repair to result in a database that can then be accessed to retrieve data from.

Note 

For more information about emergency mode repair, search for the “Resolving Errors in Database Emergency Mode” topic in SQL Server 2005 Books Online.

Warning 

The emergency mode repair is really the last resort. There is nothing really that can be done if it doesn’t work for you.