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

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

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

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

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;

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

/****************************************************************************
 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

USE PoCDatabase;
GO

/**************************************************
 STEP 2: Create Table & Insert Initial Data
***************************************************/
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. */

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');

/* 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';  

/* 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). */
DECLARE @StopTime DATETIME;
SELECT TOP 1 @StopTime = StopTime 
FROM ##StopTimeStore;

PRINT '--- Restoring TLog #2 with STOPAT (RECOVERY) ---';
RESTORE LOG PoCDatabase
    FROM DISK = N'/var/opt/mssql/backups/PoCDatabase_Log2.trn'
    WITH STOPAT = @StopTime, 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