Backup file database was restored from

  • Hello!

    I am trying to find out name of the backup file database was restored from (I am restoring from DISK), something similar to physical_device_name in backupmediafamily. restorehistory doesn't have this information.

    Any advice is greatly appreciated.

    Thanks,

    Igor

  • The full path of the restore is logged in the servers Application Event log as well as the SQL Logs. Look for EventID 18267.

  • I wanted to collect/analyze this information through TSQL. I think I came with solutions (thanks to help from other user):

    SELECT h.destination_database_name,

    h.restore_date,

    h.user_name,

    h.restore_type,

    f.destination_phys_name,

    fg.filegroup_name,

    mf.physical_device_name

    FROM msdb.dbo.restorehistory h,

    msdb.dbo.restorefile f,

    msdb.dbo.restorefilegroup fg

    , msdb.dbo.backupset b

    , msdb.dbo.backupmediafamily mf

    WHERE h.restore_history_id = f.restore_history_id

    AND h.restore_history_id = fg.restore_history_id

    AND b.backup_set_id = h.backup_set_id

    AND b.media_set_id = mf.media_set_id

    ORDER BY --h.destination_database_name,

    h.restore_date DESC --latest on top

  • Hello Igor,

    I routinely take backups from production & restore to up to four dev servers with different hardware & OS. I created this script in master on each of the dev servers to find out which file was restored & when. In my case, each db has a single filegroup. HTH

    CREATE PROC dbo.procLastRestoreDetails

    (

    @database_name AS varchar(50) = NULL

    )

    AS

    SELECT

    BS.database_name,

    RH.restore_date AS Last_Restored_DateTime,

    RF.destination_phys_name AS Restored_To_Database_Location,

    BMF.physical_device_name AS Restored_From_Backup_File

    FROM

    msdb.dbo.restorehistory RH

    INNER JOIN msdb.dbo.restorefile RF

    ON RH.restore_history_id = RF.restore_history_id

    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

    WHERE

    RF.destination_phys_name LIKE '%.mdf'

    ANDRH.backup_set_id =

    (

    SELECT

    MAX(backup_set_id)

    FROM

    msdb.dbo.restorehistory

    WHERE

    destination_database_name = BS.database_name

    )

    AND

    (

    BS.database_name = @database_name

    OR

    @database_name IS NULL

    )

    ORDER BY

    BS.database_name

Viewing 4 posts - 1 through 3 (of 3 total)

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