Modify device path for multiple Backup Devices

Backup Devices provide a nice way to permanently configure the backup location, enabling BACKUP DATABASE to look like:

BACKUP DATABASE [xyz] TO [backup-device-name];

When you create a Backup Device, you specify the physical location where backups will actually be stored when using the above BACKUP DATABASE syntax. So, for instance, you might have a Backup Device named [Master Backup], that you use for backing up the master database, with a physical location of \\old-backup-server\SQLBackups\MyServer\master\master_full_backup.bak. Modifying that location to save the backups onto your brand-new shiny storage appliance might typically consist of using the SSMS GUI to drop-and-recreate the backup device with the path pointing at the new location.

Creating a backup device via the pointy-clicky-GUI

Creating a backup device via the pointy-clicky-GUI

However, if you have a large number of backup devices on a server, and need to modify the target location for all your backups, you may end up with a whole lot of pointy-clicky work, which no-one likes to do. Instead, use the following code to dynamically generate the necessary code that drops-and-recreates each Backup Device:

DECLARE @cmd nvarchar(max);
DECLARE @bdname sysname;
DECLARE @bdpn nvarchar(260);
DECLARE @oldPath nvarchar(260) = '\\Old-Backup-Server';
DECLARE @newPath nvarchar(260) = '\\New-Backup-Server';
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT bd.name
    , bd.physical_name
FROM sys.backup_devices bd
WHERE bd.physical_name LIKE '%' + @oldPath + '%'
ORDER BY bd.name;
OPEN cur;
FETCH NEXT FROM cur INTO @bdname, @bdpn;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cmd = 'EXEC sys.sp_dropdevice @logicalname = ''' + @bdname + ''', @delfile = NULL;'
    PRINT @cmd;
    SET @bdpn = REPLACE(@bdpn, @oldPath, @newPath)
    SET @cmd = 'EXEC sys.sp_addumpdevice @devtype = ''disk'', @logicalname = ''' + @bdname + '''
    , @physicalname = ''' + @bdpn +'''
    , @cntrltype = NULL
    , @devstatus = NULL;

'
    PRINT @cmd;
    FETCH NEXT FROM cur INTO @bdname, @bdpn;
END
CLOSE cur;
DEALLOCATE cur;

In the example above, I’m replacing \\Old-Backup-Server in the physical device name with \\New-Backup-Server. You’d want to adjust those parameters for your requirements.

Microsoft provides excellent documentation regarding Backup Devices here.