Sqlserver Backup Recovery

SQL Server Backup and Recovery

Recovery Model Selection

Choose the recovery model before designing any backup strategy. Changing the recovery model mid-stream breaks the log chain.

-- Check current recovery models
SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE database_id > 4;

-- Set recovery model
ALTER DATABASE [YourDB] SET RECOVERY FULL;        -- Production databases with RPO requirements
ALTER DATABASE [YourDB] SET RECOVERY BULK_LOGGED; -- During ETL windows only
ALTER DATABASE [YourDB] SET RECOVERY SIMPLE;      -- Dev/QA, no point-in-time recovery needed

Recovery model decision guide:

Backup Strategy Design

Match backup frequency to your RPO and RTO requirements.

Retention recommendation:

• Full backups: 4 weeks
• Differential backups: 2 weeks
• Log backups: 1 week (or longer based on compliance)

Key Backup Commands

Full backup

BACKUP DATABASE [YourDB]
TO DISK = N'D:\Backups\YourDB_Full_20240115.bak'
WITH
    COMPRESSION,
    CHECKSUM,
    STATS = 10,
    NAME = N'YourDB Full Backup';

Differential backup

BACKUP DATABASE [YourDB]
TO DISK = N'D:\Backups\YourDB_Diff_20240115_1800.bak'
WITH DIFFERENTIAL, COMPRESSION, CHECKSUM, STATS = 10;

Transaction log backup

BACKUP LOG [YourDB]
TO DISK = N'D:\Backups\YourDB_Log_20240115_1800.trn'
WITH COMPRESSION, CHECKSUM, STATS = 10;

Copy-only backup (does not break differential chain)

BACKUP DATABASE [YourDB]
TO DISK = N'D:\Backups\YourDB_CopyOnly.bak'
WITH COPY_ONLY, COMPRESSION, CHECKSUM, STATS = 10;

Striped backup (faster for large databases)

BACKUP DATABASE [YourDB]
TO DISK = N'D:\Backups\YourDB_Full_1.bak',
   DISK = N'E:\Backups\YourDB_Full_2.bak',
   DISK = N'F:\Backups\YourDB_Full_3.bak'
WITH COMPRESSION, CHECKSUM, STATS = 10;

Always include COMPRESSION and CHECKSUM. Enable compression as default at instance level:

EXEC sp_configure 'backup compression default', 1;
RECONFIGURE;

Backup Verification

Verify backup file integrity (no restore required)

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

Inspect backup contents before restoring

-- List all backup sets in the file
RESTORE HEADERONLY FROM DISK = N'D:\Backups\YourDB_Full_20240115.bak';

-- List logical file names (needed for MOVE during restore)
RESTORE FILELISTONLY FROM DISK = N'D:\Backups\YourDB_Full_20240115.bak';

-- Show media label and checksum info
RESTORE LABELONLY FROM DISK = N'D:\Backups\YourDB_Full_20240115.bak';

Point-in-Time Recovery Workflow

Use this workflow to restore a database to a specific point in time.

Step 1 — Take a tail-log backup (capture uncommitted transactions before restore)

BACKUP LOG [YourDB]
TO DISK = N'D:\Backups\YourDB_TailLog.trn'
WITH NO_TRUNCATE, NORECOVERY, CHECKSUM;
-- NO_TRUNCATE allows backup even if database is inaccessible

Step 2 — Restore the most recent full backup (leave in NORECOVERY state)

RESTORE DATABASE [YourDB]
FROM DISK = N'D:\Backups\YourDB_Full_20240115.bak'
WITH NORECOVERY, REPLACE, STATS = 10;

Step 3 — Restore the most recent differential backup (if available)

RESTORE DATABASE [YourDB]
FROM DISK = N'D:\Backups\YourDB_Diff_20240115_1800.bak'
WITH NORECOVERY, STATS = 10;

Step 4 — Restore log backups in chronological order until reaching the target time

RESTORE LOG [YourDB]
FROM DISK = N'D:\Backups\YourDB_Log_20240115_1900.trn'
WITH NORECOVERY, STATS = 10;

RESTORE LOG [YourDB]
FROM DISK = N'D:\Backups\YourDB_Log_20240115_2000.trn'
WITH NORECOVERY, STATS = 10;

Step 5 — Apply the final log backup with STOPAT (point-in-time)

RESTORE LOG [YourDB]
FROM DISK = N'D:\Backups\YourDB_Log_20240115_2100.trn'
WITH RECOVERY, STOPAT = N'2024-01-15 20:45:00';
-- RECOVERY brings the database online; no more logs can be applied after this

Restore to a different database name or server

RESTORE DATABASE [YourDB_Test]
FROM DISK = N'D:\Backups\YourDB_Full_20240115.bak'
WITH
    MOVE N'YourDB_Data' TO N'D:\Data\YourDB_Test.mdf',
    MOVE N'YourDB_Log'  TO N'D:\Log\YourDB_Test_log.ldf',
    RECOVERY, REPLACE, STATS = 10;
-- Get logical file names from RESTORE FILELISTONLY first

Backup History Monitoring Queries

Recent backup history

SELECT TOP 50
    bs.database_name,
    CASE bs.type
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Log'
    END AS backup_type,
    bs.backup_start_date,
    bs.backup_finish_date,
    DATEDIFF(MINUTE, bs.backup_start_date, bs.backup_finish_date) AS duration_min,
    bs.backup_size / 1024.0 / 1024.0 / 1024.0            AS size_gb,
    bs.compressed_backup_size / 1024.0 / 1024.0 / 1024.0 AS compressed_gb,
    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
ORDER BY bs.backup_start_date DESC;

Databases without recent full backup (alert if > 25 hours)

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

FULL recovery databases missing log backups (alert if > 60 minutes)

SELECT
    d.name AS database_name,
    MAX(bs.backup_finish_date)                                      AS last_log_backup,
    DATEDIFF(MINUTE, MAX(bs.backup_finish_date), GETDATE())         AS minutes_since_log_backup
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset bs
    ON d.name = bs.database_name AND bs.type = 'L'
WHERE d.database_id > 4
  AND d.recovery_model_desc = 'FULL'
  AND d.state = 0
GROUP BY d.name
HAVING MAX(bs.backup_finish_date) IS NULL
    OR MAX(bs.backup_finish_date) < DATEADD(MINUTE, -60, GETDATE())
ORDER BY minutes_since_log_backup DESC;

Common Backup/Restore Issues

References

• Backup commands — complete T-SQL backup and restore commands with all options
• Restore scenarios — step-by-step procedures for each restore scenario
• Backup monitoring — monitoring queries for backup health and history
• Examples — full restore, PITR, cross-server restore, broken log chain fix, backup verification