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:

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:

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.