November 2, 2012 at 4:10 pm
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.
November 2, 2012 at 4:23 pm
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.
November 2, 2012 at 4:44 pm
That's for 2008 R2. If you look at it for 2005, it does not specify that.
November 9, 2012 at 4:08 pm
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
November 9, 2012 at 4:08 pm
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
November 9, 2012 at 4:36 pm
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.
November 9, 2012 at 4:48 pm
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
December 4, 2012 at 3:15 am
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
December 4, 2012 at 11:27 am
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
December 10, 2012 at 4:51 am
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.
December 11, 2012 at 10:05 am
Hemant.R (12/10/2012)
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.
Please re-read the first sentence of the original post.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 11, 2012 at 10:41 am
We're running SQL 2005 Enterprise Edition. We restore a backup of a 100gb db every day in the early morning hours.
The scheduled job starts at 3:45am each day; the value I see in RESTORE_DATE is 3:45:04am (+-3 seconds).
This db does NOT get restored in 4 seconds; it takes about an hour.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply