Backup, Recovery and Administration

A reliable backup strategy and sound administration practices are essential for any SQL Server environment. This lesson covers backup types, restore operations, SQL Server Agent, Dynamic Management Views (DMVs), and routine maintenance.

---

Backup Types

SQL Server provides several backup types to protect your data:

Backup Type	What It Captures	Restoration
Full	Entire database	Standalone restore point
Differential	Changes since the last full backup	Requires the last full backup
Transaction Log	Log records since the last log backup	Requires full + chain of log backups
Copy-Only	Full or log backup that does not affect the backup chain	Standalone (does not break the chain)
Filegroup	Specific filegroups only	Partial database restore

---

Performing Backups

-- Full backup
BACKUP DATABASE BookStore
TO DISK = 'C:\Backups\BookStore_Full.bak'
WITH COMPRESSION, CHECKSUM;

-- Differential backup
BACKUP DATABASE BookStore
TO DISK = 'C:\Backups\BookStore_Diff.bak'
WITH DIFFERENTIAL, COMPRESSION;

-- Transaction log backup
BACKUP LOG BookStore
TO DISK = 'C:\Backups\BookStore_Log.trn'
WITH COMPRESSION;