Changing a SQL Backup Device Location

The time comes when we need to change where our SQL Backups are located. By Default they are placed in the root SQL backup directory. But as databases grow the need to move these is also needed. This quick tutorial will show you how to move a backup job quickly without needing to edit and re-configure the backup jobs. 

Lets go ahead and launch the SQL Server Management Studio from the start menu on our server. 

Login to SQL Server.

Scroll down to Server Objects > Backup Devices

In this example, we don't have any devices so we will add a couple. 

Since we are working with our Support database we will name this backup job Support_A. We will leave this backup in the default location. In this SQL 2012 its the Backup Folder within the root SQL installation directory. 

We do the same for our second backup device. This one we will name Support_B. We create two backup devices to our full database backups alternate between the two backup devices, giving us at least two full backups to restore from in case of emergency. 

Now we have our devices created we can change the location of where one of them is stored. To do this we click "new Query" above on the toolbar. 

We will be issuing three sets of commands. The first to delete the device, the second to recreate it, and the third to verify this all worked. 

The fist command would be 

EXEC master.dbo.sp_dropdevice
@logicalname = N'Support_A'
GO

Type this, highlight it and click the "Execute" button in the toolbar above. 

The second command re-created the same device but changes the loc
ation of where the file will be stored. 

EXEC master.dbo.sp_addumpdevice
@devtype = N'disk',
@logicalname = N'Support_A',
@physicalname = N'\\NAS\Storage\SQL\Support_A.BAK'
GO

In this instance we are going to store the backup on a local NAS drive using the UNC path. 

Finally a simple command to verify this all worked. 

SELECT *
FROM sys.backup_devices
GO 

As you can see the Support_A device now reflects the new NAS storage location. 

Using this method means you will not need to  go into your backup jobs and re-configure anything. They will run like normal but will store the files where you specified.