44 — Backup and Restore

-- Minimum viable backup
BACKUP DATABASE AdventureWorks2022
TO DISK = 'D:\Backups\AdventureWorks2022_full.bak'
WITH
    COMPRESSION,
    CHECKSUM,           -- validates each page; detects torn pages at backup time
    STATS = 10;         -- progress every 10%

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

BACKUP LOG AdventureWorks2022
TO DISK = 'D:\Backups\AdventureWorks2022_log_20260317_0100.bak'
WITH
    COMPRESSION,
    CHECKSUM,
    STATS = 10;

-- Default is NOINIT — appends to file. INIT overwrites.
BACKUP DATABASE AdventureWorks2022
TO DISK = 'D:\Backups\AW_full.bak'
WITH COMPRESSION, CHECKSUM, INIT;   -- recommended: always use INIT for scheduled jobs

-- NOINIT appends: one file may hold many backups (a "backup family set").
-- Requires RESTORE HEADERONLY to enumerate them.

Full (Sunday)
  ├─ Diff (Monday)
  ├─ Diff (Tuesday)   ← restore this if recovering to Wednesday midnight
  └─ Log every 15 min (Mon–Wed)

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE name = 'AdventureWorks2022';

-- Minimal log backup (scheduled job, e.g., every 15 minutes)
BACKUP LOG AdventureWorks2022
TO DISK = 'D:\LogBackups\AW_log_'
        + REPLACE(CONVERT(VARCHAR(20), GETDATE(), 120), ':', '') + '.bak'
WITH COMPRESSION, CHECKSUM, INIT;

Monday diff:   only Monday's changes (small)
Tuesday diff:  Monday + Tuesday changes (medium)
Wednesday diff: Monday + Tuesday + Wednesday changes (larger)

-- Step 1: restore full, do not recover yet
RESTORE DATABASE AdventureWorks2022
FROM DISK = 'D:\Backups\AW_full.bak'
WITH NORECOVERY, REPLACE, STATS = 10;

-- Step 2: restore the most recent differential, do not recover
RESTORE DATABASE AdventureWorks2022
FROM DISK = 'D:\Backups\AW_diff_tuesday.bak'
WITH NORECOVERY, STATS = 10;

-- Step 3: restore each log backup in sequence
RESTORE LOG AdventureWorks2022
FROM DISK = 'D:\LogBackups\AW_log_20260317_0000.bak'
WITH NORECOVERY;

-- ... repeat for each log ...

-- Step 4: recover the database
RESTORE DATABASE AdventureWorks2022 WITH RECOVERY;

BACKUP DATABASE AdventureWorks2022
TO DISK = 'D:\Backups\AW_copy_only.bak'
WITH COPY_ONLY, COMPRESSION, CHECKSUM;

-- Copy-only log (does not affect log chain position)
BACKUP LOG AdventureWorks2022
TO DISK = 'D:\Backups\AW_log_copy_only.bak'
WITH COPY_ONLY, COMPRESSION, CHECKSUM;

-- Stripe backup across 4 files
BACKUP DATABASE AdventureWorks2022
TO
    DISK = 'D:\Backups\AW_stripe1.bak',
    DISK = 'E:\Backups\AW_stripe2.bak',
    DISK = 'F:\Backups\AW_stripe3.bak',
    DISK = 'G:\Backups\AW_stripe4.bak'
WITH COMPRESSION, CHECKSUM, INIT, STATS = 10;

-- Restore must reference ALL stripe files
RESTORE DATABASE AdventureWorks2022
FROM
    DISK = 'D:\Backups\AW_stripe1.bak',
    DISK = 'E:\Backups\AW_stripe2.bak',
    DISK = 'F:\Backups\AW_stripe3.bak',
    DISK = 'G:\Backups\AW_stripe4.bak'
WITH NORECOVERY, STATS = 10;

-- Step 1: create a master key in master if not present
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassw0rd!';

-- Step 2: create a certificate for backup encryption
CREATE CERTIFICATE BackupEncryptCert
WITH SUBJECT = 'Backup Encryption Certificate',
     EXPIRY_DATE = '2030-01-01';

-- Step 3: back up the certificate (CRITICAL — without this you cannot restore)
BACKUP CERTIFICATE BackupEncryptCert
TO FILE = 'D:\Certs\BackupEncryptCert.cer'
WITH PRIVATE KEY (
    FILE = 'D:\Certs\BackupEncryptCert.pvk',
    ENCRYPTION BY PASSWORD = 'CertKeyPassw0rd!'
);

-- Step 4: encrypted backup
BACKUP DATABASE AdventureWorks2022
TO DISK = 'D:\Backups\AW_encrypted.bak'
WITH
    COMPRESSION,
    CHECKSUM,
    ENCRYPTION (
        ALGORITHM = AES_256,
        SERVER CERTIFICATE = BackupEncryptCert
    );

-- Step 1: create a credential with S3 access key
CREATE CREDENTIAL [s3://my-bucket-endpoint/backups]
WITH
    IDENTITY = 'S3 Access Key',
    SECRET = 'ACCESSKEYID:SECRETACCESSKEY';  -- colon-delimited

-- Step 2: backup to S3 URL
BACKUP DATABASE AdventureWorks2022
TO URL = 's3://my-s3-host:9000/sql-backups/AW_full.bak'
WITH
    COMPRESSION,
    CHECKSUM,
    ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = BackupEncryptCert),
    STATS = 10;

-- Step 3: restore from S3
RESTORE DATABASE AdventureWorks2022
FROM URL = 's3://my-s3-host:9000/sql-backups/AW_full.bak'
WITH
    MOVE 'AdventureWorks2022' TO 'D:\Data\AW.mdf',
    MOVE 'AdventureWorks2022_log' TO 'D:\Log\AW_log.ldf',
    RECOVERY, STATS = 10;

-- Using a Shared Access Signature (recommended)
CREATE CREDENTIAL [https://mystorageacct.blob.core.windows.net/sql-backups]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
     SECRET = 'sv=2023-...&sig=...';  -- SAS token without leading '?'

BACKUP DATABASE AdventureWorks2022
TO URL = 'https://mystorageacct.blob.core.windows.net/sql-backups/AW_full.bak'
WITH COMPRESSION, CHECKSUM, STATS = 10;

-- Enable Azure Blob managed backup (SQL Server 2014+)
EXEC msdb.managed_backup.sp_backup_config_basic
    @database_name = 'AdventureWorks2022',
    @container_url = 'https://mystorageacct.blob.core.windows.net/sql-backups',
    @retention_days = 30;

-- Inspect backup header (backup sets in file)
RESTORE HEADERONLY FROM DISK = 'D:\Backups\AW_full.bak';

-- Inspect file list (logical and physical file names)
RESTORE FILELISTONLY FROM DISK = 'D:\Backups\AW_full.bak';

-- Inspect backup label (media set info)
RESTORE LABELONLY FROM DISK = 'D:\Backups\AW_full.bak';

RESTORE DATABASE AdventureWorks2022
FROM DISK = 'D:\Backups\AW_full.bak'
WITH
    MOVE 'AdventureWorks2022'     TO 'D:\Data\AW2022.mdf',
    MOVE 'AdventureWorks2022_log' TO 'E:\Log\AW2022_log.ldf',
    REPLACE,        -- overwrite existing database
    RECOVERY,       -- bring online immediately (use NORECOVERY if applying more backups)
    STATS = 10;

-- Backup set 3 in a NOINIT file
RESTORE DATABASE AdventureWorks2022
FROM DISK = 'D:\Backups\multi_backups.bak'
WITH FILE = 3, NORECOVERY, STATS = 10;

-- STANDBY: allows read queries between log restores
RESTORE LOG AdventureWorks2022
FROM DISK = 'D:\LogBackups\AW_log_0100.bak'
WITH STANDBY = 'D:\Standby\AW_undo.bak';

-- Query the standby database here...

-- Apply next log (must be in restoring state first — STANDBY handles this automatically)
RESTORE LOG AdventureWorks2022
FROM DISK = 'D:\LogBackups\AW_log_0115.bak'
WITH STANDBY = 'D:\Standby\AW_undo.bak';

-- Final recovery
RESTORE DATABASE AdventureWorks2022 WITH RECOVERY;

-- Full restore chain to a specific point in time (e.g., just before an accidental delete)

-- 1. Restore full backup
RESTORE DATABASE AdventureWorks2022
FROM DISK = 'D:\Backups\AW_full_20260316.bak'
WITH NORECOVERY, MOVE 'AdventureWorks2022' TO 'D:\Data\AW.mdf',
     MOVE 'AdventureWorks2022_log' TO 'E:\Log\AW.ldf', REPLACE;

-- 2. Restore differential (skip if none between full and target)
RESTORE DATABASE AdventureWorks2022
FROM DISK = 'D:\Backups\AW_diff_20260316_2200.bak'
WITH NORECOVERY;

-- 3. Restore log backups, stopping at the target time on the last one
RESTORE LOG AdventureWorks2022
FROM DISK = 'D:\LogBackups\AW_log_20260317_0000.bak'
WITH NORECOVERY;

RESTORE LOG AdventureWorks2022
FROM DISK = 'D:\LogBackups\AW_log_20260317_0015.bak'
WITH NORECOVERY;

-- Target time: stop at 00:23:10 on March 17
RESTORE LOG AdventureWorks2022
FROM DISK = 'D:\LogBackups\AW_log_20260317_0030.bak'
WITH STOPAT = '2026-03-17T00:23:10', RECOVERY;

-- Stop at a specific LSN (more precise than time)
RESTORE LOG AdventureWorks2022
FROM DISK = 'D:\LogBackups\AW_log.bak'
WITH STOPATMARK = 'lsn:0000000025000000100000001',
     RECOVERY;

-- Tail-log backup: WITH NORECOVERY takes the source database offline
-- and prevents new transactions during the restore process
BACKUP LOG AdventureWorks2022
TO DISK = 'D:\LogBackups\AW_taillog.bak'
WITH NORECOVERY,  -- database goes into restoring state after this
     COMPRESSION,
     CHECKSUM,
     STATS = 5;

-- If the database is inaccessible (corruption, disk failure), use NO_TRUNCATE
BACKUP LOG AdventureWorks2022
TO DISK = 'D:\LogBackups\AW_taillog.bak'
WITH NO_TRUNCATE,  -- backs up log even if data files are inaccessible
     COMPRESSION,
     CHECKSUM;

-- Step 1: restore primary filegroup first
RESTORE DATABASE AdventureWorks2022
FILEGROUP = 'PRIMARY'
FROM DISK = 'D:\Backups\AW_full.bak'
WITH PARTIAL, NORECOVERY;

-- Step 2: restore and recover primary (database is partially online)
RESTORE DATABASE AdventureWorks2022 WITH RECOVERY;

-- Step 3: restore secondary filegroups online (database remains accessible)
RESTORE DATABASE AdventureWorks2022
FILEGROUP = 'FG_Historical'
FROM DISK = 'D:\Backups\AW_full.bak'
WITH NORECOVERY;

RESTORE LOG AdventureWorks2022
FROM DISK = 'D:\LogBackups\AW_log.bak'
WITH RECOVERY;

-- Restore backup to a temporary database
RESTORE DATABASE AdventureWorks2022_CheckCopy
FROM DISK = 'D:\Backups\AW_full.bak'
WITH
    MOVE 'AdventureWorks2022'     TO 'D:\Data\AW_check.mdf',
    MOVE 'AdventureWorks2022_log' TO 'E:\Log\AW_check_log.ldf',
    RECOVERY;

-- Run CHECKDB — use PHYSICAL_ONLY for speed, full check for thoroughness
DBCC CHECKDB (AdventureWorks2022_CheckCopy) WITH NO_INFOMSGS, ALL_ERRORMSGS;

-- Drop the check copy when done
DROP DATABASE AdventureWorks2022_CheckCopy;

-- Verify backup integrity (reads entire file)
RESTORE VERIFYONLY
FROM DISK = 'D:\Backups\AW_full.bak'
WITH CHECKSUM;   -- validates page checksums if backup was taken WITH CHECKSUM

-- Enable compression by default (SQL Server 2008+ Enterprise; 2008 R2+ Standard)
EXEC sp_configure 'backup compression default', 1;
RECONFIGURE;

-- Per-backup override
BACKUP DATABASE AdventureWorks2022
TO DISK = 'D:\Backups\AW.bak'
WITH COMPRESSION;           -- explicit compression

BACKUP DATABASE AdventureWorks2022
TO DISK = 'D:\Backups\AW_nocomp.bak'
WITH NO_COMPRESSION;        -- explicit no compression (overrides default)

SELECT
    d.name                              AS database_name,
    d.recovery_model_desc,
    MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) AS last_full,
    MAX(CASE WHEN bs.type = 'I' THEN bs.backup_finish_date END) AS last_diff,
    MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) AS last_log
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset bs
    ON bs.database_name = d.name
    AND bs.backup_finish_date > DATEADD(DAY, -7, GETDATE())
WHERE d.database_id > 4   -- exclude system databases
GROUP BY d.name, d.recovery_model_desc
ORDER BY last_full DESC;

SELECT d.name AS database_name, d.recovery_model_desc,
       MAX(bs.backup_finish_date) AS last_full_backup
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset bs
    ON bs.database_name = d.name AND bs.type = 'D'
WHERE d.database_id > 4
  AND d.state_desc = 'ONLINE'
GROUP BY d.name, d.recovery_model_desc
HAVING MAX(bs.backup_finish_date) < DATEADD(DAY, -1, GETDATE())
   OR  MAX(bs.backup_finish_date) IS NULL
ORDER BY last_full_backup ASC;

SELECT TOP 100
    bs.database_name,
    bs.type,
    bs.backup_start_date,
    bs.backup_finish_date,
    DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) AS duration_sec,
    CAST(bs.backup_size / 1024.0 / 1024.0 AS DECIMAL(10,2))          AS backup_size_mb,
    CAST(bs.compressed_backup_size / 1024.0 / 1024.0 AS DECIMAL(10,2)) AS compressed_mb,
    bmf.physical_device_name
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = 'AdventureWorks2022'
ORDER BY bs.backup_start_date DESC;

-- Find which backups are needed to restore to a specific time
DECLARE @TargetDB   NVARCHAR(128) = 'AdventureWorks2022';
DECLARE @TargetTime DATETIME      = '2026-03-17T00:23:10';

-- Last full before target time
SELECT TOP 1 bs.backup_set_id, bs.type, bs.backup_start_date,
             bs.backup_finish_date, bmf.physical_device_name
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = @TargetDB
  AND bs.type = 'D'
  AND bs.backup_finish_date <= @TargetTime
ORDER BY bs.backup_finish_date DESC;

-- Example: full backup of all user databases
EXECUTE dbo.DatabaseBackup
    @Databases = 'USER_DATABASES',
    @Directory = 'D:\Backups',
    @BackupType = 'FULL',
    @Compress = 'Y',
    @CheckSum = 'Y',
    @CleanupTime = 72;   -- remove files older than 72 hours

-- Log backup
EXECUTE dbo.DatabaseBackup
    @Databases = 'USER_DATABASES',
    @Directory = 'D:\LogBackups',
    @BackupType = 'LOG',
    @Compress = 'Y',
    @CheckSum = 'Y',
    @CleanupTime = 24;

-- Identify latest full + differential + logs covering target time
-- (query msdb as shown in Section 20, then build the restore chain)

RESTORE DATABASE AdventureWorks2022_Recovered
FROM DISK = 'D:\Backups\AW_full_20260316.bak'
WITH
    MOVE 'AdventureWorks2022' TO 'D:\Data\AW_recovered.mdf',
    MOVE 'AdventureWorks2022_log' TO 'E:\Log\AW_recovered.ldf',
    NORECOVERY, REPLACE, STATS = 10;

RESTORE DATABASE AdventureWorks2022_Recovered
FROM DISK = 'D:\Backups\AW_diff_20260316.bak'
WITH NORECOVERY, STATS = 10;

-- Apply logs up to (but not including) last one
RESTORE LOG AdventureWorks2022_Recovered
FROM DISK = 'D:\LogBackups\AW_log_0000.bak'
WITH NORECOVERY;

-- Apply last log with STOPAT
RESTORE LOG AdventureWorks2022_Recovered
FROM DISK = 'D:\LogBackups\AW_log_0015.bak'
WITH STOPAT = '2026-03-17T00:23:10', RECOVERY;

-- Run on a non-production server or with a different database name
RESTORE DATABASE AdventureWorks2022_Test
FROM DISK = 'D:\Backups\AW_full_latest.bak'
WITH
    MOVE 'AdventureWorks2022' TO 'D:\TestData\AW_test.mdf',
    MOVE 'AdventureWorks2022_log' TO 'D:\TestLog\AW_test.ldf',
    RECOVERY, REPLACE, STATS = 10;

-- Verify data integrity
DBCC CHECKDB (AdventureWorks2022_Test) WITH NO_INFOMSGS, ALL_ERRORMSGS;

-- Check row counts against known-good values
SELECT COUNT(*) FROM AdventureWorks2022_Test.Person.Person;

-- Clean up
DROP DATABASE AdventureWorks2022_Test;

-- Log result to a monitoring table
INSERT INTO DBA.dbo.BackupTestResults (TestDate, DatabaseName, Result)
VALUES (GETDATE(), 'AdventureWorks2022', 'PASS');

-- 1. Take tail-log backup if log files accessible
BACKUP LOG AdventureWorks2022
TO DISK = 'D:\LogBackups\AW_emergency_taillog.bak'
WITH NO_TRUNCATE, COMPRESSION, CHECKSUM;

-- 2. Restore full (last known good)
RESTORE DATABASE AdventureWorks2022
FROM DISK = 'D:\Backups\AW_full_latest.bak'
WITH NORECOVERY, REPLACE;

-- 3. Apply latest differential
RESTORE DATABASE AdventureWorks2022
FROM DISK = 'D:\Backups\AW_diff_latest.bak'
WITH NORECOVERY;

-- 4. Apply all transaction log backups in order
-- (query msdb to enumerate them, then RESTORE LOG each)

-- 5. Apply tail-log backup
RESTORE LOG AdventureWorks2022
FROM DISK = 'D:\LogBackups\AW_emergency_taillog.bak'
WITH NORECOVERY;

-- 6. Recover
RESTORE DATABASE AdventureWorks2022 WITH RECOVERY;

-- Alert if any FULL recovery database has no log backup in last 60 minutes
SELECT d.name, d.recovery_model_desc,
       MAX(bs.backup_finish_date) AS last_log_backup,
       DATEDIFF(MINUTE, MAX(bs.backup_finish_date), GETDATE()) AS minutes_since_log
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset bs
    ON bs.database_name = d.name AND bs.type = 'L'
WHERE d.recovery_model_desc = 'FULL'
  AND d.database_id > 4
  AND d.state_desc = 'ONLINE'
GROUP BY d.name, d.recovery_model_desc
HAVING MAX(bs.backup_finish_date) < DATEADD(MINUTE, -60, GETDATE())
   OR  MAX(bs.backup_finish_date) IS NULL;