Finding database creation time is simple if database has not been detached from server. You can find database creation time using below methods.
You can find the creation time of database using sp_hepldb system procedure.
EXEC sp_helpdb 'SqlAndMe'
This will return database creation time along with other details:
Using catalog view sys.databases:
Database creation time is also available in sys.databases catalog view:
WHERE name = 'SqlAndMe'
(1 row(s) affected)
However, if the database has been detached and reattached to server, the creation time will be changed to attach time. Also, if the database has been restored from a backup after dropping the database creation time will be changed.
Actual creation time of database is stored in the boot page of the database which is retained after restore or detach/attach. This is stored as dbi_crdate.
You can use DBCC PAGE to read creation time of database from boot page:
DBCC PAGE('SqlAndMe', 1, 9, 3)
Result Set (Trimmed):
dbi_createVersion = 661 dbi_ESVersion = 0
dbi_nextseqnum = 1900-01-01 00:00:00.000 dbi_crdate = 2011-11-07 21:12:46.357
dbi_filegeneration = 2
Hope This Helps!
Filed under: Catalog Views, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012, Undocumented Functions