SQL Server Performance

Question on table master..backupset

Discussion in 'Getting Started' started by lcerni, Feb 1, 2010.

  1. lcerni New Member

    I originally set up a backup device as
    EXEC master.dbo.sp_addumpdevice @devtype = N'disk', @logicalname = N'countermind_staging_backup_fri', @physicalname = N'D:I1_Backupcountermind_stagingFridaycountermind_staging_backup_fri.BAK'
    then I had to move the backups to the J Drive. Thus I deleted and recreated the backup device as
    EXEC master.dbo.sp_addumpdevice @devtype = N'disk', @logicalname = N'countermind_staging_backup_fri', @physicalname = N'J:I1_Backupcountermind_stagingFridaycountermind_staging_backup_fri.BAK'
    However, when I run the following code
    SELECT *
    --SUBSTRING(BMF.physical_device_name, 1, len(BMF.physical_device_name) - CHARINDEX('', REVERSE(BMF.physical_device_name), 0))
    from
    backupset MST
    inner join backupmediafamily BMF ON BMF.media_set_id = MST.media_set_id
    where database_name='countermind_staging'

    It still says that the backup device is on the D drive and not on the J drive.
    What must I do to correct this information? I don't think that I should just go into the master..backupset to correct it. Is there a command that I should run?


  2. preethi Member

    Hi lcerni,
    First of all sp_AddDumpDevice does not create a backup. It creates a device for you to take a backup so that you can JUST say CREATE BACKUP <DB> TO <device> When you move/copy the file, it is done at the operating system level. SQL Server has no knowledge on what you have done.
    Even though you have dropped and recreated the device in a different location, you have not created a backup again to the new device. But when you are querying from meta data you are referring the actual backup files and not the devices.
    If you want to know the current location, you need to use the logical_device_name and join that with sys.backup_devices on name.
    The following code may help you:
    SELECT bd.name, bd.physical_name
    --SUBSTRING(BMF.physical_device_name, 1, len(BMF.physical_device_name) - CHARINDEX('', REVERSE(BMF.physical_device_name), 0))
    from
    backupset MST
    inner join backupmediafamily BMF ON BMF.media_set_id = MST.media_set_id
    inner join sys.backup_devices bd on bmf.logical_device_name = bd.name
    where database_name='DBAdmin'
    Please refer those objects in books online for further help.

Share This Page