SQL Server Backup & Recovery

MSDN Documentation > SQL Server > Admin Guide > Backup & Recovery

Overview

Ensuring data durability and availability is a core responsibility of any SQL Server administrator. This guide covers the strategies, commands, and best practices for performing backups and restoring databases safely and efficiently.

Backup Types

SQL Server provides several backup options to suit different recovery objectives.

  • Full Backup – Captures the entire database at a point in time.
  • Differential Backup – Stores changes since the last full backup.
  • Transaction Log Backup – Records all log activity, enabling point‑in‑time recovery.
  • File/Filegroup Backup – Allows selective backup of database files.
  • Partial Backup – Useful for read‑only filegroups in large databases.
-- Full backup example
BACKUP DATABASE AdventureWorks2019
TO DISK = N'C:\Backups\AdventureWorks2019_Full.bak'
WITH INIT, COMPRESSION;

Restore Operations

Restoring a database involves applying one or more backups in the correct order.

Simple Restore Sequence

  1. Restore the most recent full backup with WITH NORECOVERY.
  2. Apply any differential backup (if used) with WITH NORECOVERY.
  3. Apply transaction log backups sequentially, ending with WITH RECOVERY.
-- Restore full backup
RESTORE DATABASE AdventureWorks2019
FROM DISK = N'C:\Backups\AdventureWorks2019_Full.bak'
WITH NORECOVERY;

-- Restore differential backup
RESTORE DATABASE AdventureWorks2019
FROM DISK = N'C:\Backups\AdventureWorks2019_Diff.bak'
WITH NORECOVERY;

-- Restore log backup
RESTORE LOG AdventureWorks2019
FROM DISK = N'C:\Backups\AdventureWorks2019_Log.trn'
WITH RECOVERY;

Best Practices

  • Schedule regular full backups (weekly) and differential backups (daily).
  • Back up transaction logs frequently (every 15‑30 minutes) for point‑in‑time recovery.
  • Store backups offsite or in Azure Blob Storage for disaster recovery.
  • Validate backups regularly using RESTORE VERIFYONLY.
  • Encrypt backups when storing sensitive data.
  • Document backup retention policies and test restore procedures quarterly.

Frequently Asked Questions

Can I restore a database to a different server?
Yes. Use the WITH MOVE clause to specify new file locations during restore.
What happens if I run out of disk space during a backup?
The backup will fail. Ensure sufficient free space or use backup compression.
Is it safe to delete old backup files?
Only after verifying you have a sufficient backup chain (full + all subsequent differentials/logs) and after confirming retention policies.