SQL Server Documentation

This guide provides a comprehensive reference for backing up and restoring Microsoft SQL Server databases. It covers backup types, best practices, T‑SQL commands, PowerShell scripts, and common troubleshooting scenarios.

Key concepts:

• Full backup – captures the entire database at a point in time.
• Differential backup – records changes since the last full backup.
• Transaction log backup – preserves log records to enable point‑in‑time recovery.
• Copy‑only backup – creates a backup without affecting the backup chain.

Full Backup

BACKUP DATABASE [AdventureWorks] 
TO DISK = N'C:\Backups\AdventureWorks_Full_2025_09_13.bak' 
WITH INIT, 
NAME = N'AdventureWorks-Full Backup',
DESCRIPTION = N'Full backup of AdventureWorks on 2025‑09‑13',
COMPRESSION, 
STATS = 10;

Differential Backup

BACKUP DATABASE [AdventureWorks] 
TO DISK = N'C:\Backups\AdventureWorks_Diff_2025_09_13.bak' 
WITH DIFFERENTIAL,
NAME = N'AdventureWorks-Diff Backup',
DESCRIPTION = N'Differential backup after full backup',
STATS = 10;

Transaction Log Backup

BACKUP LOG [AdventureWorks] 
TO DISK = N'C:\Backups\AdventureWorks_Log_2025_09_13.trn' 
WITH NORECOVERY,
NAME = N'AdventureWorks-Log Backup',
DESCRIPTION = N'Log backup for point‑in‑time restore',
STATS = 10;

# Requires SqlServer module
Import-Module SqlServer

$server   = 'SQL01\MAIN'
$database = 'AdventureWorks'
$path     = "C:\Backups\$($database)_Full_$(Get-Date -Format 'yyyyMMdd_HHmm').bak"

Backup-SqlDatabase -ServerInstance $server `
    -Database $database `
    -BackupFile $path `
    -CompressionOption On `
    -Initialize `
    -BackupAction Database `
    -Verbose

Write-Host "Backup completed: $path"

Restore steps typically follow this order:

1. Restore the most recent full backup with WITH NORECOVERY.
2. Apply one or more differential backups (if any) also with WITH NORECOVERY.
3. Apply all required log backups in chronological order, ending with WITH RECOVERY or WITH STANDBY for a read‑only state.

Full Restore Example

RESTORE DATABASE [AdventureWorks] 
FROM DISK = N'C:\Backups\AdventureWorks_Full_2025_09_13.bak' 
WITH MOVE N'AdventureWorks_Data' TO N'D:\Data\AdventureWorks.mdf',
MOVE N'AdventureWorks_Log' TO N'D:\Logs\AdventureWorks_log.ldf',
NORECOVERY,
REPLACE,
STATS = 10;

Applying Differential Backup

RESTORE DATABASE [AdventureWorks] 
FROM DISK = N'C:\Backups\AdventureWorks_Diff_2025_09_13.bak' 
WITH NORECOVERY,
STATS = 10;

Applying Log Backups

RESTORE LOG [AdventureWorks] 
FROM DISK = N'C:\Backups\AdventureWorks_Log_2025_09_13.trn' 
WITH RECOVERY,
STATS = 10;

• Store backups on a separate storage volume or remote location.
• Validate backups regularly with RESTORE VERIFYONLY.
• Keep at least three generations of full backups and corresponding differential/log chains.
• Encrypt backups for compliance using ENCRYPTION options.
• Document backup schedules, retention policies, and recovery objectives (RPO/RTO).

Common Errors

• High Availability & Disaster Recovery
• Security & Auditing
• Performance Tuning