I ran across a question on Twitter recently where someone asked about the DBCC CHECKDB date after a restore. For those of you that don’t know this, you can run this command on your instance (in a database)

DBCC DBINFO WITH tableresults;

This returns a lot of information, but I’ve circled one value below:

This is the last known good DBCC date that exists for this database. It’s also the value in the error log from the execution of DBCC CHECKDB on my instance early this morning (Arrow marks the entry).

If I were to restore this database, what happens to this value? I’d expect that it would be restored to the last value that was contained in the backup file. That would make sense to me, but let’s test it.

First I run a backup:

BACKUP DATABASE [db1] TO  DISK = N'C:\SQLBackup\db1_20121115.bak' WITH NOFORMAT, NOINIT,  NAME = N'db1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

This should have the last DBCC date inside the file. I know run a CHECKDB and note the time in the error log.

This returns successfully (whew, didn’t want to test corruption restores). If I then run DBINFO again, I get the current date from the error log returned. Now let’s restore from my backup.

This completes and when I run DBCC DBINFO again I find the dbccLastKnownGood date is reset back to the 12:04am value instead of the 9:11am value.

That’s what I expect, and that was what a few other people confirmed on Twitter. It’s logical that this should be the behavior, but you never know until you’ve tested it.

Filed under: Blog Tagged: administration, sql server, syndicated