TechNet
Export (0) Print
Expand All

Define a Logical Backup Device for a Disk File (SQL Server)

 

Applies To: SQL Server 2016

This topic describes how to define a logical backup device for a disk file in SQL Server 2016 by using SQL Server Management Studio or Transact-SQL. A logical device is a user-defined name that points to a specific physical backup device (a disk file or tape drive). The initialization of the physical device occurs later, when a backup is written to the backup device.

In This Topic

Limitations and Restrictions

  • The logical device name must be unique among all the logical backup devices on the server instance. To view the existing logical device names, query the sys.backup_devices catalog view.

Recommendations

  • We recommend that a backup disk be a different disk than the database data and log disks. This is necessary to make sure that you can access the backups if the data or log disk fails.

Security

Permissions

Requires membership in the diskadmin fixed server role.

Requires permission to write to the disk.

To define a logical backup device for a disk file

  1. After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.

  2. Expand Server Objects, and right-click Backup Devices.

  3. Click New Backup Device. The Backup Device dialog box opens.

  4. Enter a device name.

  5. For the destination, click File and specify the full path of the file.

  6. To define the new device, click OK.

To back up to this new device, add it to the Back up to: field in the Back up Database (General) dialog box. For more information, see Create a Full Database Backup (SQL Server).

To define a logical backup for a disk file

  1. Connect to the Database Engine.

  2. From the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute. This example shows how to use sp_addumpdevice to define a logical backup device for a disk file. The example adds the disk backup device named mydiskdump, with the physical name c:\dump\dump1.bak.

USE AdventureWorks2012 ;  
GO  
EXEC sp_addumpdevice 'disk', 'mydiskdump', 'c:\dump\dump1.bak' ;  
GO  

BACKUP (Transact-SQL)
Backup Devices (SQL Server)
sys.backup_devices (Transact-SQL)
sp_addumpdevice (Transact-SQL)
sp_dropdevice (Transact-SQL)
Define a Logical Backup Device for a Tape Drive (SQL Server)
View the Properties and Contents of a Logical Backup Device (SQL Server)

Community Additions

ADD
Show:
© 2016 Microsoft