SQL Server Backup and Restore

Comprehensive documentation for managing data safety in SQL Server.

On this page:

Introduction to SQL Server Backup and Restore

Data integrity and availability are paramount for any database system. SQL Server provides robust mechanisms for backing up and restoring your databases, ensuring that you can recover from hardware failures, accidental data corruption, or other data loss events. This documentation guides you through the essential concepts and procedures for effective backup and restore strategies.

Understanding the different types of backups, recovery models, and restore operations is crucial for designing a reliable data protection plan.

Understanding Backup Types

SQL Server supports several types of backups, each serving a specific purpose:

  • Full Backup: Captures the entire database. This is the foundation of any backup strategy.
  • Differential Backup: Captures only the data that has changed since the last full backup. This reduces backup time and storage requirements compared to full backups.
  • Transaction Log Backup: Captures transactions from the transaction log since the last log backup. This is only available for databases using the Full or Bulk-Logged recovery models and allows for point-in-time restores.

Recovery Models

The recovery model of a database dictates how transactions are logged and how backups can be performed:

  • Simple: Minimal logging. Only full and differential backups are supported. Transaction log space is automatically reclaimed.
  • Full: Full transaction logging. Supports full, differential, and transaction log backups. Allows for point-in-time restores.
  • Bulk-Logged: Similar to Full, but some bulk operations (like ALTER TABLE ... ADD COLUMN, BULK INSERT) are minimally logged. Transaction log backups are supported.
Important: For point-in-time restore capabilities, your database must be in the Full or Bulk-Logged recovery model.

Common Restore Scenarios

Restoring a database involves bringing it back to a consistent state after a data loss event. The process depends on the type of backup used and the desired recovery point.

Restoring a Full Backup

This is the most basic restore operation, bringing the database to the state it was in when the full backup was taken.

Restoring a Differential Backup

After restoring a full backup, you can restore a differential backup to bring the database closer to its current state. This is faster than taking another full backup.

Restoring a Transaction Log Backup

Transaction log backups are essential for point-in-time recovery. You typically restore a full backup, followed by the latest differential backup (if used), and then a sequence of transaction log backups up to the desired point in time.

Tip: Always test your restore procedures regularly in a non-production environment to ensure their effectiveness.

Restoring to a Point in Time

Using a sequence of transaction log backups, you can restore your database to a specific point in time, minimizing data loss.

Backup and Restore Best Practices

  • Regular Backups: Implement a consistent backup schedule tailored to your Recovery Point Objective (RPO).
  • Multiple Backup Copies: Store backups in different physical locations to protect against site-wide disasters.
  • Verify Backups: Use the VERIFYONLY option to check the integrity of your backup files.
  • Automate: Use SQL Server Agent jobs to automate backup tasks.
  • Monitor: Regularly monitor backup job history and backup file availability.
  • Document: Maintain clear documentation of your backup and restore strategy.
  • Test Restores: Periodically perform test restores to confirm your backups are valid and your restore process works.

Example Commands

Performing a Full Backup

T-SQL 
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

Performing a Differential Backup

T-SQL 
BACKUP DATABASE [MyDatabase]
TO DISK = N'C:\Backups\MyDatabase_Diff.bak'
WITH DIFFERENTIAL, NOFORMAT, NOINIT,  NAME = N'MyDatabase-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO

Performing a Transaction Log Backup

T-SQL 
BACKUP LOG [MyDatabase]
TO DISK = N'C:\Backups\MyDatabase_Log.trn'
WITH NOFORMAT, NOINIT,  NAME = N'MyDatabase-Log Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO

Restoring a Full Backup

T-SQL 
RESTORE DATABASE [MyDatabase]
FROM DISK = N'C:\Backups\MyDatabase_Full.bak'
WITH NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5;
GO

Restoring a Differential Backup

T-SQL 
RESTORE DATABASE [MyDatabase]
FROM DISK = N'C:\Backups\MyDatabase_Diff.bak'
WITH NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5;
GO

Restoring a Transaction Log Backup

T-SQL 
RESTORE LOG [MyDatabase]
FROM DISK = N'C:\Backups\MyDatabase_Log.trn'
WITH NORECOVERY, NOUNLOAD, STATS = 5;
GO

Recovering the Database (after all restores)

T-SQL 
RESTORE DATABASE [MyDatabase] WITH RECOVERY;
GO

Restoring to a Specific Point in Time

T-SQL 
RESTORE LOG [MyDatabase]
FROM DISK = N'C:\Backups\MyDatabase_Log_001.trn'
WITH STOPAT = '2023-10-27T10:30:00', NORECOVERY, NOUNLOAD, STATS = 5;
GO

RESTORE LOG [MyDatabase]
FROM DISK = N'C:\Backups\MyDatabase_Log_002.trn'
WITH NORECOVERY, NOUNLOAD, STATS = 5;
GO

RESTORE DATABASE [MyDatabase] WITH RECOVERY;
GO