Export (0) Print
Expand All

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 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

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.

Arrow icon used with Back to Top link [Top]

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.

Arrow icon used with Back to Top link [Top]

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

Arrow icon used with Back to Top link [Top]

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft