Backup Strategies for SQL Server

Effective backup strategies protect your data against loss, corruption, and disasters. This guide outlines the types of backups available in SQL Server, best practices for scheduling, and how to automate backups using T‑SQL and PowerShell.

Backup Types

• Full Backup – Captures the entire database at a point in time.
• Differential Backup – Captures only the changes since the last full backup.
• Transaction Log Backup – Captures the transaction log, enabling point‑in‑time recovery.
• File/Filegroup Backup – Backs up specific files or filegroups for large databases.

Creating a Full Backup (T‑SQL)

BACKUP DATABASE [AdventureWorks] 
TO DISK = N'C:\Backups\AdventureWorks_Full_2025_09_16.bak' 
WITH FORMAT, 
     INIT, 
     NAME = N'AdventureWorks-Full Backup', 
     SKIP, 
     NOREWIND, 
     NOUNLOAD, 
     STATS = 10;
Copy

Automating Backups with PowerShell

# PowerShell script to schedule nightly full backups
$server   = "localhost\SQLEXPRESS"
$dbName   = "AdventureWorks"
$backupPath = "C:\Backups\{0}_Full_{1}.bak" -f $dbName,(Get-Date -Format "yyyy_MM_dd")
$script = @"
BACKUP DATABASE [$dbName] 
TO DISK = N'$backupPath' 
WITH INIT, NAME = N'$dbName-Full Backup', STATS = 5;
"@

Invoke-Sqlcmd -ServerInstance $server -Query $script
Write-Host "Backup completed: $backupPath"
Copy

Recovering a Database

To restore a full backup followed by log backups, use the following sequence:

RESTORE DATABASE [AdventureWorks] 
FROM DISK = N'C:\Backups\AdventureWorks_Full_2025_09_16.bak' 
WITH NORECOVERY;

RESTORE LOG [AdventureWorks] 
FROM DISK = N'C:\Backups\AdventureWorks_Log_2025_09_16_01.trn' 
WITH RECOVERY;
Copy

Best Practices

• Store backups on a separate physical disk or cloud storage.
• Verify backups regularly using RESTORE VERIFYONLY.
• Implement a retention policy aligned with business requirements.
• Encrypt sensitive backups with Transparent Data Encryption (TDE) or custom certificates.