Specify a Disk or Tape As a Backup Destination (SQL Server)

This topic describes how to specify a disk or tape as a backup destination in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL.

Note

Support for tape backup devices will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

In This Topic

• Before you begin:

  Security

• To specify a disk or tape as a backup destination, using:

  SQL Server Management Studio

  Transact-SQL

Before You Begin

Security

Permissions

BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.

Ownership and permission problems on the backup device's physical file can interfere with a backup operation. SQL Server must be able to read and write to the device; the account under which the SQL Server service runs must have write permissions. However, sp_addumpdevice, which adds an entry for a backup device in the system tables, does not check file access permissions. Such problems on the backup device's physical file may not appear until the physical resource is accessed when the backup or restore is attempted.

[Top]

Using SQL Server Management Studio

To specify a disk or tape as a backup destination

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 Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.

3. Right-click the database, point to Tasks, and then click Back Up. The Back Up Database dialog box appears.

4. In the Destination section of the General page, click Disk or Tape. To select the paths of up to 64 disk or tape drives containing a single media set, click Add.

   To remove a backup destination, select it and click Remove. To view the contents of a backup destination, select it and click Contents.

[Top]

Using Transact-SQL

To specify a disk or tape as a backup destination

1. Connect to the Database Engine.

2. From the Standard bar, click New Query.

3. In the BACKUP statement, specify the file or device and its physical name. This example backs up the AdventureWorks2012 database to the disk file Z:\SQLServerBackups\AdventureWorks2012.Bak.

USE AdventureWorks2012;
GO
BACKUP DATABASE AdventureWorks2012
TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012.Bak'
GO

[Top]

See Also

Concepts

Back Up a Transaction Log (SQL Server)

Back Up Files and Filegroups (SQL Server)

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

Create a Differential Database Backup (SQL Server)

Define a Logical Backup Device for a Tape Drive (SQL Server)