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

  • Article
  • 12 minutes to read

Applies to: Backup Devices (SQL Server) - SQL Server (1) 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.


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 AdventureWorks2012 TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012.bak'; GO 

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

(Video) SQL Server tutorial 75: Backups, understanding backup devices.

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 


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

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.


    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


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.

(Video) How to Create Backup Devices in SQL Server

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:


    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.


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


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.

(Video) How to BACKUP DATABASES in SQL Server Management Studio

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 


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.

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


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.

(Video) SQL Server: How to Create a Backup Device

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)
sys.backup_devices (Transact-SQL)
sys.dm_io_backup_tapes (Transact-SQL)
Mirrored Backup Media Sets (SQL Server)

(Video) SQL Server Fails Backup to Network Share


What are the main 3 types of backups in SQL? ›

SQL Server Backup Types. Microsoft SQL Server supports five types of backup: full, differential, transaction log, tail log, and copy-only backup.

What is backup device in 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.

How to check backup failure in SQL Server? ›

Locate the event log for backup failure in the Event Viewer

On the right-hand side in the Actions menu, navigate to Find. Type in the name of the database for which the failure occurred, and click Find Next. Every time you click Find Next, the previous event log for the database displays.

What is the best way to backup a SQL database? ›

To take a backup of your database, follow these steps:
  1. Launch SQL Server Management Studio (SSMS) and connect to your SQL Server instance.
  2. Expand the Databases node in Object Explorer.
  3. Right-click the database, hover over Tasks, and select Back up....
  4. Under Destination, confirm that the path for your backup is correct.
Nov 18, 2022

What is the best backup method? ›

Investing in cloud storage is one of the best ways to back up your data. A good cloud storage service, such as Google Drive or Dropbox, will come with its own file management system for simplified access, as well as decent encryption to keep your data away from third parties.

What are basics of backup devices? ›

A backup storage device is used to make copies of data that is actively in use. Backup machines provide redundancy of data residing on primary storage. Should the storage medium, such as a hard disk drive (HDD), fail or become corrupted, the original data is recovered from copies on the backup hardware.

What are the 3 ways to backup? ›

3 Methods for Computer Backup
  1. Use an External Hard Drive. One of the simplest ways that you can backup your computer is by purchasing an external hard drive. ...
  2. Backup Your Files Online. ...
  3. Use a Cloud Storage Service. ...
  4. Back It Up.
May 10, 2017

Why are backup devices important? ›

Backups protect against human errors, hardware failure, virus attacks, power failure, and natural disasters. Backups can help save time and money if these failures occur.

What is the 3 2 1 backup rule? ›

The 3-2-1 backup strategy simply states that you should have 3 copies of your data (your production data and 2 backup copies) on two different media (disk and tape) with one copy off-site for disaster recovery.

What causes a backup to fail? ›

Media failure, human error and hardware failure are among the most common reasons backups fail. Learn what you can do to prevent these issues and others to perform legitimate, usable backups. Backup troubleshooting tip: Media failure, human error and hardware failure are among the most common reasons backups fail.

How to check if backup completed successfully in SQL Server? ›

Open SSMS, right click on a database then select Tasks > Back Up. A screen similar to the below image will open. After you select all of the backup options and click OK, you can monitor the progress on the lower left side of the GUI as shown in the below image. This will give you an idea of the status of the backup.

Which one is the most used rule for backup? ›

The 3-2-1 backup rule has been the most effective approach in data protection for decades. By keeping three different copies of your data, stored on two storage media with one kept offsite, you significantly reduce the chances of losing all of your data.

What are the 3 types of backup schemes are available? ›

There are 3 main types of backup: Full, differential and incremental.

Which is the fastest backup strategy? ›

The benefit of an incremental backup is that it copies a smaller amount of data than a full. Thus, these operations will have a faster backup speed, and require less media to store the backup.

Which is the fastest way to backup the data? ›

The easiest way to backup a server is with a server backup solution. These solutions can come in the form of software or appliances. Server backup solutions are typically designed to help you backup server data to another local server, a cloud server, or a hybrid system.

How do I do a full database backup? ›

You must perform at least one full database backup before you can perform a differential or a transaction log backup. Under Backup component, select Database. In the Destination section, review the default location for the backup file (in the ../mssql/data folder).

How can I improve my backup performance? ›

How to Increase Backup Performance
  1. Turn off virus scanning on Windows systems, this can double performance.
  2. Upgrade network interface card (NIC) drivers to the latest release. ...
  3. Increase system background performance over foreground performance. ...
  4. Apply the latest service packs and system patches.
Jul 10, 2018

Which backup method takes the longest time? ›

A full backup takes the longest time and the most space to complete. However, if an organization only uses full backups, then only the latest full backup needs to be restored. Any backup that uses a differential or incremental backup will first start with a full backup as its baseline.

What are the four most common types of backup? ›

Each backup program has its own approach in executing the backup, but there are four common types of backup implemented and generally used in most of these programs: full backup, differential backup, incremental backup and mirror backup.

How do I backup all my devices? ›

Automatically back up your phone
  1. Open your phone's Settings app.
  2. Select Google. Backup. Tip: If this is your first time, turn on Backup by Google One and follow the on-screen instructions.
  3. Tap Back up now.

Which storage device is the best option for backing up files? ›

Flash drives and SSDs are the most efficient physical way to backup your system. Flash drives and solid state drives use flash technology to write and read data very quickly, making for speedy backups.

Do you need to backup everything on your device Why or why not? ›

Everyone experiences technical issues with their devices. And if you lose data without having your device backed up, you're losing a lot more than just your contacts! Even if you do transfer all of your data storage to something like iCloud or Dropbox, you should still do a hard backup of your devices.

What are two critical reasons to backup your files? ›

Top 5 Reasons To Backup Your Data
  • Data loss can occur at any time and for all kinds of reasons. This might include crashes, malware, physical damage or failure, theft, or basic user error. ...
  • Audits and Archives. ...
  • Getting Back Up Quickly. ...
  • Losing Everything Through Downtime. ...
  • Saving Time.
Dec 27, 2021

What is the difference between backup and recovery? ›

Backup refers to creating copies of important documents and data that are stored on your computer. This process includes backing up your database, videos and other media. Recovery is the process of recovering deleted or damaged data from backups.

What is the golden rule of backups? ›

According to the 3-2-1 backup rule, you should keep at least two backup copies to protect your data against natural disasters, accidental deletions, hardware failure, and cyberattacks.

What is 3-2-1 1 0 backup rule? ›

The 3-2-1 backup rule has long been the standard for data protection. It says to keep three copies of your data—one primary and two backups—with two copies stored locally on two formats (network-attached storage, tape, or local drive) and one copy stored offsite in the cloud or secure storage.

What is 4 3 2 backup strategy? ›

A 4-3-2 strategy means backups are duplicated and geographically distant to offer protection from events like natural disasters. Backups are also stored on two separate networks, isolating them from production networks in the event they're compromised.

How do I troubleshoot a backup failure? ›

To troubleshoot an unsuccessful backup of a volume. Make sure that the hard disk is connected to the computer, turned on, and functioning properly. Run chkdsk /f /r to fix any errors on the hard disk (/f) and recover readable information from any bad sectors (/r). For more information about running chkdsk, see CHKDSK.

How often do backups fail? ›

Backups fail for many reasons, two being from hardware and software failures. According to a recent survey by, the failure rate for backups is an astonishing 37%, which is why investing in professional cloud backups is crucial to your organization.

What are the disadvantages of backups? ›

Full Backup

No matter what happens to your hardware, you know you'll have a complete copy of all the company information you need. Cons: Because these backups replicate so much information, they require a lot of storage space, time, and financial investment to complete.

How do I know if backup was successful? ›

How to Verify Backups—4 Ways
  1. Manual Check/Access the Backup Files. Many cloud backup applications allow a user to view the contents of a backup, like it was another disk. ...
  2. Run a Backup Verification Application. ...
  3. Health Check. ...
  4. Perform a 'Test Restore.
Mar 11, 2020

How would you verify if you have a successful backup? ›

7 Effective Tips to Verify Your Data Backups
  1. Verify backups periodically. ...
  2. Examine backups systematically. ...
  3. Test backups in different cases. ...
  4. Check all backed up data. ...
  5. Ensure enough space available for data recovery. ...
  6. Don't try to restore to original media. ...
  7. Compare the restored data to originals carefully.
Apr 26, 2017

How do I test my server backups? ›

Steps on testing your database backup & recovery plan
  1. Familiarize yourself with your backup and recovery systems. ...
  2. Run tests to recover deleted or corrupted files. ...
  3. Test the backup of your applications. ...
  4. Test your database recovery. ...
  5. Time how long it takes to back up your data. ...
  6. Try testing your backup & recovery plan remotely.
Feb 11, 2022

What is the 3 2 2 rule for backups? ›

Here's what the 3-2-1 backup rule involves: 3: Create one primary backup and two copies of your data. 2: Save your backups to two different types of media. 1: Keep at least one backup file offsite.

What is one of the most important things to remember when doing backup data? ›

Prioritise Security. The last thing to consider is security, but it's probably one of the most important. You can't back up all your data and leave it in a random location. Poorly secured backups leave not only a few documents open to hackers but tons of data.

What are the two most popular methods to back up files? ›

Dropbox and Box are two of the most used file sync services that also let you retain previous versions of updated files. Many cloud backup services let you use their applications and storage resources without cost for a limited amount of data -- usually about 5 GB.

What are the different types of SQL backups? ›

Microsoft SQL Server supports five types of backup: full, differential, transaction log, tail log, and copy-only backup.

What are the three phases of backup and recovery in SQL Server? ›

SQL Server database recovery consists of three phases: the analysis phase, the redo phase, and finally the undo phase.

Which type of backup takes the least amount of time? ›

As we've said, incremental backup is often quicker and takes less space in storage. Businesses that wish to optimize storage space and don't add vast volumes of changed data to storage since the last backup will benefit more from incremental backups.

What are the 3 different types of backup and what is the difference between them? ›

The most common backup types are a full backup, incremental backup and differential backup. Other backup types include synthetic full backups and mirroring. In the debate over cloud vs. local backup, there are some types of backup that are better in certain locations.

What are the 3 recovery models in SQL Server? ›

Three recovery models exist: simple, full, and bulk-logged. Typically, a database uses the full recovery model or simple recovery model. A database can be switched to another recovery model at any time.

What is the 321 rule for backup? ›

You may have heard of the 3-2-1 backup strategy. It means having at least three copies of your data, two local (on-site) but on different media (read: devices), and at least one copy off-site. We'll use “socialsecurity.

What is the 3-2-1 rule for backup? ›

The 3-2-1 backup strategy simply states that you should have 3 copies of your data (your production data and 2 backup copies) on two different media (disk and tape) with one copy off-site for disaster recovery.

What is full vs differential backup in SQL Server? ›

A differential backup is based on the most recent, previous full data backup. A differential backup captures only the data that has changed since that full backup. The full backup upon which a differential backup is based is known as the base of the differential.

What is incremental backup in SQL Server? ›

This kind of backup copies data after the last backup activity. By only backing up new data, they can save time and disk space. This method is commonly for cloud backup as it uses fewer resources.

What are the 5 components of recovery? ›

Five Essential Components of a Recovery Journey
  • Self Direction. The first step on your recovery journey begins with self-direction and making an individual commitment to improving your life. ...
  • Individualized Treatment. ...
  • Holistic Approach. ...
  • Peer Support. ...
  • Hope.

What are the 4 components of recovery? ›

Recovery identifies four dimensions to support a healthy life. These include health, home, purpose, and community.


1. How to Backup SQL Server Databases to a Network Share Drive or NAS
2. How to copy a backup database from Microsoft SQL Server to connected backup device
3. SQL Server 2005 - Create Backup Device
4. How to create a backup device in SQL server- uCertify
5. How to Schedule SQL Server Database Backups.
(Darren's Tech Tutorials)
6. SQL BACKUP/RESTORE , SCHEDULED BACKUP & Google Drive Synchronization
(Muhammad Waqas)
Top Articles
Latest Posts
Article information

Author: Clemencia Bogisich Ret

Last Updated: 03/06/2023

Views: 5465

Rating: 5 / 5 (60 voted)

Reviews: 83% of readers found this page helpful

Author information

Name: Clemencia Bogisich Ret

Birthday: 2001-07-17

Address: Suite 794 53887 Geri Spring, West Cristentown, KY 54855

Phone: +5934435460663

Job: Central Hospitality Director

Hobby: Yoga, Electronics, Rafting, Lockpicking, Inline skating, Puzzles, scrapbook

Introduction: My name is Clemencia Bogisich Ret, I am a super, outstanding, graceful, friendly, vast, comfortable, agreeable person who loves writing and wants to share my knowledge and understanding with you.