THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Michael Zilberstein

How to find real database creation date

Restoring database from backup or moving it to another server resets all the creation_date fields (in sys.databases, msdb..backupset etc'). So is there any way to find out the original creation date? Looks like there is one. It is stored in the boot page of the database (page 9) and you'll have to use DBCC PAGE in order to find it.

DBCC TRACEON(3604)
GO
DBCC PAGE (YourDBName, 1, 9, 3)
GO

In the bottom of the page you'll find dbi_crdate field - it is the original creation date.

Boot page fields are poorly documented (I'm being polite - actually, they aren't documented at all). And from the first glance looks like it contains interesting data. For example: dbi_firstSysIndexes, dbi_maxLogSpaceUsed. Here Paul Randal blogged about another column, dbi_dbccLastKnownGood, which is the last time DBCC CHECKDB ran without finding any corruptions in the database.

Published Tuesday, March 18, 2008 9:56 AM by Michael Zilberstein
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

jerryhung said:

Nice tip, I just ran it and worked perfectly

March 18, 2008 9:53 AM
 

Sankar Reddy said:

Michael,

Although I am not sure when will I need this specific information, I learned something new today. Thanks for the cool tip!

March 19, 2008 6:11 PM
 

prakashp said:

The information retrieved for Sys DBs are incorrect

April 25, 2008 11:54 AM
 

David Carr said:

Thanks - exactly what I wanted - worked perfectly.

May 31, 2010 8:57 PM
 

Amy Liu said:

I checked the name, you are wrong, It is restore date which you also could find it on property.

June 21, 2011 6:30 PM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement