I learned something new today while I was listening to one of the courses on corruption authored by the master, Paul Randal(B/T).

In the course Paul mentioned that you can actually rollback DBCC CHECKDB REPAIR by using it within a Transaction.

This is something which is absolutely new to me, and I decided to test this stuff for some fun.

Why we need to rollback a repair?

The short answer is – There is no specific need to rollback a repair, but technically this will work and its possible.

Here is a quick demo on how this works -

We will be using a database named DBCC_PLAY which is already corrupt,and the corruption is for pageID 288.  An extract from DBCC CHECKDB output is mentioned below.

Msg 8928, Level 16, State 1, Line 55
Object ID 245575913, index ID 0, partition ID 72057594040549376, alloc unit ID 72057594045792256 (type In-row data): Page (1:288) could not be processed. See other errors for details.

If we try to do a read(SELECT) from one of the tables in the database, then that will also throw an error.

--SELECT DATA FROM THE TABLE
SELECT * FROM Test_Table1
GO

Msg 824, Level 24, State 2, Line 35
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xdf2e6f16; actual: 0xdfaeef16). It occurred during a read of page (1:288) in database ID 5 at offset 0x00000000240000 in file ‘C:\SQLDATA\DBCC_PLAY.mdf’. 

Before we try to do a repair, we will bring the database to single user mode.

--SET DB SINGLE_USER FOR REPAIR
ALTER DATABASE DBCC_PLAY SET SINGLE_USER
GO

Now we will run the repair in a transaction, but wont do a commit.

--REPAIR IN A TRAN DEMO
BEGIN TRAN
GO
DBCC CHECKDB('DBCC_PLAY',REPAIR_ALLOW_DATA_LOSS)
GO
--ROLLBACK TRAN

DBCC REPAIR does its job, and the error has been repaired

Msg 8928, Level 16, State 1, Line 65
Object ID 245575913, index ID 0, partition ID 72057594040549376, alloc unit ID 72057594045792256 (type In-row data): Page (1:288) could not be processed. See other errors for details.

The error has been repaired.
Msg 8939, Level 16, State 98, Line 65
Table error: Object ID 245575913, index ID 0, partition ID 72057594040549376, alloc unit ID 72057594045792256 (type In-row data), page (1:288). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 10249 and -4.

The error has been repaired.

We can run DBCC CHECKDB to be sure.

--CHECK CORRUPTION
DBCC CHECKDB('DBCC_PLAY') WITH NO_INFOMSGS;
GO

Command(s) completed successfully.

We can also do a SELECT from the table and see if that’s working too.

--SELECT DATA FROM THE TABLE
SELECT * FROM Test_Table1
GO

(832 row(s) affected)

SELECT is also working,and we now dont have any logical consistency error.

Now comes the fun part. Lets ROLLBACK the transaction which we had started for the repair.

ROLLBACK TRAN

Command(s) completed successfully.

Lets run a DBCC CHECKDB

--CHECK CORRUPTION
DBCC CHECKDB('DBCC_PLAY') WITH NO_INFOMSGS;
GO

Gosh, corruption is back !

Msg 8928, Level 16, State 1, Line 55
Object ID 245575913, index ID 0, partition ID 72057594040549376, alloc unit ID 72057594045792256 (type In-row data): Page (1:288) could not be processed. See other errors for details.

Can we do a SELECT from the table? Lets try

--SELECT DATA FROM THE TABLE
SELECT * FROM Test_Table1
GO

Nope, there comes the logical consistency error

Msg 824, Level 24, State 2, Line 35
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xdf2e6f16; actual: 0xdfaeef16). It occurred during a read of page (1:288) in database ID 5 at offset 0x00000000240000 in file ‘C:\SQLDATA\DBCC_PLAY.mdf’. 

This clearly shows that you have the ability to roll back a repair.

Lets clean up things, by repairing the database and committing it.

--REPAIR IN A TRAN DEMO
BEGIN TRAN
GO
DBCC CHECKDB('DBCC_PLAY',REPAIR_ALLOW_DATA_LOSS)
GO
COMMIT TRAN

Command(s) completed successfully.

Run a DBCC CHECKDB to ensure that there is no more corruption.

--CHECK CORRUPTION
DBCC CHECKDB('DBCC_PLAY') WITH NO_INFOMSGS;
GO

Command(s) completed successfully.

Thanks for reading and keep watching this space for more !