Skip to content

The Last DBCC CHECKDB Date and Restores

November 28, 2012

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:

dbcc1

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).

dbcc2

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.

dbcc3

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.

About these ads

From → Blog

One Comment
  1. Great to see another method used to check the last known good. Here was my contribution
    http://jasonbrimhall.info/2012/11/20/last-known-good-checkdb/

Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 4,515 other followers

%d bloggers like this: