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

  • 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.

  • 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.

  • That's for 2008 R2. If you look at it for 2005, it does not specify that.

  • 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

  • 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

  • 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.

  • 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

  • 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

  • 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

  • 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.

  • 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

  • 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.


    Cursors are useful if you don't know SQL

Viewing 12 posts - 1 through 11 (of 11 total)

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