Create Backup Devices to Simplify SQL Server Backup Process

What is Backup Device in SQL Server?

Backup Device in SQL Server refers to the storage medium used to store single or multiple backup files. Normally it has a physical name, refers to the full path that the operating system uses to access to the device.

You can also define a simpler logical name that points to the physical device where to store your backups, it will be saved in system tables.

Basically except for the name used to refer the storage path, there’s no difference between physical backup device and logical device.

In this article, we only talk about logical backup device.

Why to Use a Backup Device in SQL Server?

When using SQL Server databases, you may have multiple backup jobs or scripts need to perform, such as full backups, differential backups, transaction log backups, and so on. When you perform them at the same time, normally you need to manually set the backup location and file name for each job, which can be pretty tedious and time-consuming.

If you are under such circumstances, I recommend you define a backup device in SQL Server. In this way, you can define the destination path and file name beforehand, and then specify this backup device directly when backing up and restoring.

✦ When you want to change the backup storage location, you only need to change the backup device's physical location. instead of re-specifing the path for each backup job. Therefore, it simplifies the backup process.

How do I Create a Backup Device in SQL Server?

To create a permanent backup device in SQL Server, you only need to define the logical name of the physical path you want to specify. After that you can use this name to back up your SQL database to this physical path, or restore database from there.

Here I will introduce you 2 common ways to create a permanent logical backup device in SQL Server.

Creating a Backup Device with SSMS GUI

1. Launch SQL Server Management Studio (SSMS) and connect to your instance.

2. Expand Server Objects, right-click Backup Devices and select New Backup Device…

3. In the pop-up window, provide a logical device name you want in Device Name.

4. Click File… in Destination to specify the physical path of the device.

5. Then click OK to create this backup device.

You can check the created backup device in Server Objects > Backup Devices.

Creating a Backup Device in SQL Server with T-SQL

Except for SSMS GUI, you can also use sp_addumpdevice to specify a physical path as the backup device in SQL Server. The basic syntax is:

USE master
EXEC sp_addumpdevice 'device type', 'logical name', 'physical name'

Note:
Deivce type: refers to Disk or Tape.
Logical name: Refers to the name you defined when creating the SQL Server backup device.
Physical name: Refers to the full path and file name of the specified disk or tape device.

In the following, I will show you some examples, to explain how you should customize the statements to create the backup devices according to your demands.

✦ Steps:

1. Launch SSMS and connect to your instance. Click New Query on the Standard bar.

2. Input the corresponding statements.

• Specify a disk backup device

Here is an example: I want to create a disk backup device with the physical path of D:\backups\ANbackup.bak, and name it as ANbackup. Therefore, I will input the following statements:

USE master
EXEC sp_addumpdevice 'disk', 'ANbackup', 'D:\backups\ANbackup.bak'

• Specify a tape backup device

Example: I want to create a tape backup device with the physical path of \\.\APtape, and name it as APbackup. So, I will input the following statements:

USE master
EXEC sp_addumpdevice 'tape', 'APbackup', '\\.\APtape'

Note: SQL Server will no longer support tape backup devices in future versions, please avoid using them in your work, and modify them to more appropriate devices according to your actual situation.

• Specify a network path for backup device

Example: I want specify backup device as a network path, with the physical path of \\192.168.0.52\ backups\ AFdate, and the name AFbackup. So, I will input the statements:

USE master
EXEC sp_addumpdevice 'disk', 'AFbackup', '\\192.168.0.52\backups\AFdate'

3. Click Execute on toolbar to create this backup device in SQL Server.

Now you have specified a backup device. When backing up SQL databases, you can just specify the logical name of your backup device as the destination, instead of the complex physical names.

Note: If you want to change the backup destination path, you can just change backup device location by redefining the physical path to it, instead of extensively modifying the path in the jobs or scripts. This can effectively simplify the backup process.

How to Delete a Backup Device in SQL Server?

If you want to delete a backup device, you can also use SSMS GUI and T-SQL statements to achieve that. Here I will introduce you the detailed procedures.

Deleting backup device on SSMS GUI

1. Launch SSMS and connect to your instance. Expand Server Objects > Backup Devices.

2. Right-click the backup device name and select Delete.

3. Click OK and this backup device will be removed from storage.

Deleting backup device in SQL Server with T-SQL

Just like creating, we use sp_addumpdevice and specify the DELFILE parameter to delete a backup device. The basic syntax is:

USE master
EXEC sp_dropdevice 'logical name', DELFILE

1. Launch SSMS and connect to your instance. Click New Query on the Standard bar.

2. Input the corresponding statements.

Example: I want to delete the backup device DAbackup. So, I will input:

USE master
EXEC sp_dropdevice 'DAbackup', DELFILE

3. Click Execute on the toolbar to delete the device.

Fixes for SQL Cannot Open Backup Device Operating System Error 5

If you encounter the error message: “Cannot open backup device. Operating system error 5 (Access is denied)” when you backing up a SQL Server database to a backup device. There could be 2 possible reasons:

• The backup file is in Read-only status
• The NTFS permissions for the backup folder are not enabled

Here I will show you how to fix the cannot open backup device access denied error.

Fix 1. Uncheck the Read-only Attributes of the Backup File

1. Find the backup file in its folder, right-click it and select Properties.

2. In General tab, uncheck Read-only in Attributes. Click OK to save the change.

Fix 2. Enable the NTFS Permissions for the Backup Folder

If the error still exists after you unchecked Read-only, then please check if you have enabled the NTFS permissions for the backup folder.

1. Find the backup file in its folder, right-click it and select Properties.

2. Turn to Security tab, click Edit…

3. Select Users in the box, and Allow all the permissions below. Click OK to save.

Summary

In this article, I introduced you what is a backup device in SQL Server, and 2 common ways to create and delete it, analyzed 2 possible causes to the common error 5 you may encounter. Hope it could help you.

SQL Server database backup has always been a huge topic. To meet different requirements, you may need to learn a variety of complex methods and tools. And when you want simplify the procedure, you may need to learn even more knowledges.

So, if you don’t have enough time for learning, AOMEI Centralized Database Backupper may be a better option. By allowing you to centrally control all laptops, workstations, and servers within LAN, it is most suitable application for performing remote SQL databases backup and restore. For enterprises, this can ensure both the security of your data and the efficiency of your work.