DBA Scripts: Generate Test Databases

When you’re validating migrations, failover, or automation at scale, you need realistic volume: hundreds or thousands of databases to stress-test backups, restores, log shipping, and copy operations.

This helper automates that setup, it creates batches of small, randomized databases with configurable sizes and naming, so you can reproduce large-scale scenarios quickly and repeatably.

/*
Script Name : New-TestDatabases
Category    : dba-lab
Purpose     : Create multiple test databases with randomised names for lab and migration scenarios.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-generate-test-databases/)
Requires    : sysadmin or dbcreator
Notes       : Edit the DECLARE parameter block before running in SSMS.
              For PowerShell-driven creation use powershell\lab\New-MultipleDatabases.ps1.
              For parameterised execution from a script use powershell\lab\Run-CreateTestDatabases.ps1.
*/
-- WARNING: Creates databases — review @Count and @Prefix before running
-- SAFE:CreatesObjects
-- IMPACT:High
SET NOCOUNT ON;

-- Parameters (edit these values before running)
DECLARE @Count      INT     = 10;
DECLARE @Prefix     SYSNAME = N'migdb';
DECLARE @StartIndex INT     = 1;
DECLARE @SuffixLen  INT     = 8;
DECLARE @IndexWidth INT     = 3;
DECLARE @DataSizeMB INT     = 25;
DECLARE @LogSizeMB  INT     = 10;

DECLARE @i   INT = @StartIndex;
DECLARE @End INT = @i + @Count - 1;
DECLARE @Name SYSNAME;
DECLARE @SQL  NVARCHAR(MAX);

WHILE @i <= @End
BEGIN
    SET @Name = @Prefix + '_'
        + RIGHT(REPLICATE('0', @IndexWidth) + CAST(@i AS VARCHAR(20)), @IndexWidth)
        + '_' + LEFT(REPLACE(CONVERT(VARCHAR(36), NEWID()), '-', ''), @SuffixLen);

    PRINT 'Creating: ' + @Name;

    IF DB_ID(@Name) IS NULL
    BEGIN
        BEGIN TRY
            SET @SQL = N'CREATE DATABASE [' + @Name + N'];';
            EXEC sp_executesql @SQL;

            DECLARE @DataLogical SYSNAME = NULL;
            DECLARE @LogLogical  SYSNAME = NULL;

            SELECT @DataLogical = mf.name FROM sys.master_files mf
            WHERE mf.database_id = DB_ID(@Name) AND mf.type_desc = 'ROWS';

            SELECT @LogLogical = mf.name FROM sys.master_files mf
            WHERE mf.database_id = DB_ID(@Name) AND mf.type_desc = 'LOG';

            IF @DataLogical IS NOT NULL
            BEGIN
                SET @SQL = N'ALTER DATABASE [' + @Name + N'] MODIFY FILE (NAME = N''' + @DataLogical
                    + ''', SIZE = ' + CAST(@DataSizeMB AS NVARCHAR(10)) + N'MB);';
                EXEC sp_executesql @SQL;
            END

            IF @LogLogical IS NOT NULL
            BEGIN
                SET @SQL = N'ALTER DATABASE [' + @Name + N'] MODIFY FILE (NAME = N''' + @LogLogical
                    + ''', SIZE = ' + CAST(@LogSizeMB AS NVARCHAR(10)) + N'MB);';
                EXEC sp_executesql @SQL;
            END
        END TRY
        BEGIN CATCH
            PRINT 'Failed creating ' + @Name + ': ' + ERROR_MESSAGE();
            THROW;
        END CATCH
    END

    SET @i += 1;
END

PRINT 'Done.';

SELECT
    name        AS database_name,
    create_date AS created_at,
    state_desc
FROM sys.databases
WHERE name LIKE @Prefix + N'_%'
ORDER BY create_date DESC;

Understanding the Parameters

The DECLARE block at the top is the entire interface. Read it before you run anything, because the defaults decide how many databases appear and what they are called.

Frequently Asked Questions