Backup Devices (SQL Server)
This document describes backup devices in SQL Server, their types, and how they are used for performing backups and restores.
What are Backup Devices?
A backup device is a physical location where SQL Server stores backup data. When you create a backup, you specify a backup device to write the backup set to. Similarly, when you restore a database, you specify the backup device containing the backup set.
Types of Backup Devices
SQL Server supports two primary types of backup devices:
- Disk: Backups are written to files on a disk drive. This is the most common and flexible type of backup device.
- Tape: Backups are written to tape cartridges using a tape drive. While less common now, it was historically used for long-term archival.
Disk Backup Devices
Disk backup devices offer several advantages, including speed, ease of management, and suitability for both full and differential backups.
Creating Disk Backups
You can create disk backups using SQL Server Management Studio (SSMS) or Transact-SQL (T-SQL).
Using SSMS
- In Object Explorer, connect to your SQL Server instance.
- Expand the "Databases" node.
- Right-click the database you want to back up, select "Tasks," and then click "Back Up...".
- In the "Back Up Database" dialog box, select "Disk" as the backup type.
- Under "Destination," click "Add" to specify the file path and name for your backup file (e.g.,
C:\Backups\MyDatabase_Full.bak). - Configure backup options (e.g., backup type, name, description) as needed.
- Click "OK" to start the backup process.
Using T-SQL
The following T-SQL script demonstrates how to perform a full database backup to a disk device:
BACKUP DATABASE [MyDatabase]
TO DISK = N'C:\Backups\MyDatabase_Full.bak'
WITH NOFORMAT, NOINIT, NAME = N'MyDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
Backup Device Options (Disk)
- WITH FORMAT: Initializes the backup device, overwriting any existing backups. Use with caution.
- WITH NOFORMAT: Appends the backup to the existing content of the device. This is the default.
- WITH INIT: Overwrites all existing backup sets on the media. Equivalent to FORMAT.
- WITH NOINIT: Appends the backup to the media. This is the default.
- WITH NAME: Assigns a name to the backup set.
- WITH DESCRIPTION: Adds a descriptive text to the backup set.
- STATS: Displays progress messages at specified intervals.
Tape Backup Devices
Tape backups are typically used for long-term archival and disaster recovery. They require a compatible tape drive installed on the server.
Creating Tape Backups
While SSMS can be used, tape backups are often managed through specialized backup software that integrates with SQL Server.
Using T-SQL
The following T-SQL script demonstrates how to perform a full database backup to a tape device (assuming a tape drive is available and configured):
BACKUP DATABASE [MyDatabase]
TO TAPE = N'\\.\Tape0'
WITH NOFORMAT, NOINIT, NAME = N'MyDatabase-Full Database Backup', SKIP, REWIND, UNLOAD, STATS = 10
GO
Backup Device Options (Tape)
- TO TAPE: Specifies the tape device.
- REWIND: Rewinds the tape after the backup.
- NOREWIND: Leaves the tape positioned at the end of the backup.
- UNLOAD: Ejects the tape after the backup.
- NOUNLOAD: Leaves the tape in the drive.
Backup Device Considerations
- Backup Location: Store backups on a separate physical drive or network share from your database files to protect against hardware failure.
- Backup Frequency: Determine an appropriate backup schedule based on your Recovery Point Objective (RPO).
- Retention Policies: Implement a clear policy for how long backups are kept.
- Backup Verification: Regularly verify your backups using the `RESTORE VERIFYONLY` command or SSMS options.