Setting Up a Standby Reporting Database in SQL Server Express

Objective and Setup

You want a read‑only reporting copy (test_reporting) of a primary database (test_prod) on the same SQL Server Express instance. You’ll periodically restore transaction log backups from test_prod to test_reporting so reporting users can query between restores. We’ll do this with RESTORE … WITH STANDBY.

---

Prerequisites and Notes

• SQL Server Express is fine; it lacks SQL Agent, so scheduling will be manual or via Windows Task Scheduler + sqlcmd.
• Ensure you have directories for data, log, backups, and the standby undo file:
  • Example paths:
    • D:\Data, E:\Logs
    • D:\Backups
    • D:\StandbyUndo
• Use your actual drive letters/paths based on your server.

---

Step 1: Create the test_prod Database

Run in SSMS (or sqlcmd):

-- Create test_prod with explicit file locations
CREATE DATABASE [test_prod]
ON PRIMARY (
    NAME = N'test_prod_data',
    FILENAME = N'D:\Data\test_prod.mdf',
    SIZE = 100MB, FILEGROWTH = 50MB
)
LOG ON (
    NAME = N'test_prod_log',
    FILENAME = N'E:\Logs\test_prod.ldf',
    SIZE = 64MB, FILEGROWTH = 32MB
);
GO

-- Set FULL recovery model to enable log backups
ALTER DATABASE [test_prod] SET RECOVERY FULL;
GO

---

Step 2: Seed Some Sample Data (Optional but Useful)

USE [test_prod];
GO

CREATE TABLE dbo.Sales (
    SalesId INT IDENTITY(1,1) PRIMARY KEY,
    OrderDate DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    Customer NVARCHAR(100) NOT NULL,
    Amount DECIMAL(18,2) NOT NULL
);
GO

INSERT INTO dbo.Sales (Customer, Amount)
VALUES (N'Acme Inc', 1250.00), (N'Globex', 980.75), (N'Initech', 212.00);
GO

---

Step 3: Take an Initial Full Backup of test_prod

BACKUP DATABASE [test_prod]
TO DISK = N'D:\Backups\test_prod_full_2025-11-25.bak'
WITH INIT, CHECKSUM, STATS = 5;
GO

---

Step 4: Create the Reporting Database via Restore (NORECOVERY)

We’ll restore the full backup to a new database name (test_reporting) and place files in separate locations.

-- Restore full backup, leave database non-recovered (ready for logs)
RESTORE DATABASE [test_reporting]
FROM DISK = N'D:\Backups\test_prod_full_2025-11-25.bak'
WITH MOVE N'test_prod_data' TO N'D:\Data\test_reporting.mdf',
     MOVE N'test_prod_log'  TO N'E:\Logs\test_reporting.ldf',
     NORECOVERY, REPLACE, STATS = 5;
GO

Note: If your logical file names differ, run this first to inspect and substitute names:

RESTORE FILELISTONLY
FROM DISK = N'D:\Backups\test_prod_full_2025-11-25.bak';
GO

---

Step 5: Take the First Transaction Log Backup from test_prod

-- Make some changes (optional)
INSERT INTO dbo.Sales (Customer, Amount) VALUES (N'Vector LLC', 333.33);
GO

-- Log backup
BACKUP LOG [test_prod]
TO DISK = N'D:\Backups\test_prod_log_2025-11-25_1200.trn'
WITH INIT, CHECKSUM, STATS = 5;
GO

---

Step 6: Apply the Log Backup to test_reporting WITH STANDBY

Create an undo file path (used by SQL Server to roll back uncommitted transactions and keep the DB queryable):

RESTORE LOG [test_reporting]
FROM DISK = N'D:\Backups\test_prod_log_2025-11-25_1200.trn'
WITH STANDBY = N'D:\StandbyUndo\test_reporting_undo.dat',
     STATS = 5;
GO

Result: test_reporting is now accessible read‑only for reporting.

---

Step 7: Periodically Update test_reporting with New Log Backups

Repeat this cycle whenever you want to refresh reporting:

-- On test_prod: make changes
INSERT INTO dbo.Sales (Customer, Amount) VALUES (N'Wayne Enterprises', 9876.54);
GO

-- On test_prod: take a log backup
BACKUP LOG [test_prod]
TO DISK = N'D:\Backups\test_prod_log_2025-11-25_1300.trn'
WITH CHECKSUM, STATS = 5;
GO

-- On test_reporting: apply the new log WITH STANDBY (use the SAME undo file)
RESTORE LOG [test_reporting]
FROM DISK = N'D:\Backups\test_prod_log_2025-11-25_1300.trn'
WITH STANDBY = N'D:\StandbyUndo\test_reporting_undo.dat',
     STATS = 5;
GO

• Keep using the same undo file path every time.
• Users can run read‑only queries between restores; connections are briefly interrupted while a restore runs.

---

Step 8: Optional Actions

Bring Reporting Fully Online (Ends the Chain)

RESTORE DATABASE [test_reporting] WITH RECOVERY;
GO

After this, you cannot apply further logs from the same chain.

Rerun the Setup from a New Full Backup

If you need to reinitialize, repeat Steps 3–6 starting with a fresh full backup.

---

Operational Guidance for SQL Server Express

• Use Windows Task Scheduler to run sqlcmd for periodic restores:
  • Program: sqlcmd
  • Arguments:

    -S .\SQLEXPRESS -E -Q "RESTORE LOG [test_reporting] 
    FROM DISK = 'D:\Backups\test_prod_log_latest.trn' 
    WITH STANDBY = 'D:\StandbyUndo\test_reporting_undo.dat';"
    

• Keep your backup files timestamped and apply them strictly in chronological order.
• Don’t move or delete the undo file; it’s required for subsequent STANDBY restores.
• Advise report users about short maintenance windows during restores.

---

Quick Verification Queries

-- On test_reporting: confirm read-only and recent data
SELECT TOP 10 * FROM dbo.Sales ORDER BY SalesId DESC;
GO

-- Check database state
SELECT name, state_desc, recovery_model_desc, is_read_only
FROM sys.databases
WHERE name IN ('test_prod', 'test_reporting');
GO

---

Common Pitfalls and Fixes

• Missing or out‑of‑order log backup: Ensure the log chain is continuous; reinitialize from a new full backup if broken.
• Wrong logical file names in RESTORE MOVE: Always confirm with RESTORE FILELISTONLY.
• Changing the undo file path or name: Keep it consistent; changing it breaks the chain.
• Long‑running queries during refresh: Schedule log restores at predictable low‑usage times.

---