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;

-- Copy-only backup (does not affect the backup chain)
BACKUP DATABASE BookStore
TO DISK = 'C:\Backups\BookStore_CopyOnly.bak'
WITH COPY_ONLY, COMPRESSION;

Backup Best Practices

1. Full backups — daily (or more frequently for critical databases)
2. Differential backups — every few hours to reduce restore time
3. Transaction log backups — every 5-15 minutes for point-in-time recovery
4. Test your restores — a backup is only useful if you can restore it
5. Store backups off-site — protect against site-level disasters
6. Use COMPRESSION — reduces backup size and I/O
7. Use CHECKSUM — validates backup integrity

---

Restoring Databases

Full Restore

-- Restore from a full backup
RESTORE DATABASE BookStore
FROM DISK = 'C:\Backups\BookStore_Full.bak'
WITH RECOVERY;

Full + Differential

-- Restore full backup (with NORECOVERY to allow further restores)
RESTORE DATABASE BookStore
FROM DISK = 'C:\Backups\BookStore_Full.bak'
WITH NORECOVERY;

-- Restore differential
RESTORE DATABASE BookStore
FROM DISK = 'C:\Backups\BookStore_Diff.bak'
WITH RECOVERY;

Point-in-Time Recovery

-- Full backup (NORECOVERY)
RESTORE DATABASE BookStore
FROM DISK = 'C:\Backups\BookStore_Full.bak'
WITH NORECOVERY;

-- Log backup chain (NORECOVERY for all but the last)
RESTORE LOG BookStore
FROM DISK = 'C:\Backups\BookStore_Log1.trn'
WITH NORECOVERY;

-- Restore to a specific point in time
RESTORE LOG BookStore
FROM DISK = 'C:\Backups\BookStore_Log2.trn'
WITH STOPAT = '2024-06-15T14:30:00', RECOVERY;

RECOVERY vs NORECOVERY

Option	Database State	When to Use
RECOVERY	Online — ready to use	Last restore in the sequence
NORECOVERY	Restoring — not accessible	More restores to follow
STANDBY	Read-only — allows further restores	For reporting during restore

---

SQL Server Agent

SQL Server Agent is the built-in job scheduler:

Jobs

A job consists of one or more steps executed in order:

-- Create a job using system stored procedures
EXEC msdb.dbo.sp_add_job
    @job_name = 'Daily Backup',
    @enabled = 1;

EXEC msdb.dbo.sp_add_jobstep
    @job_name = 'Daily Backup',
    @step_name = 'Full Backup',
    @subsystem = 'TSQL',
    @command = 'BACKUP DATABASE BookStore TO DISK = ''C:\Backups\BookStore_Full.bak'' WITH COMPRESSION';

EXEC msdb.dbo.sp_add_schedule
    @schedule_name = 'Daily at 2 AM',
    @freq_type = 4,         -- daily
    @active_start_time = 020000;

EXEC msdb.dbo.sp_attach_schedule
    @job_name = 'Daily Backup',
    @schedule_name = 'Daily at 2 AM';

EXEC msdb.dbo.sp_add_jobserver
    @job_name = 'Daily Backup';

Alerts

Alerts notify you when specific events occur: