Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Determining a database's completed restore date and time... Expand / Collapse
Author
Message
Posted Friday, November 2, 2012 4:10 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 17, 2014 5:02 PM
Points: 485, Visits: 1,368
I am familiar with the various queries that can be run to return information out of the 'msdb' database for the last date a database was restored, but what I am not certain about is whether the 'restore_date' column from the dbo.restorehistory table is logging when the restore began or when it completed.

Sorry if this is just right under my nose, and I am missing something very obvious here, but I have searched the WEB, BOL, etc, and cannot find anything definitive on this.

Here is a sample of the query I am running...

SELECT *
FROM dbo.restorehistory
WHERE destination_database_name = 'MyDatabase'
ORDER BY restore_date DESC

There are obviously much better ways of writing for such information, but you hopefully get the gist of what I am trying to do here.

If anyone can say definitively if it is the start date for the restore, or when the restore completed, that would be great to know. I am learning towards thinking that it is the time the restore started and not completed, but I just want to be sure - and if my assumption is correct - is there a way to query for when the restore completed for a given database?

Thank you in advance!

SQL_ME_RICH

P.S. No 3rd party tools in use here. These are native backups being restored from, to an Enterprise Edition installation of SQL Server 2005.
Post #1380622
Posted Friday, November 2, 2012 4:23 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 8:15 AM
Points: 652, Visits: 1,428
If anyone can say definitively if it is the start date for the restore, or when the restore completed, that would be great to know.


http://msdn.microsoft.com/en-us/library/ms187408(v=sql.105).aspx

restore_date datetime Date and time of the completion of the restore operation. Can be NULL.
Post #1380625
Posted Friday, November 2, 2012 4:44 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 17, 2014 5:02 PM
Points: 485, Visits: 1,368
That's for 2008 R2. If you look at it for 2005, it does not specify that.
Post #1380631
Posted Friday, November 9, 2012 4:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:06 PM
Points: 7,125, Visits: 12,720
The 2008 (non R2) docs say exactly what the 2005 docs say. I would venture a guess that 2005 - 2008 R2 hold the logical same value per the 2008 R2 docs.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1383288
Posted Friday, November 9, 2012 4:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:06 PM
Points: 7,125, Visits: 12,720
I guess I would ask...why is it so critical to know?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1383289
Posted Friday, November 9, 2012 4:36 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 17, 2014 5:02 PM
Points: 485, Visits: 1,368
Hi opc...It was a timing thing to determine how long a database would be available that we are doing slow boat to china copy jobs from. It's a copy of our PROD system, but is refreshed on a nightly basis. I was able to determine that both 2005 and 2008 R2 does show when it is completed, just as you suggested...I just didn't want to assume so.
Post #1383295
Posted Friday, November 9, 2012 4:48 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:06 PM
Points: 7,125, Visits: 12,720
SQL_ME_RICH (11/9/2012)
Hi opc...It was a timing thing to determine how long a database would be available that we are doing slow boat to china copy jobs from. It's a copy of our PROD system, but is refreshed on a nightly basis. I was able to determine that both 2005 and 2008 R2 does show when it is completed, just as you suggested...I just didn't want to assume so.

Good to know. BOL is amazingly complete in terms of its general coverage of the product but the wording in BOL can be frustratingly ambiguous when considering nuances like what you highlighted. Thanks for posting back.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1383300
Posted Tuesday, December 4, 2012 3:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 3, 2014 4:05 AM
Points: 47, Visits: 236
hi,


you will get info in below way..
right click on database go to reports standard reports the backup and restore events
here u will get option successful restore operation this
Post #1392321
Posted Tuesday, December 4, 2012 11:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:06 PM
Points: 7,125, Visits: 12,720
Hemant.R (12/4/2012)
hi,


you will get info in below way..
right click on database go to reports standard reports the backup and restore events
here u will get option successful restore operation this

Good to know. But what does it have to do with the initial concern:

I am not certain about is whether the 'restore_date' column from the dbo.restorehistory table is logging when the restore began or when it completed.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1392640
Posted Monday, December 10, 2012 4:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 3, 2014 4:05 AM
Points: 47, Visits: 236
hi ,

whenever you restore any database ,that activity related info got stored in

restorehistory table of msdb

plz check with using this

select destination_database_name,USER_NAME,restore_date from restorehistory order by restore_date desc


if ,i m wrong plz tell me.
Post #1394547
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse