Location of destination files on the drives

• mimorr

  SSCarpal Tunnel

  Points: 4850

  More actions

  January 15, 2003 at 9:57 am

  #159404

  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

• Allen Cui-55137

  SSC Guru

  Points: 51650

  More actions

  January 15, 2003 at 10:13 am

  #446393

  select a.database_name, b.physical_device_name from backupset a, backupmediafamily b

  where a.media_set_id = b.media_set_id

• kknudson

  SSCarpal Tunnel

  Points: 4986

  More actions

  January 15, 2003 at 10:30 am

  #446395

  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

• mimorr

  SSCarpal Tunnel

  Points: 4850

  More actions

  January 15, 2003 at 11:00 am

  #446398

  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