Ways to Find out What Data Was Deleted by SQL Server Repair

Article
09/08/2016

Tip: Ways to Find out What Data Was Deleted by SQL Server Repair

In the unfortunate case where you have no choice but to use the REPAIR_ALLOW_DATA_LOSS option of DBCC CHECKDB, some data is inevitably lost. Your task becomes figuring out what data is lost so that it can be recreated or what other parts of the database are fixed up to reflect the loss.

Before running repair, you could try examining some of the pages that DBCC CHECKDB reports as corrupt to see if you can tell what data is on them. Consider the following error:
Server: Msg 8928, Level 16, State 1, Line 2
Object ID 645577338, index ID 0: Page (1:168582) could not be processed. See other errors for details.

You can try using DBCC PAGE to examine page (1:168582). Depending on how badly the page is corrupt, you may be able to see some of the records on the page and figure out what data is lost when the page is deallocated by repair.

After running repair, you may be able to tell what data has been deleted. Unless you are intimately familiar with the data in the database, you have two options:

Create a copy of the corrupt database before running repair so you can compare the prerepair and postrepair data and see what is missing. This may be tricky to do if the database is badly corrupt—you may need to use the WITH CONTINUE_AFTER_ERROR options of BACKUP and RESTORE to do this.
Start an explicit transaction before running repair. It is not very well known that you can run repair inside a transaction. After repair completes, you can examine the database to see what repair did, and if you want to undo the repairs, you can simply roll back the explicit transaction.

After the repair has completed, you may be able to query the repaired database to find out what data has been repaired. For instance, consider the case where a repair deleted a leaf-level page from a clustered index with an identity column. It may be possible to construct queries that find the range of records deleted, such as the following:

-- Start of the missing range is when a value does not have a plus-1 neighbor.
SELECT MIN(salesID + 1) FROM DemoRestoreOrRepair.dbo.sales as A
WHERE NOT EXISTS (
       SELECT salesID FROM DemoRestoreOrRepair.dbo.sales as B
       WHERE B.salesID = A.salesID + 1);
GO
-- End of the missing range is when a value does not have a minus-1 neighbor
SELECT MAX(salesID - 1) FROM DemoRestoreOrRepair.dbo.sales as A
WHERE NOT EXISTS (
       SELECT salesID FROM DemoRestoreOrRepair.dbo.sales as B
       WHERE B.salesID = A.salesID - 1);
GO

At the very least, after running a repair, you should take a full backup and perform root-cause analysis of the corruption to find out what caused it.

From the upcoming release from Microsoft Press, Microsoft SQL Server 2008 Internals (by Kalen Delaney, Paul S. Randal, Kimberly L. Tripp, Conor Cunningham, Adam Machanic, and Ben Nevarez). Due to be released on February 18, 2009.

Looking for More Tips?

For more SQL Server Tips, visit the TechNet Magazine SQL Server Tips page.

For more Tips on other products, visit the TechNet Magazine Tips index.

Additional resources