Muokkaa

Jaa

Backup Devices (SQL Server)

  • Artikkeli

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.

Important

We recommend that a backup disk be a different disk than the database data and log disks. This is necessary to make sure that you can access the backups if the data or log disk fails.

If database files and backup files are on the same device and the device fails, the database and backups will be unavailable. Also, putting the database and backup files on the separate devices optimizes the I/O performance for both the production use of the database and the writing of backups.

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 AdventureWorks2022   
   TO DISK = 'Z:\SQLServerBackups\AdventureWorks2022.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 AdventureWorks2022   
   FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2022.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 AdventureWorks2022 database to the default backup directory.

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

Note

The default location is stored in the BackupDirectory registry key under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.n\MSSQLServer.

Back up to a network share file

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 AdventureWorks2022   
   TO DISK = '\\BackupSystem\BackupDisk1\AW_backups\AdventureWorksData.Bak';  
GO

Using tape devices

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.

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 AdventureWorks2022   
   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).

Note

For more information about the BACKUP syntax and arguments, see BACKUP (Transact-SQL). For more information about the RESTORE syntax and arguments, see RESTORE (Transact-SQL) and RESTORE Arguments (Transact-SQL), respectively.

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\AdventureWorks2022.bak file or the \\.\tape0 tape drive. Backup and restore commands can then specify AdventureWorksBackups as the backup device, instead of DISK = 'Z:\SQLServerBackups\AdventureWorks2022.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 AdventureWorks2022 database to the AdventureWorksBackups logical backup device.

BACKUP DATABASE AdventureWorks2022   
   TO AdventureWorksBackups;  
GO

Note

In a given BACKUP or RESTORE statement, the logical backup device name and the corresponding physical backup device name are interchangeable.

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.

Mirrored backup media sets

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

Note

Mirrored backup media sets are supported only in SQL Server 2005 Enterprise edition and later versions.

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.

Related tasks

To specify a disk device (SQL Server Management Studio)

To specify a tape device (SQL Server Management Studio)

To define a logical backup device

To use a logical backup device

To View Information About Backup Devices

To delete a logical backup device

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)