Microsoft Learn

SQL Server Backup and Restore

On this page

Introduction to Backup and Restore

This section provides a comprehensive overview of backup and restore strategies for Microsoft SQL Server. Ensuring the availability and integrity of your data is paramount, and SQL Server's robust backup and restore features are essential tools for achieving this. This documentation will guide you through the fundamental concepts, different types of backups, restore procedures, and best practices to safeguard your databases against data loss.

A reliable backup and restore plan is critical for disaster recovery, point-in-time recovery, and migrating databases. Understanding the nuances of full, differential, and transaction log backups, along with the various restore options, allows you to tailor a strategy that meets your specific business continuity requirements.

Types of SQL Server Backups

SQL Server offers several types of backups, each serving a different purpose in your data protection strategy:

Full Database Backup

A full database backup contains all the data and transaction log records necessary to restore the database to the state it was in when the backup was completed. It serves as the starting point for other backup types.

BACKUP DATABASE YourDatabase
TO DISK = 'C:\Backup\YourDatabase_Full.bak'
WITH FORMAT;

Differential Database Backup

A differential backup backs up only the data that has changed since the last full database backup. Restoring requires the last full backup and the latest differential backup.

BACKUP DATABASE YourDatabase
TO DISK = 'C:\Backup\YourDatabase_Diff.bak'
WITH DIFFERENTIAL;

Transaction Log Backup

Transaction log backups back up the transaction log records. They are only available for databases in the FULL or BULK_LOGGED recovery model. This backup type is essential for point-in-time restores.

BACKUP LOG YourDatabase
TO DISK = 'C:\Backup\YourDatabase_Log.trn'
WITH NOFORMAT;

Tip: Regularly schedule full backups and more frequent differential and transaction log backups based on your recovery point objective (RPO).

SQL Server Restore Operations

Restoring a database involves recovering it from one or more backup files. The process depends on the type of backups you have and the desired recovery state.

Restoring a Full Backup

This is the most basic restore operation, bringing the database back to the state of the full backup.

RESTORE DATABASE YourDatabase
FROM DISK = 'C:\Backup\YourDatabase_Full.bak'
WITH REPLACE;

Restoring a Full and Differential Backup

To restore using a full and a differential backup, you first restore the full backup and then the differential backup.

-- Restore Full Backup
RESTORE DATABASE YourDatabase
FROM DISK = 'C:\Backup\YourDatabase_Full.bak'
WITH NORECOVERY, REPLACE;

-- Restore Differential Backup
RESTORE DATABASE YourDatabase
FROM DISK = 'C:\Backup\YourDatabase_Diff.bak'
WITH RECOVERY;

Restoring to a Specific Point in Time

This requires a full backup, all subsequent differential backups (if any), and all transaction log backups since the last full or differential backup. The STOPAT clause is used to specify the recovery point.

RESTORE LOG YourDatabase
FROM DISK = 'C:\Backup\YourDatabase_Log1.trn'
WITH NORECOVERY, STOPAT = '2023-10-27 10:30:00';

RESTORE LOG YourDatabase
FROM DISK = 'C:\Backup\YourDatabase_Log2.trn'
WITH RECOVERY, STOPAT = '2023-10-27 11:00:00';

Important: When restoring a transaction log backup to a specific point in time, ensure you have all the necessary log backups in sequence.

Backup Media Options

SQL Server can back up databases to various media:

  • Disk: The most common and cost-effective option.
  • Tape: Traditionally used, but less common now.
  • URL (Azure Blob Storage): For backing up to cloud storage.

Backing Up to URL

To back up to Azure Blob Storage, you first need to configure a credential that allows SQL Server to access your storage account.

-- Create a Storage Account and Blob Container in Azure
-- Configure a SQL Server Credential
CREATE CREDENTIAL YourAzureCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
     -- Replace with your actual SAS token
     -- Example: 'sr=c&sv=2018-03-28&sig=...&se=...'
     SECRET = 'YOUR_SAS_TOKEN';

-- Backup to URL
BACKUP DATABASE YourDatabase
TO URL = 'https://yourstorageaccount.blob.core.windows.net/yourcontainer/YourDatabase_Full.bak'
WITH CREDENTIAL = YourAzureCredential, FORMAT;

Common Backup and Restore Options

The BACKUP and RESTORE commands offer numerous options to control the process:

  • WITH FORMAT: Initializes the media, overwriting any existing backup sets.
  • WITH NOFORMAT: Appends the backup to existing media.
  • WITH INIT: Overwrites all existing backup sets on the media.
  • WITH NOINIT: Appends to the media set.
  • WITH NAME = 'backup_set_name': Specifies a name for the backup set.
  • WITH DESCRIPTION = 'description': Adds a description to the backup set.
  • WITH COMPRESSION: Compresses the backup (supported in Enterprise and Standard editions).
  • WITH CHECKSUM: Verifies backup checksums.
  • WITH COPY_ONLY: Creates a backup that is independent of the backup chain.
  • WITH MOVE 'logical_file_name' TO 'physical_file_path': Used during restore to move data and log files to new locations.

Note: Backup compression can significantly reduce backup size and potentially speed up the backup process, but it requires more CPU resources.

Common Tasks

Key Concepts

  • Recovery Model: Determines how transactions are logged and what backup and restore operations are supported (Simple, Full, Bulk-Logged).
  • Backup Set: A set of files containing a single logical backup.
  • Backup Chain: The sequence of backups (full, differential, log) required to restore a database.
  • Point-in-Time Restore: Restoring a database to a specific point in time using transaction log backups.
  • WITH NORECOVERY: Leaves the database in a restoring state, allowing subsequent restores.
  • WITH RECOVERY: Completes the restore operation and brings the database online.
  • WITH RESTART: Resumes a backup or restore operation if interrupted.