backup file database was restored from

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

backup file database was restored from

Author

Topic

imarchenko
Yak Posting Veteran

57 Posts

Posted - 2008-02-05 : 20:22:30

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

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-02-05 : 20:50:45

check log error.you will see it

imarchenko
Yak Posting Veteran

57 Posts

Posted - 2008-02-05 : 21:18:09

I have been advised to use backupmediafamily:

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

dewacorp.alliances

452 Posts

Posted - 2008-02-07 : 01:06:57

Hi there

If you want to know the restore history. Use this one instead:

SELECT     TOP (100) PERCENT RH.destination_database_name, RH.restore_history_id, RF.file_number, RH.restore_date, RF.destination_phys_name, 
                      RG.filegroup_name, BS.name AS Backup_Set_Name, BS.description, BS.database_creation_date, BS.backup_start_date, BS.database_name, 
                      BS.server_name
FROM         msdb.dbo.restorehistory AS RH LEFT OUTER JOIN
                      msdb.dbo.restorefile AS RF ON RF.restore_history_id = RH.restore_history_id LEFT OUTER JOIN
                      msdb.dbo.restorefilegroup AS RG ON RG.restore_history_id = RH.restore_history_id LEFT OUTER JOIN
                      msdb.dbo.backupset AS BS ON BS.backup_set_id = RH.backup_set_id
ORDER BY RH.restore_date DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources