Create Date

Question

Post reply

Create Date

Tara-1044200

SSCoach

Points: 15951
More actions
January 7, 2010 at 9:04 am

#137943

Restoring database from backup or moving it to another server resets all the creation_date fields (in sys.databases, msdb..backupset etc').
Is there a way i can list creation date of all databases in my sql server?

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736479 More actions · Answer 1

sys.objects for a system table in each database? So something like:

SELECT create_date

FROM sys.objects

WHERE name = 'sysrowsets'

Run for each db?

Tara-1044200 SSCoach Points: 15951 More actions · Answer 2

is there a way i can get dbname,size and original creation date of all databases in the server ?

Steve Jones - SSC Editor SSC Guru Points: 736479 More actions · Answer 3

You could use a cursor, which is what I might recommend. You could get a list of dbs.

declare mycursor Cursor

FOR

SELECT NAME

FROM sys.databases

DECLARE @name varchar(20)

, @cmd VARCHAR(200)

OPEN Mycursor

FETCH NEXT FROM Mycursor INTO @name

WHILE @@fetch_status = 0

BEGIN

SELECT @cmd = 'select '''+ @name + ''', f.size, o.create_date from ' + @name+'.sys.database_files f inner join ' + @name + '.sys.objects o on f.type_desc = ''rows'' and o.name = ''sysrowsets'''

--SELECT @cmd

EXEC(@cmd)

FETCH NEXT FROM mycursor INTO @name

end

DEALLOCATE mycursor

-- SELECT * FROM sys.database_files

-- SELECT * FROM sysobjects

that's quick and dirty, but it should help. You could insert the data into a temp table inside the cursor, then select it at the end.

GRE (Gethyn Ellis) SSCrazy Eights Points: 9602 More actions · Answer 4

Borrowing Steve's query the below will return DB name and create date

exec sp_msforeachdb 'select ''?'', create_date FROM sys.objects where name = ''sysrowsets'''

Gethyn Elliswww.gethynellis.com

Tara-1044200 SSCoach Points: 15951 More actions · Answer 5

I dont see any difference, how can i get this in a single list, may be inserting in a temp table ?

Is this date original creation date, does it change when its moved to another server or restored ? The reason i am asking is from this query i am getting 80% of the databases with same date and time.

Steve Jones - SSC Editor SSC Guru Points: 736479 More actions · Answer 6

The dates for the objects should remain the same across a restore. It does on my test instance.

You can use a temp table to insert the data insert of selecting it in the execute.

Tara-1044200 SSCoach Points: 15951 More actions · Answer 7

I dont think the date is correct, i get 2005-10-14 01:36:06.690

for all the dataabses in my server. even for the database which i created yesterday.

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 8

Use this.

select [NAME],CREATE_DATE from master.sys.databases

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

Tara-1044200 SSCoach Points: 15951 More actions · Answer 9

Bru

that is not the original date it gets updated when i restore or move it from another server.

Daxesh Patel Hall of Fame Points: 3208 More actions · Answer 10

small correction to SSC Eights's script. Adding "use ?"

exec sp_msforeachdb 'use ? select ''?'', create_date FROM sys.objects where name = ''sysrowsets'''

This should give you create date for each database.

297t73g SSChasing Mays Points: 630 More actions · Answer 11

I used this script, and I know from hard notes on the histories, these create dates I see on my server are not correct. Sql 2008 STD RTM.

homebrew01 SSC Guru Points: 55548 More actions · Answer 12

Side note: I put [] around the ? like [?] in case there are any dashes or other characters in db names. We have a couple such as My-Database and the code will fail without it.

297t73g SSChasing Mays Points: 630 More actions · Answer 13

Thanks. I use that also in cases like you mentioned, but I see no errors, just really wromng dates. Like most are all the same date and time, with a few different, but with years before the servers even existed, etc.