Skip to content

Latest commit

 

History

History
89 lines (61 loc) · 5.02 KB

specify-a-disk-or-tape-as-a-backup-destination-sql-server.md

File metadata and controls

89 lines (61 loc) · 5.02 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic helpviewer_keywords
Specify disk or tape backup destination
This article shows you how to specify a disk or tape as a backup destination in SQL Server by using SQL Server Management Studio or Transact-SQL.
MashaMSFT
mathoma
03/14/2017
sql
backup-restore
how-to
backup devices [SQL Server], tapes
backing up databases [SQL Server], tapes
database backups [SQL Server], tapes
backup devices [SQL Server], disks
disk backup devices [SQL Server]
database backups [SQL Server], disks
backing up databases [SQL Server], disks
backups [SQL Server], creating
tape backup devices, backing up

Specify a disk or tape backup destination (SQL Server)

[!INCLUDE SQL Server]

This topic describes how to specify a disk or tape as a backup destination in [!INCLUDEssnoversion] by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql].

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

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. [!INCLUDEssNoVersion] must be able to read and write to the device; the account under which the [!INCLUDEssNoVersion] 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.

Using SQL Server Management Studio

To specify a disk or tape as a backup destination

  1. After connecting to the appropriate instance of the [!INCLUDEmsCoName] [!INCLUDEssDEnoversion], 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.

Using Transact-SQL

To specify a disk or tape as a backup destination

  1. Connect to the [!INCLUDEssDE].

  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 [!INCLUDE sssampledbobject-md] database to the disk file Z:\SQLServerBackups\AdventureWorks2022.bak.

USE AdventureWorks2022;  
GO  
BACKUP DATABASE AdventureWorks2022  
TO DISK = 'Z:\SQLServerBackups\AdventureWorks2022.bak'  
GO

See Also

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)