Applies To: SQL Server 2016
THIS TOPIC APPLIES TO: SQL Server (starting with 2016) Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse
This topic describes how to specify a disk or tape as a backup destination in SQL Server 2016 by using SQL Server Management Studio or Transact-SQL.
In This Topic
Before you begin:
To specify a disk or tape as a backup destination, using:
SQL Server Management Studio
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.
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.
Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.
Right-click the database, point to Tasks, and then click Back Up. The Back Up Database dialog box appears.
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.
Connect to the Database Engine.
From the Standard bar, click New Query.
In the BACKUP statement, specify the file or device and its physical name. This example backs up the
AdventureWorks2012database to the disk file
USE AdventureWorks2012; GO BACKUP DATABASE AdventureWorks2012 TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012.Bak' GO
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)