Ev Vasilev

Posted on Jan 15

Building a Solid SQL Backup & Recovery Strategy (With T-SQL PoC)

While Documenting Backup Strategies, I Turned My Notes into This Quick Post

I’ve been working on thorough documentation for backup strategies across different databases. While putting together the artifacts, I figured I'd share a short post on SQL Server backup and restore techniques. I hope it's useful for anyone looking to ramp up their SQL backups. I'll cover strategies for other databases in upcoming posts—stay tuned!

Overview

A reliable backup and recovery strategy is critical for any production SQL Server environment. Here’s a quick guide on how to set up full, differential, and transaction log backups (T-Log) for point-in-time restoration. We’ll also walk through a proof-of-concept script showing how to back up data, restore it, and skip unwanted transactions.

Why Care?

Protect against data loss and corruption
Enable faster restores when things go wrong
Meet compliance/audit requirements

1. Backup Types & Scheduling

Full Backup

Captures everything: data, schema, indexes.
Frequency: Often once a week or after major DB changes.

BACKUP DATABASE YourDatabase
TO DISK = 'C:\Backups\YourDB_Full.bak'
WITH INIT, NAME = 'Full Backup', STATS = 10;

Differential Backup

Only changes since the last full backup.
Frequency: Daily or multiple times a day.

BACKUP DATABASE YourDatabase
TO DISK = 'C:\Backups\YourDB_Diff.bak'
WITH DIFFERENTIAL, INIT, NAME = 'Differential Backup', STATS = 10;

Transaction Log Backup (T-Log)

Captures transaction log records since the last T-Log backup.
Frequency: Every 15–30 minutes (or more often if you can’t afford to lose data).

BACKUP LOG YourDatabase
TO DISK = 'C:\Backups\YourDB_Log.trn'
WITH INIT, NAME = 'T-Log Backup', STATS = 10;

Key: Make sure your database is in FULL (or BULK_LOGGED) recovery mode to allow T-Log backups.

2. Restoring Databases

Basic Restore Flow

Full Backup → WITH NORECOVERY
Differential Backup → WITH NORECOVERY
T-Log Backup → WITH NORECOVERY
Final Step → WITH RECOVERY

RESTORE DATABASE YourDatabase
FROM DISK = 'C:\Backups\YourDB_Full.bak'
WITH NORECOVERY, REPLACE;

RESTORE DATABASE YourDatabase
FROM DISK = 'C:\Backups\YourDB_Diff.bak'
WITH NORECOVERY;

RESTORE LOG YourDatabase
FROM DISK = 'C:\Backups\YourDB_Log.trn'
WITH NORECOVERY;

/* Once done with all backups/logs, finalize: */
RESTORE DATABASE YourDatabase WITH RECOVERY;

Point-in-Time Recovery

If you need to exclude bad transactions, you can stop at a specific time:

RESTORE LOG YourDatabase
FROM DISK = 'C:\Backups\YourDB_Log.trn'
WITH STOPAT = 'YYYY-MM-DD HH:MM:SS',
     RECOVERY;

3. Common Pitfalls

Transaction Log Is Full (Error 9002)

Backup the Log to free space in the log file.
Check for open transactions using DBCC OPENTRAN(YourDatabase).
Avoid switching to SIMPLE unless in an absolute emergency (it breaks point-in-time recovery).

Corrupted or Missing Backups

Always verify backups with RESTORE VERIFYONLY.
Include timestamps in filenames (e.g., YourDB_Full_20250114.bak) to avoid overwrites.

4. The Proof-of-Concept (PoC) T-SQL Script

Below is a single script you can run in a test environment. It creates a sample database, does full, differential, and transaction log backups, then simulates a disaster and recovers up to a chosen point in time (excluding unwanted rows).

Note: Adjust file paths to match your environment (e.g., C:\Backups\..., /var/opt/mssql/backups/, etc.).

/****************************************************************************
 SINGLE SCRIPT: FULL, DIFF, TLOG BACKUPS + POINT-IN-TIME RESTORE (STOPAT)
****************************************************************************/

/**************************************************
 STEP 0: Clean Up from Previous POC Runs (If Any)
***************************************************/
USE master;
GO

IF DB_ID('PoCDatabase') IS NOT NULL
BEGIN
    PRINT '--- Dropping existing PoCDatabase for a clean start ---';
    ALTER DATABASE PoCDatabase 
        SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE PoCDatabase;
END
GO

/**************************************************
 STEP 1: Create PoCDatabase in FULL Recovery
***************************************************/
PRINT '--- Creating PoCDatabase in FULL recovery mode ---';
CREATE DATABASE PoCDatabase;
GO

ALTER DATABASE PoCDatabase 
    SET RECOVERY FULL;
GO

/**************************************************
 STEP 2: Create Table & Insert Initial Data
***************************************************/
USE PoCDatabase;
GO

IF OBJECT_ID('dbo.SampleData') IS NOT NULL
    DROP TABLE dbo.SampleData;

CREATE TABLE dbo.SampleData (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Description VARCHAR(100),
    CreatedDate DATETIME DEFAULT GETDATE()
);

INSERT INTO dbo.SampleData (Description)
VALUES ('Initial row 1'),
       ('Initial row 2'),
       ('Initial row 3');

SELECT 'Initial Data in PoCDatabase:' AS [Info], *
FROM dbo.SampleData;
GO

/**************************************************
 STEP 3: Perform a FULL Backup
***************************************************/
USE master;
GO

PRINT '--- Performing FULL Backup ---';
BACKUP DATABASE PoCDatabase
    TO DISK = N'/var/opt/mssql/backups/PoCDatabase_Full.bak'
    WITH INIT,
         NAME = 'PoCDatabase Full Backup',
         STATS = 10;

PRINT '--- Verifying FULL Backup ---';
RESTORE VERIFYONLY
    FROM DISK = N'/var/opt/mssql/backups/PoCDatabase_Full.bak';
GO

/**************************************************
 STEP 4: Insert Data & Perform DIFFERENTIAL Backup
***************************************************/
USE PoCDatabase;
GO

PRINT '--- Inserting data for differential backup ---';
INSERT INTO dbo.SampleData (Description)
VALUES ('Diff insert row A'),
       ('Diff insert row B');

SELECT 'After Diff Inserts:' AS [Info], *
FROM dbo.SampleData;
GO

USE master;
GO

PRINT '--- Performing DIFFERENTIAL Backup ---';
BACKUP DATABASE PoCDatabase
    TO DISK = N'/var/opt/mssql/backups/PoCDatabase_Diff.bak'
    WITH DIFFERENTIAL,
         INIT,
         NAME = 'PoCDatabase Differential Backup',
         STATS = 10;

PRINT '--- Verifying DIFFERENTIAL Backup ---';
RESTORE VERIFYONLY
    FROM DISK = N'/var/opt/mssql/backups/PoCDatabase_Diff.bak';
GO

/**************************************************
 STEP 5: Additional Data & TLog #1
***************************************************/
USE PoCDatabase;
GO

PRINT '--- Inserting TLog #1 data ---';
INSERT INTO dbo.SampleData (Description)
VALUES ('TLog #1 row - Keep me');

SELECT 'After TLog #1 Insert:' AS [Info], *
FROM dbo.SampleData;
GO

USE master;
GO

PRINT '--- Performing TRANSACTION LOG Backup #1 ---';
BACKUP LOG PoCDatabase
    TO DISK = N'/var/opt/mssql/backups/PoCDatabase_Log1.trn'
    WITH INIT,
         NAME = 'PoCDatabase Transaction Log Backup #1',
         STATS = 10;

PRINT '--- Verifying TLog #1 ---';
RESTORE VERIFYONLY
    FROM DISK = N'/var/opt/mssql/backups/PoCDatabase_Log1.trn';
GO

/**************************************************
 STEP 6: Insert Two Rows & TLog #2 
   - Keep the 1st
   - Exclude the 2nd with STOPAT
***************************************************/

/*
   We'll store the "stop time" in a global #temp table so we can 
   retrieve it even after new GO statements or dropping the DB.
   This avoids variable-scope issues.
*/
IF OBJECT_ID('tempdb..##StopTimeStore') IS NOT NULL
    DROP TABLE ##StopTimeStore;

CREATE TABLE ##StopTimeStore (
    StopTime DATETIME NOT NULL
);

USE PoCDatabase;
GO

PRINT '--- Inserting two new records for point-in-time demo ---';

/* 1) Insert the first record (we DO want to keep). */
INSERT INTO dbo.SampleData (Description)
VALUES ('Point-in-time row 1 - Good');
WAITFOR DELAY '00:00:01'; 
/* 2) Capture the exact timestamp AFTER the first insert. */
INSERT INTO ##StopTimeStore(StopTime)
VALUES (GETDATE());

/* 
   3) Wait 5 seconds so the second row definitely 
      has a later timestamp than the first.
*/
WAITFOR DELAY '00:00:05';  -- 5 seconds

/* 4) Insert second row (we want to exclude it). */
INSERT INTO dbo.SampleData (Description)
VALUES ('Point-in-time row 2 - EXCLUDE');

SELECT 'Two new rows inserted (pre TLog #2):' AS [Info], *
FROM dbo.SampleData
ORDER BY ID;
GO

USE master;
GO

PRINT '--- Performing TRANSACTION LOG Backup #2 (contains both new rows) ---';
BACKUP LOG PoCDatabase
    TO DISK = N'/var/opt/mssql/backups/PoCDatabase_Log2.trn'
    WITH INIT,
         NAME = 'PoCDatabase Transaction Log Backup #2',
         STATS = 10;

PRINT '--- Verifying TLog #2 ---';
RESTORE VERIFYONLY
    FROM DISK = N'/var/opt/mssql/backups/PoCDatabase_Log2.trn';
GO

/**************************************************
 STEP 7: Simulate Disaster & Restore to STOPAT
***************************************************/
USE master;
GO

PRINT '--- Dropping PoCDatabase to simulate disaster ---';
IF DB_ID('PoCDatabase') IS NOT NULL
BEGIN
    ALTER DATABASE PoCDatabase 
        SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE PoCDatabase;
END
GO

PRINT '--- Restoring FULL Backup (NORECOVERY) ---';
RESTORE DATABASE PoCDatabase
    FROM DISK = N'/var/opt/mssql/backups/PoCDatabase_Full.bak'
    WITH NORECOVERY,
         REPLACE,
         STATS = 10;
GO

PRINT '--- Restoring DIFFERENTIAL Backup (NORECOVERY) ---';
RESTORE DATABASE PoCDatabase
    FROM DISK = N'/var/opt/mssql/backups/PoCDatabase_Diff.bak'
    WITH NORECOVERY,
         STATS = 10;
GO

PRINT '--- Restoring TLog #1 (NORECOVERY) ---';
RESTORE LOG PoCDatabase
    FROM DISK = N'/var/opt/mssql/backups/PoCDatabase_Log1.trn'
    WITH NORECOVERY,
         STATS = 10;
GO

/*
   Retrieve the saved stop time (captured after first row insertion).
   We'll restore TLog #2 up to (but NOT including) the second row.
*/
DECLARE @StopTime DATETIME;
SELECT TOP 1 @StopTime = StopTime 
FROM ##StopTimeStore;  -- read from global temp table

PRINT '--- Restoring TLog #2 with STOPAT (RECOVERY) ---';
RESTORE LOG PoCDatabase
    FROM DISK = N'/var/opt/mssql/backups/PoCDatabase_Log2.trn'
    WITH 
        STOPAT = @StopTime,   -- exclude row(s) inserted after this time
        RECOVERY,
        STATS = 10;
GO

/**************************************************
 STEP 8: Validate Data
***************************************************/
USE PoCDatabase;
GO

PRINT '--- Final State: only the first new row is present ---';
SELECT 'FINAL DATABASE STATE:' AS [Info], *
FROM dbo.SampleData
ORDER BY ID;

PRINT '--- Point-in-time restore POC COMPLETE ---';
GO

When you run this script, you’ll see that the second row (the one we wanted to exclude) never makes it back into the restored database.

5. Final Tips

Automation: Use SQL Server Agent or your preferred CI/CD system (Jenkins, Azure DevOps, etc.) to schedule and monitor backups.
Test Restores: Periodically restore your backups in a dev environment to confirm they’re valid.
Keep Enough Retention: Balance retention needs (e.g., 2 weeks to months) with storage costs.
Document Everything: Store your scripts in version control (Git) or a wiki/Confluence page for easy updates and collaboration.

That’s It!

A robust backup and restore strategy can save your bacon when the unexpected happens. Try out the PoC script in a sandbox, tweak the scheduling to match your RPO/RTO needs, and enjoy a bit more peace of mind knowing your SQL databases are protected.

Questions? Feel free to drop a comment below or share your own backup/restore tips!

DEV Community