Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

The Last DBCC CHECKDB Date and Restores

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.


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

Comments

Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...