How to tell when db was restored? 1

[equus2]

Hi,

I have a nightly process that copies a backup from one server to another then restores it by overwriting the existing database. I don't drop it first.

However, when I check the newly restored db's properties in Managment Studio, it has an old "create date". How come it doesn't show the date of the most current restore??

I have other ways of checking if the restore completed (log files) but I would prefer to see the correct date in the properties. I can also see the restore happening in real-time.

I can swear the "create date" used to be updated after the restore (because I've seen it), but now it isn't updating the create date. What's going on?

Thanks

Thanks

 

[SQLBill]

I believe the answer is:

Because you are restoring an existing database and not creating it. The database was created on that date regardless of where you move/restore it to.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[mrdenny]

You can check on the restore data using the restorehistory table in the msdb database. It keeps track of each database restore which is done within the instance.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[equus2]

Thanks.........

alter PROCEDURE GetLastRestoreDate
AS
BEGIN

SELECT * FROM msdb..restorehistory
ORDER BY destination_database_name, restore_date DESC

END

 

[mrdenny]

That will do the trick.

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog