July 14, 2005 at 12:17 am
hi,
is there a list of table that contains the name of all the files that has been restored in a database? i checked the system tables restorefile, restorefilegroup and restorehistory from msdb and they dont have the actual filenames of the backup that were restored. sort of like the backupmediafamily system table for the list of backup that were created.
thanks.
ann
July 14, 2005 at 6:01 am
are you looking for something like this:
select logical_name, physical_name, filegroup_name, user_name, destination_database_name, restore_date
from restorehistory rh
inner join
backupfile bf
on
rh.backup_set_id = bf.backup_set_id
order by restore_date desc
**ASCII stupid question, get a stupid ANSI !!!**
July 14, 2005 at 6:07 pm
close. can i actually get the actual name of the backup file that has been applied? for example, if apply a backup with name servce_20050714_0600_log.bak where will i get that name?
when you restore a database, the screen shows you the names of the files that has been applied to that database. where does sql gets that info?
July 14, 2005 at 6:45 pm
hmmm - ann - I see the name only in the "backupmediafamily" - still looking to see how it links up with the restore tables....
**ASCII stupid question, get a stupid ANSI !!!**
July 15, 2005 at 6:18 am
Folks,
This query when executed on the master table will list all the full databse backups in the last 25 hours... It can of course be manipulated to log backups[ type='L'
] and a different time[dateadd(hh,-25,getdate())] ...
select database_name,
physical_device_name
from msdb.dbo.backupset as bkupset,
msdb.dbo.backupmediafamily as bkupfamily
where type='D'
and backup_start_date >
(dateadd(hh,-25,getdate()))
and bkupfamily.media_set_id =
bkupset.media_set_id
Regards,
M
July 15, 2005 at 6:26 am
maertean - thanks - I beilieve though (ann can set me straight) - that she needs a list of all the files that have been restored - so do you know how to link the backup files with the restorefiles ?!
**ASCII stupid question, get a stupid ANSI !!!**
July 15, 2005 at 9:44 am
ann - could you pl. check out this query and see if it works...I can't test this right now...
select restore_type, physical_device_name, destination_database_name, restore_date
from restorehistory rh
inner join
backupset bs
on
rh.backup_set_id = bs.backup_set_id
inner join
backupmediafamily bf
on
bs.media_set_id = bf.media_set_id
and restore_type = 'D'
order by restore_date desc
**ASCII stupid question, get a stupid ANSI !!!**
July 18, 2005 at 1:28 am
hi sushila,
yup, ur right in the sense that i need the list of RESTORED files. ur sql statement looks about just right and i ran it from the analyzer and it returned the information that i need.
i just replaced the D with L coz i need to track all the restore of tran logs.
thanks heaps =)
ann
July 18, 2005 at 5:10 am
oh good - thx for getting back on this one 'cos I still haven't been able to test it!
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply