Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Determining a database's completed restore date and time...


Determining a database's completed restore date and time...

Author
Message
SQL_ME_RICH
SQL_ME_RICH
Mr or Mrs. 500
Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)

Group: General Forum Members
Points: 526 Visits: 1588
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.
George M Parker
George M Parker
SSChasing Mays
SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)

Group: General Forum Members
Points: 660 Visits: 1469
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.
SQL_ME_RICH
SQL_ME_RICH
Mr or Mrs. 500
Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)

Group: General Forum Members
Points: 526 Visits: 1588
That's for 2008 R2. If you look at it for 2005, it does not specify that.
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8213 Visits: 14368
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
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8213 Visits: 14368
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
SQL_ME_RICH
SQL_ME_RICH
Mr or Mrs. 500
Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)

Group: General Forum Members
Points: 526 Visits: 1588
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.
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8213 Visits: 14368
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
Hemant.R
Hemant.R
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 240
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
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8213 Visits: 14368
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
Hemant.R
Hemant.R
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 240
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search