Getting Database Creation Date Via T-SQL

Hey everyone! Welcome back to SQL Something!

The first post for April is going to be pretty simple. Today we are going to look at how to retrieve the date that a database was created via T-SQL. Could be useful in some situations. I, for instance, just wanted to know when a DB was swapped out for a empty one. The new empty DB wasn't put to use the same day/time it was created, so the timestamps of the actual data in the DB didn't exactly provide me with what I wanted.

Anyway, I digress. Let's look at what sys.databases can do in order to help with our problem.

Sys.databases (and its legacy counterpart sys.sysdatabases) allows you to access a little bit of database property info. In our case we are going to specifically look at two columns: Name and Create_Date.

The following query gets the date that all DBs were created:

SELECT Name, Create_Date
FROM Sys.Databases
ORDER BY Create_Date;

As with other queries, you can filter by a specific DB Name.

SELECT Name, Create_Date
FROM Sys.Databases
WHERE Name = 'Test'
ORDER BY Create_Date;

Lastly, if you want a quick way to tell when the service itself was last started you can query the creation date of the TempDB. This is because the TempDB is recreated every time the SQL Server service is started.

SELECT Name, Create_Date
FROM Sys.Databases
WHERE Name = 'tempdb'
ORDER BY Create_Date;

The DB name and creation date are just two of 60-something columns that you can access via sys.databases in order to get database info. Other noteworthy columns (in my small opinion) are: Compatibility_Level, Collation_Name, Is_Read_Only, State and State_Description, Recovery_Model and Is_Published. You can choose any that are relevant to you and your needs.


DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)