MSDN Documentation

SQL Server Backup and Restore

Learn about the essential processes of backing up and restoring SQL Server databases to ensure data availability, disaster recovery, and business continuity. This guide covers different backup types, restore strategies, and important considerations for managing your SQL Server data reliably.

Types of Backups

SQL Server offers several backup types, each serving a specific purpose in your data protection strategy:

  • Full Backup: Captures the entire database, including all data and transaction log records up to the point of backup. It's the foundation of any backup strategy.
  • Differential Backup: Backs up only the data that has changed since the last full backup. This significantly reduces backup time and storage requirements compared to frequent full backups.
  • Transaction Log Backup: Backs up the transaction log records since the last log backup (or full/differential backup if no log backups have occurred). This is crucial for point-in-time recovery and requires the database to be in the FULL or BULK_LOGGED recovery model.

The Restore Process

Restoring a database involves returning the database to a specific point in time using one or more backup files. The process typically involves:

  1. Restoring the Last Full Backup: This establishes the baseline for the restore operation.
  2. Restoring Subsequent Differential Backups (Optional): If differential backups are part of your strategy, you'll restore the latest one after the full backup.
  3. Restoring Transaction Log Backups: For point-in-time recovery, you'll restore a sequence of transaction log backups until the desired point is reached.

Common Backup Options

The BACKUP DATABASE statement supports various options:

  • WITH NOINIT / WITH INIT: Specifies whether to append to an existing backup set or overwrite it.
  • WITH COMPRESSION / WITH NO_COMPRESSION: Enables or disables backup compression to save disk space.
  • WITH DIFFERENTIAL: Creates a differential backup.
  • WITH COPY_ONLY: Creates a backup that does not affect the differential or log chain. Useful for ad-hoc backups.
  • WITH STATS = percentage: Displays progress during the backup operation.

Example of a full backup with compression:

BACKUP DATABASE YourDatabase
TO DISK = 'C:\Backups\YourDatabase_Full.bak'
WITH COMPRESSION, STATS = 10;

Common Restore Options

The RESTORE DATABASE statement is used for restoration:

  • WITH REPLACE: Allows overwriting an existing database with the same name. Use with caution.
  • WITH RECOVERY / WITH NORECOVERY / WITH STANDBY = undo_file_name: Controls the final state of the database after restoration. RECOVERY makes the database usable, NORECOVERY leaves it in a restoring state for further log restores, and STANDBY allows read-only access while keeping it in a restoring state.
  • WITH MOVE 'LogicalFileName' TO 'PhysicalFileName': Relocates database files during a restore.
  • WITH STOPAT = 'datetime': Specifies the point in time for a transaction log restore.

Example of restoring a full backup and preparing for log restores:

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

-- Then, to restore a log backup:
RESTORE LOG YourDatabase
FROM DISK = 'C:\Backups\YourDatabase_Log1.trn'
WITH NORECOVERY, STOPAT = '2023-10-27 10:30:00';

-- Finally, to bring the database online:
RESTORE DATABASE YourDatabase WITH RECOVERY;

Backup Media

SQL Server backups can be written to various media:

  • Disk: The most common and flexible option.
  • Tape: Traditional but less common now.
  • URL: Backups can be written directly to cloud storage like Azure Blob Storage.
  • Other Devices: Various third-party solutions integrate with SQL Server.

Backup and Restore Best Practices

  • Regularly Test Restores: A backup is only valuable if it can be successfully restored. Schedule regular restore tests.
  • Use a Reliable Backup Strategy: Combine full, differential, and log backups based on your recovery point objective (RPO) and recovery time objective (RTO).
  • Store Backups Offsite: Ensure your backups are stored in a geographically separate location to protect against site-wide disasters.
  • Monitor Backup Jobs: Use SQL Server Agent alerts or other monitoring tools to ensure backups are completing successfully.
  • Understand Recovery Models: Choose the appropriate recovery model (FULL, BULK_LOGGED, SIMPLE) based on your data durability and recovery needs.
  • Secure Your Backups: Protect backup files from unauthorized access or modification.