Location of destination files on the drives

  • 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

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

    where a.media_set_id = b.media_set_id

  • 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

  • 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