February 5, 2008 at 6:23 pm
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
February 5, 2008 at 8:36 pm
The full path of the restore is logged in the servers Application Event log as well as the SQL Logs. Look for EventID 18267.
February 6, 2008 at 6:33 pm
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
February 7, 2008 at 7:47 am
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