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?
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.