Post reply

To find the history of restored DB

  • July 26, 2012 at 7:04 am

    #278552

    Hi All,

    If I need to find the details or history of a restored DB, how can I do that? What I mean by history is that if the DB was restored from UAT/DEV/TESTING, environment, etc. Anyone have any script for this or clue as to how find this information? Please advise.

    Thanks in advance,

    TA

    Regards,
    SQLisAwe5oMe.

  • July 26, 2012 at 7:09 am

    #1517909

    select * from msdb..restorehistory

  • July 26, 2012 at 7:39 am

    #1517940

    This is a script I call

    "WhatGotRestoredFromWhereWhenAndByWho"

    SELECT

    DatabaseRestoredTo = RH.destination_database_name,

    TimeOfRestore = RH.restore_date,

    UserImplimentingRestore = RH.user_name,

    RestoreType = CASE RH.restore_type WHEN 'D' THEN 'Full DB Restore'

    WHEN 'F' THEN 'File Restore'

    WHEN 'G' THEN 'Filegroup Restore'

    WHEN 'I' THEN 'Differential Restore'

    WHEN 'L' THEN 'Log Restore'

    WHEN 'V' THEN 'Verify Only'

    END,

    ServerWhereBackupTaken = BS.server_name,

    UserWhoBackedUpTheDatabase = BS.user_name,

    BackupOfDatabase = BS.database_name,

    DateOfBackup = BS.backup_start_date,

    RestoredFromPath = BMF.physical_device_name

    FROM

    msdb.dbo.restorehistory RH

    INNER JOIN

    msdb.dbo.backupset BS

    ON

    RH.backup_set_id = BS.backup_set_id

    INNER JOIN

    msdb.dbo.backupmediafamily BMF

    ON

    BS.media_set_id = BMF.media_set_id

    ORDER BY

    RH.restore_history_id

  • July 26, 2012 at 2:18 pm

    #1518176

    Thanks Hadrian & Anthony.

    TA.

    Regards,
    SQLisAwe5oMe.

  • July 27, 2012 at 10:13 am

    #1518622

    anthony.green (7/26/2012)

    This is a script I call

    "WhatGotRestoredFromWhereWhenAndByWho"

    Good name! 🙂

  • July 29, 2012 at 2:30 am

    #1518913

    just use this to make our lives simpler.

    select * from msdb.dbo.restorehistory

Viewing 6 posts - 1 through 5 (of 5 total)

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