Backup Devices (SQL Server) - SQL Server (2023)

Applies to: SQL Server

During a backup operation on a SQL Server database, the backed up data (the backup) is written to a physical backup device. This physical backup device is initialized when the first backup in a media set is written to it. Backups on a set of one or more backup devices compose a single media set.

Terms and definitions

backup disk
A hard disk or other disk storage media that contains one or more backup files. A backup file is a regular operating system file.

media set
An ordered collection of backup media, tapes or disk files, that uses a fixed type and number of backup devices. For more information about media sets, see Media Sets, Media Families, and Backup Sets (SQL Server).

physical backup device
Either a tape drive or a disk file that is provided by the operating system. A backup can be written to from 1 to 64 backup devices. If a backup requires multiple backup devices, the devices all must correspond to a single type of device (disk or tape).

SQL Server Backups can also be written to Azure Blob Storage in addition to disk or tape.

Using disk backup devices

If a disk file fills while a backup operation is appending a backup to the media set, the backup operation fails. The maximum size of a backup file is determined by the free disk space available on the disk device; therefore, the appropriate size for a backup disk device depends on the size of your backups.

A disk backup device could be a simple disk device, such as an ATA drive. Alternatively, you could use a hot-swappable disk drive that would let you transparently replace a full disk on the drive with an empty disk. A backup disk can be a local disk on the server or a remote disk that is a shared network resource. For information about how to use a remote disk, see Backing Up to a File on a Network Share, later in this topic.

SQL Server management tools are very flexible at handling disk backup devices because they automatically generate a time-stamped name on the disk file.

Specify a backup file using its physical name (Transact-SQL)

The basic BACKUP syntax for specifying a backup file by using its physical device name is:

BACKUP DATABASE database_name

TO DISK = { 'physical_backup_device_name' | @physical_backup_device_name_var }

For example:

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

To specify a physical disk device in a RESTORE statement, the basic syntax is:

RESTORE { DATABASE | LOG } database_name

FROM DISK = { 'physical_backup_device_name' | @physical_backup_device_name_var }

For example,

RESTORE DATABASE AdventureWorks2012 FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2012.bak'; 

Specify the disk backup file path

When you are specifying a backup file, you should enter its full path and file name. If you specify only the file name or a relative path when you are backing up to a file, the backup file is put in the default backup directory. The default backup directory is C:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Backup, where n is the number of the server instance. Therefore, for the default server instance, the default backup directory is: C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup.

To avoid ambiguity, especially in scripts, we recommend that you explicitly specify the path of the backup directory in every DISK clause. However, this is less important when you are using Query Editor. In that case, if you are sure that the backup file resides in the default backup directory, you can omit the path from a DISK clause. For example, the following BACKUP statement backs up the AdventureWorks2019 database to the default backup directory.

BACKUP DATABASE AdventureWorks2012 TO DISK = 'AdventureWorks2012.bak'; GO 

For SQL Server to access a remote disk file, the SQL Server service account must have access to the network share. This includes having the permissions needed for backup operations to write to the network share and for restore operations to read from it. The availability of network drives and permissions depends on the context is which SQL Server service is running:

• To back up to a network drive when SQL Server is running in a domain user account, the shared drive must be mapped as a network drive in the session where SQL Server is running. If you start Sqlservr.exe from command line, SQL Server sees any network drives you have mapped in your login session.

• When you run Sqlservr.exe as a service, SQL Server runs in a separate session that has no relation to your login session. The session in which a service runs can have its own mapped drives, although it usually does not.

• You can connect with the network service account by using the computer account instead of a domain user. To enable backups from specific computers to a shared drive, grant access to the computer accounts. As long as the Sqlservr.exe process that is writing the backup has access, it is irrelevant whether the user sending the BACKUP command has access.

  Important

  Backing up data over a network can be subject to network errors; therefore, we recommend that when you are using a remote disk you verify the backup operation after it finishes. For more information, see RESTORE VERIFYONLY (Transact-SQL).

Specify a Universal Naming Convention (UNC) name

To specify a network share in a backup or restore command, use the fully qualified universal naming convention (UNC) name of the file for the backup device. A UNC name has the form \\Systemname\ShareName\Path\FileName.

For example:

BACKUP DATABASE AdventureWorks2012 TO DISK = '\\BackupSystem\BackupDisk1\AW_backups\AdventureWorksData.Bak'; GO 

Using tape devices

Backing up SQL Server data to tape requires that the tape drive or drives be supported by the Microsoft Windows operating system. Additionally, for the given tape drive, we recommend that you use only tapes recommended by the drive manufacturer. For more information about how to install a tape drive, see the documentation for the Windows operating system.

When a tape drive is used, a backup operation may fill one tape and continue onto another tape. Each tape contains a media header. The first media used is called the initial tape. Each successive tape is known as a continuation tape and has a media sequence number that is one higher than the previous tape. For example, a media set associated with four tape devices contains at least four initial tapes (and, if the database does not fit, four series of continuation tapes). When appending a backup set, you must mount the last tape in the series. If the last tape is not mounted, the Database Engine scans forward to the end of the mounted tape and then requires that you change the tape. At that point, mount the last tape.

Tape backup devices are used like disk devices, with the following exceptions:

• The tape device must be connected physically to the computer that is running an instance of SQL Server. Backing up to remote tape devices is not supported.

• If a tape backup device is filled during the backup operation, but more data still must be written, SQL Server prompts for a new tape and continues the backup operation after a new tape is loaded.

Specify a backup tape using its physical name (Transact-SQL)

The basic BACKUP syntax for specifying a backup tape using the physical device name of the tape drive is:

BACKUP { DATABASE | LOG } database_name

TO TAPE = { 'physical_backup_device_name' | @physical_backup_device_name_var }

For example:

BACKUP LOG AdventureWorks2012 TO TAPE = '\\.\tape0'; GO 

To specify a physical tape device in a RESTORE statement, the basic syntax is:

RESTORE { DATABASE | LOG } database_name

FROM TAPE = { 'physical_backup_device_name' | @physical_backup_device_name_var }

Tape-Specific BACKUP and RESTORE options (Transact-SQL)

To facilitate tape management, the BACKUP statement provides the following tape-specific options:

• { NOUNLOAD | UNLOAD }

  You can control whether a backup tape is unloaded automatically from the tape drive after a backup or restore operation. UNLOAD/NOUNLOAD is a session setting that persists for the life of the session or until it is reset by specifying the alternative.

• { REWIND | NOREWIND }

  You can control whether SQL Server keeps the tape remains open after the backup or restore operation or releases and rewinds the tape after it fills. The default behavior is to rewind the tape (REWIND).

Managing open tapes

To view a list of open tape devices and the status of mount requests, query the sys.dm_io_backup_tapes dynamic management view. This view shows all the open tapes. These include in-use tapes that are temporarily idle while they wait for the next BACKUP or RESTORE operation.

If a tape has been accidentally left open, the fastest way to release the tape is by using the following command: RESTORE REWINDONLY FROM TAPE =backup_device_name. For more information, see RESTORE REWINDONLY (Transact-SQL).

Using the Azure Blob Storage

SQL Server Backups can be written to Azure Blob Storage. For more information on how to use Azure Blob Storage for your backups, see SQL Server Backup and Restore with Microsoft Azure Blob Storage.

Use a logical backup device

A logical backup device is an optional, user-defined name that points to a specific physical backup device (a disk file or tape drive). A logical backup device lets you use indirection when referencing the corresponding physical backup device.

Defining a logical backup device involves assigning a logical name to a physical device. For example, a logical device, AdventureWorksBackups, could be defined to point to the Z:\SQLServerBackups\AdventureWorks2012.bak file or the \\.\tape0 tape drive. Backup and restore commands can then specify AdventureWorksBackups as the backup device, instead of DISK = 'Z:\SQLServerBackups\AdventureWorks2012.bak' or TAPE = '\\.\tape0'.

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. This view displays the name of each logical backup device and describes the type and physical file name or path of the corresponding physical backup device.

After a logical backup device is defined, in a BACKUP or RESTORE command, you can specify the logical backup device instead of the physical name of the device. For example, the following statement backs up the AdventureWorks2012 database to the AdventureWorksBackups logical backup device.

BACKUP DATABASE AdventureWorks2012 TO AdventureWorksBackups; GO 

One advantage of using a logical backup device is that it is simpler to use than a long path. Using a logical backup device can help if you plan to write a series of backups to the same path or to a tape device. Logical backup devices are especially useful for identifying tape backup devices.

A backup script can be written to use a particular logical backup device. This lets you switch to a new physical backup devices without updating the script. Switching involves the following process:

1. Dropping the original logical backup device.

2. Defining a new logical backup device that uses the original logical device name but maps to a different physical backup device. Logical backup devices are especially useful for identifying tape backup devices.

Mirroring of backup media sets reduces the effect of backup-device malfunctions. These malfunctions are especially serious because backups are the last line of defense against data loss. As the sizes of databases grow, the probability increases that a failure of a backup device or media will make a backup nonrestorable. Mirroring backup media increases the reliability of backups by providing redundancy for the physical backup device. For more information, see Mirrored Backup Media Sets (SQL Server).

Archive SQL Server backups

We recommend that you use a file system backup utility to archive the disk backups and that you store the archives off-site. Using disk has the advantage that you use the network to write the archived backups onto an off-site disk. Azure Blob Storage can be used as off-site archival option. You can either upload your disk backups, or directly write the backups to Azure Blob Storage.

Another common archiving approach is to write SQL Server backups onto a local backup disk, archive them to tape, and then store the tapes off-site.

To specify a disk device (SQL Server Management Studio)

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

To specify a tape device (SQL Server Management Studio)

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

To define a logical backup device

• sp_addumpdevice (Transact-SQL)

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

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

• BackupDevice (SMO)

To use a logical backup device

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

• Restore a Backup from a Device (SQL Server)

• BACKUP (Transact-SQL)

• RESTORE (Transact-SQL)

To View Information About Backup Devices

• Backup History and Header Information (SQL Server)

• View the Properties and Contents of a Logical Backup Device (SQL Server)

• View the Contents of a Backup Tape or File (SQL Server)

To delete a logical backup device

• sp_dropdevice (Transact-SQL)

• Delete a Backup Device (SQL Server)

See also

SQL Server, Backup Device Object
BACKUP (Transact-SQL)
Maintenance Plans
Media Sets, Media Families, and Backup Sets (SQL Server)
RESTORE (Transact-SQL)
RESTORE LABELONLY (Transact-SQL)
sys.backup_devices (Transact-SQL)
sys.dm_io_backup_tapes (Transact-SQL)
Mirrored Backup Media Sets (SQL Server)