January 15, 2003 at 9:57 am
I'm looking for a place within SQL that tells me which file a database is backed up to. For instance databaseA goes to d:\backups\databasea.sqlbak. I can scour the drives with master..xp_cmdshell looking for *.sqlbak but wonder if there is a location listing in a view that I could query.
TIA
Michelle
January 15, 2003 at 10:13 am
select a.database_name, b.physical_device_name from backupset a, backupmediafamily b
where a.media_set_id = b.media_set_id
January 15, 2003 at 10:30 am
Try This
USE MSDB
SELECT * FROM sysdbmaintplan_history WHERE (activity LIKE 'backup database')
ORDER BY end_time Desc
You can tweak, but I think it get's everything, and more, for what you are looking for.
KlK, MCSE
KlK
January 15, 2003 at 11:00 am
Thanks everyone!
This is what I ended up with:
select a.database_name, max(a.backup_finish_date) as backup_date, b.physical_device_name, 'benrosql03' as servername
from msdb.dbo.backupset a join msdb.dbo.backupmediafamily b
on a.media_set_id = b.media_set_id
where right(rtrim(b.physical_device_name),7) = '.sqlbak'
group by a.database_name, b.physical_device_name
Since these were setup manually instead of part of a maintenance plan, kknudson's didn't work for me.
Now our sysadmin will know what files to place on tape!
Michelle
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply