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:
|
1 |
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
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
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.