sqlserver-backup

SQL Server Backup & Restore

Use this skill for database backup strategies, restore procedures, and verifying backup integrity.

---

Recovery Models

Choose the right recovery model before designing your backup strategy.

Model	Log Truncation	Point-in-Time Recovery	Log Growth Risk	Use When
SIMPLE	Auto at checkpoint	❌ No	Low	Dev, test, non-critical data
FULL	On log backup	✅ Yes	High (must take log backups)	Production — minimizes data loss
BULK_LOGGED	On log backup	Partial	Medium	Bulk load periods (then switch back to FULL)

-- Check current recovery model
SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE name = DB_NAME();

-- Change recovery model
ALTER DATABASE MyDatabase SET RECOVERY FULL;
ALTER DATABASE MyDatabase SET RECOVERY SIMPLE;

⚠️ Switching to FULL recovery model: You must take a full backup immediately after switching — until you do, the log cannot be backed up and will grow unbounded.

---

BACKUP DATABASE

Full Backup

-- Full backup to disk
BACKUP DATABASE MyDatabase
TO DISK = 'D:\Backups\MyDatabase_Full_20240315.bak'
WITH
    COMPRESSION,              -- Recommended — reduces size significantly
    CHECKSUM,                 -- Verify backup integrity during write
    STATS = 10,               -- Progress reporting every 10%
    NAME = 'MyDatabase Full Backup 2024-03-15';
GO

-- Full backup with timestamp in filename (useful for scripted backups)
DECLARE @BackupPath NVARCHAR(500) =
    'D:\Backups\MyDatabase_Full_' + FORMAT(GETDATE(), 'yyyyMMdd_HHmmss') + '.bak';
BACKUP DATABASE MyDatabase TO DISK = @BackupPath WITH COMPRESSION, CHECKSUM;

Differential Backup

A differential backup captures only changes since the last full backup. Faster than full; faster to restore than many log backups.

BACKUP DATABASE MyDatabase
TO DISK = 'D:\Backups\MyDatabase_Diff_20240315.bak'
WITH DIFFERENTIAL, COMPRESSION, CHECKSUM, STATS = 10;

Transaction Log Backup

Must be taken regularly in FULL recovery mode to prevent log growth and enable point-in-time recovery.

BACKUP LOG MyDatabase
TO DISK = 'D:\Backups\MyDatabase_Log_20240315_1200.trn'
WITH COMPRESSION, CHECKSUM, STATS = 10;

---

RESTORE DATABASE

Restore from Full Backup Only

-- Restore to original location
RESTORE DATABASE MyDatabase
FROM DISK = 'D:\Backups\MyDatabase_Full_20240315.bak'
WITH
    RECOVERY,    -- Bring database online after this restore
    STATS = 10;

-- Restore to different location (new database)
RESTORE DATABASE MyDatabase_Restored
FROM DISK = 'D:\Backups\MyDatabase_Full_20240315.bak'
WITH
    MOVE 'MyDatabase'      TO 'E:\Data\MyDatabase_Restored.mdf',
    MOVE 'MyDatabase_log'  TO 'F:\Logs\MyDatabase_Restored_log.ldf',
    RECOVERY, STATS = 10;

Restore Full + Differential + Log Backups

-- Step 1: Restore full backup (NORECOVERY = keep restoring)
RESTORE DATABASE MyDatabase
FROM DISK = 'D:\Backups\MyDatabase_Full_20240315.bak'
WITH NORECOVERY, STATS = 10;

-- Step 2: Apply differential backup (NORECOVERY)
RESTORE DATABASE MyDatabase
FROM DISK = 'D:\Backups\MyDatabase_Diff_20240315.bak'
WITH NORECOVERY, STATS = 10;

-- Step 3: Apply transaction log backups in order (NORECOVERY until last one)
RESTORE LOG MyDatabase
FROM DISK = 'D:\Backups\MyDatabase_Log_20240315_0800.trn'
WITH NORECOVERY;

RESTORE LOG MyDatabase
FROM DISK = 'D:\Backups\MyDatabase_Log_20240315_0900.trn'
WITH NORECOVERY;

-- Step 4: Final log backup — WITH RECOVERY brings database online
RESTORE LOG MyDatabase
FROM DISK = 'D:\Backups\MyDatabase_Log_20240315_1000.trn'
WITH RECOVERY;

Point-in-Time Restore

-- Restore to specific point in time (requires FULL recovery model)
RESTORE LOG MyDatabase
FROM DISK = 'D:\Backups\MyDatabase_Log_20240315_1200.trn'
WITH
    RECOVERY,
    STOPAT = '2024-03-15 11:45:00';

---

RESTORE VERIFYONLY

Verify a backup file is readable without performing the restore:

RESTORE VERIFYONLY
FROM DISK = 'D:\Backups\MyDatabase_Full_20240315.bak'
WITH CHECKSUM;

-- Also check backup header
RESTORE HEADERONLY FROM DISK = 'D:\Backups\MyDatabase_Full_20240315.bak';

-- List files in a backup (for MOVE statements)
RESTORE FILELISTONLY FROM DISK = 'D:\Backups\MyDatabase_Full_20240315.bak';

---

Check Backup History

-- Recent backups for a database
SELECT TOP 20
    bs.database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) AS duration_seconds,
    bs.backup_size / 1048576.0                                     AS backup_size_mb,
    bs.compressed_backup_size / 1048576.0                          AS compressed_mb,
    CASE bs.type
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Log'
    END                                                            AS backup_type,
    bmf.physical_device_name                                       AS backup_file
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = 'MyDatabase'
ORDER BY bs.backup_finish_date DESC;

-- Check when last full backup was taken for all databases
SELECT
    database_name,
    MAX(backup_finish_date) AS last_full_backup,
    DATEDIFF(HOUR, MAX(backup_finish_date), GETDATE()) AS hours_since_backup
FROM msdb.dbo.backupset
WHERE type = 'D'  -- D = Full
GROUP BY database_name
ORDER BY last_full_backup ASC;

---

Backup Strategy Recommendations

Database Size	RPO	Recommended Strategy
< 50 GB	Hours	Nightly full + 4-hourly log backups
50–500 GB	1 hour	Nightly full + daily differential + hourly log
> 500 GB	15 min	Weekly full + nightly differential + 15-min log

Always:

• Store backups on a separate disk/server from the database
• Test restores periodically (at least monthly) — an untested backup is not a backup
• Use WITH COMPRESSION — typically 60-80% size reduction
• Use WITH CHECKSUM — detects media corruption at backup time, not restore time