Post reply

using a script to obtain backup device contents

  • July 9, 2008 at 1:40 pm

    #187940

    Hello,

    I hate to ask what seems to be an easy question to answer. However, I have done some googling and some investigating and haven't found what I need.

    i am writing an ASP script that will collect the names of the backup devices we have. My intention is to obtain the PhysicalName of the device to make sure that the backup device has backed up the correct database.

    of course, i can always use the trusty 'restore filelistonly' command. However, that doesn't seem to lend itself to extracting just the PhysicalName from the backup device.

    i'm trying to figure out what master table to look for this information. Does anyone offhand know how to find physical names for backup devices that I can access with a script?

    thanks.

  • July 9, 2008 at 1:51 pm

    #840486

    I should clarify. I'm familiar with this command:

    select * from master.sys.backup_devices

    This returns the physical_name of the device. However, i'm trying to obtain the contents (e.g., myDBFile.MDF) of the device programmically.

    thanks.

  • July 9, 2008 at 2:03 pm

    #840494

    Here is the script which gives you the details of latest backup files when it was taken with its location too.

    select s1.database_name,s1.backup_start_date,s1.backup_finish_date,s2.physical_device_name

    from msdb..backupset s1 inner join msdb..backupmediafamily s2

    on s1.media_set_id = s2.media_set_id

    where s1.type ='D'

    and s1.backup_start_date =

    (select max(s3.backup_start_date) from msdb..backupset s3

    where s3.database_name = s1.database_name and s3.type ='D')

  • July 9, 2008 at 2:21 pm

    #840502

    wow, this is a tremendous help. Thanks!!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply