Backup and Restore

42.1 Backup Types

Entire database plus enough log to be consistent Extents changed since the last full backup Transaction log Log records since the last log backup FULL / BULK_LOGGED

Last log backup before a restore (captures recent work) Doesn't break the backup chain File / Filegroup All (best with FULL) Primary + read-write filegroups Snapshot backup

Storage snapshots (T-SQL snapshot backup, 2022)

42.2 Backup Commands

BACKUP DATABASE ShopDB TO DISK = 'B:\Backup\ShopDB_Full.bak'
WITH COMPRESSION, CHECKSUM, STATS = 10, INIT,
     ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = BackupCert);

BACKUP DATABASE ShopDB TO DISK = 'B:\Backup\ShopDB_Diff.bak' WITH DIFFERENTIAL, COMPRESSION, CHECKSUM;
BACKUP LOG ShopDB TO DISK = 'B:\Backup\ShopDB_Log_1015.trn' WITH COMPRESSION, CHECKSUM;
BACKUP DATABASE ShopDB TO DISK = 'B:\Backup\ShopDB_CopyOnly.bak' WITH COPY_ONLY;

-- Striped across multiple files for speed
BACKUP DATABASE ShopDB TO DISK = 'B:\b1.bak', DISK = 'C:\b2.bak' WITH COMPRESSION;

-- Backup to URL (Azure Blob / S3-compatible in 2022)
BACKUP DATABASE ShopDB TO URL = 'https://acct.blob.core.windows.net/backups/ShopDB.bak' WITH COMPRESSION;
BACKUP DATABASE ShopDB TO URL = 's3://s3.region.amazonaws.com/bucket/ShopDB.bak' WITH COMPRESSION;

-- Compression algorithm (2022): ZSTD in 2025, Intel QAT
BACKUP DATABASE ShopDB TO DISK = 'B:\ShopDB.bak' WITH COMPRESSION (ALGORITHM = ZSTD);

42.3 Verifying Backups

A backup is only as good as your last successful test restore.

RESTORE VERIFYONLY FROM DISK = 'B:\Backup\ShopDB_Full.bak' WITH CHECKSUM;
RESTORE HEADERONLY   FROM DISK = 'B:\Backup\ShopDB_Full.bak';
RESTORE FILELISTONLY FROM DISK = 'B:\Backup\ShopDB_Full.bak';
-- Backup history
SELECT database_name, type, backup_start_date, backup_finish_date, backup_size
FROM msdb.dbo.backupset ORDER BY backup_start_date DESC;

42.4 Restore Scenarios

-- 1. Tail-log backup (if the database is still accessible)
BACKUP LOG ShopDB TO DISK = 'B:\Backup\ShopDB_Tail.trn' WITH NORECOVERY, NO_TRUNCATE;

-- 2. Restore sequence: Full → latest Diff → all Logs → Tail
RESTORE DATABASE ShopDB FROM DISK = 'B:\Backup\ShopDB_Full.bak' WITH NORECOVERY, REPLACE;
RESTORE DATABASE ShopDB FROM DISK = 'B:\Backup\ShopDB_Diff.bak' WITH NORECOVERY;
RESTORE LOG ShopDB FROM DISK = 'B:\Backup\ShopDB_Log_1015.trn' WITH NORECOVERY;
RESTORE LOG ShopDB FROM DISK = 'B:\Backup\ShopDB_Tail.trn' WITH RECOVERY;

-- 3. Point-in-time restore
RESTORE LOG ShopDB FROM DISK = 'B:\Backup\ShopDB_Log_1030.trn'
WITH STOPAT = '2025-10-24 10:27:00', RECOVERY;
-- also STOPATMARK / STOPBEFOREMARK with marked transactions

-- 4. Restore as a new database
RESTORE DATABASE ShopDB_Test FROM DISK = 'B:\Backup\ShopDB_Full.bak'
WITH MOVE 'ShopDB_Data' TO 'D:\Data\ShopDB_Test.mdf', MOVE 'ShopDB_Log' TO 'L:\Log\ShopDB_Test.ldf', RECOVERY;

-- 5. STANDBY (read-only between log restores)
RESTORE LOG ShopDB FROM DISK = '...' WITH STANDBY = 'D:\Data\ShopDB_undo.dat';

-- 6. Page restore (repair specific corrupt pages)
RESTORE DATABASE ShopDB PAGE = '1:57, 1:202' FROM DISK = 'B:\Backup\ShopDB_
…

42.5 Backup Strategy Design

Define the RPO (Recovery Point Objective, i.e., acceptable data loss) and RTO (Recovery Time Objective, i.e., acceptable downtime).

Example: Full weekly, Differential daily, Log every 15 minutes → RPO ≤ 15 minutes.

Follow the 3-2-1 rule: 3 copies, 2 media types, 1 offsite or immutable copy.

Back up system databases (master, msdb, model) too, along with TDE certificates and keys.

Automate with Ola Hallengren's scripts or maintenance plans, and monitor for failures.

42.6 Rebuilding and Restoring System Databases

Restore master: start in single-user mode (-m), then RESTORE DATABASE master ....

Rebuild system databases from setup: setup.exe /ACTION=REBUILDDATABASE.