Robot测试前如何用SQL Server脚本基于模板重建数据库？

DevOps 小满

2026-5-14

Great question! SQL Server doesn’t have a direct equivalent to PostgreSQL’s CREATE DATABASE ... TEMPLATE syntax, but you can absolutely achieve the same workflow—dumping the current test database, then rebuilding it from a template—with a combination of T-SQL commands and automation. Here’s how to set it up for your nightly Robot testing pipeline:

Step 1: Prep Your Template Database

First, create and configure a template database with all the schema, seed data, and settings you need for your tests. Keep this database in a stable state (don’t run tests against it directly!)—it’ll be your baseline for every nightly rebuild.

Step 2: Nightly Rebuild Script (T-SQL)

This script handles the full workflow: backing up the existing test database (for audit/rollback), cleaning up the old test DB, and creating a fresh copy from your template.

-- 1. Backup the current test database (optional but recommended for debugging)
BACKUP DATABASE [YourTestDB] 
TO DISK = N'C:\SQLBackups\YourTestDB_PreRebuild_$(DATE).bak' 
WITH NOFORMAT, NOINIT, NAME = N'YourTestDB-Pre Rebuild Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO

-- 2. Kill all active connections to the test database (required to drop it)
USE master;
GO
DECLARE @killCmd VARCHAR(8000) = '';
SELECT @killCmd = @killCmd + 'KILL ' + CONVERT(VARCHAR(5), session_id) + ';'
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID('YourTestDB');

EXEC(@killCmd);
GO

-- 3. Drop the existing test database
DROP DATABASE IF EXISTS [YourTestDB];
GO

-- 4. Create fresh test DB from the template
-- Option A: Backup template + restore as test DB (most reliable for automation)
-- First, take a fresh backup of the template (skip this if your template rarely changes)
BACKUP DATABASE [YourTemplateDB] 
TO DISK = N'C:\SQLBackups\YourTemplateDB_Latest.bak' 
WITH NOFORMAT, INIT, NAME = N'YourTemplateDB-Full Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO

-- Restore the template backup as the new test database
RESTORE DATABASE [YourTestDB]
FROM DISK = N'C:\SQLBackups\YourTemplateDB_Latest.bak'
WITH REPLACE,
-- Map template data/log files to new paths for the test DB
MOVE N'YourTemplateDB' TO N'C:\SQLData\YourTestDB.mdf',
MOVE N'YourTemplateDB_log' TO N'C:\SQLLogs\YourTestDB_log.ldf',
STATS = 10;
GO

-- Option B: Attach copied template files (faster for small DBs, needs OS-level file copy first)
-- Uncomment this if you prefer this method, but run a file copy command first (e.g., xcopy in Windows)
/*
CREATE DATABASE [YourTestDB]
ON (FILENAME = N'C:\SQLData\YourTestDB_Copy.mdf'),
(FILENAME = N'C:\SQLLogs\YourTestDB_Copy_log.ldf')
FOR ATTACH_REBUILD_LOG;
GO
*/

Step 3: Automate with Robot Framework

To run this script as part of your nightly test setup, you can call it directly from Robot using the Execute Command keyword (using sqlcmd for T-SQL execution):

*** Test Cases ***
Prep Test Database for Nightly Runs
    # Execute the SQL script with sqlcmd (adjust credentials/server as needed)
    ${script_output}=    Execute Command    sqlcmd -S YourSQLServerInstance -E -i "C:\Path\To\Your\RebuildTestDB.sql"
    Log    Database rebuild completed. Output: ${script_output}
    # Verify the new database exists (optional)
    ${db_exists}=    Run SQL Query    SELECT COUNT(*) FROM sys.databases WHERE name = 'YourTestDB'
    Should Be Equal    ${db_exists[0][0]}    1    Test database was not created successfully

Key Notes:

Permissions: Make sure the SQL Server service account has read/write access to your backup, data, and log directories.
Date Variables: For unique backup filenames, use system date variables. On Windows, replace $(DATE) with %DATE:~10,4%%DATE:~4,2%%DATE:~7,2% to get a clean YYYYMMDD format.
Template Stability: If your template database doesn’t change often, skip the nightly template backup and use a pre-made static backup to speed up the process.

内容的提问来源于stack exchange，提问作者JuniorNinjaTurte

Robot测试前如何用SQL Server脚本基于模板重建数据库？

Key Notes: #

Key Notes: