Invoke-DbaDbDataGenerator

Synopsis

Generates realistic test data for SQL Server database tables using configuration-driven rules

Description

Populates database tables with randomly generated but realistic test data based on JSON configuration files. Uses the Bogus library to create fake but believable data like names, addresses, phone numbers, and dates that respect column constraints and data types. Perfect for creating development environments, testing scenarios, or demo databases without using production data. Handles identity columns, unique indexes, nullable fields, and foreign key relationships while maintaining data integrity.

Syntax

Invoke-DbaDbDataGenerator
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [[-SqlCredential] <PSCredential>]
    [[-Database] <String[]>]
    [-FilePath] <Object>
    [[-Locale] <String>]
    [[-CharacterString] <String>]
    [[-Table] <String[]>]
    [[-Column] <String[]>]
    [[-ExcludeTable] <String[]>]
    [[-ExcludeColumn] <String[]>]
    [[-MaxValue] <Int32>]
    [-ExactLength]
    [[-ModulusFactor] <Int32>]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Invoke-DbaDbDataGenerator -SqlInstance sqldb2 -Database DB1 -FilePath C:\temp\sqldb1.db1.tables.json

Apply the data generation configuration from the file “sqldb1.db1.tables.json” to the db1 database on sqldb2. Prompt for confirmation for each table.

Required Parameters

-FilePath

Path to the JSON configuration file that defines data generation rules for tables and columns. Accepts local file paths or HTTP URLs.
This file specifies which tables to populate, how many rows to generate, and the data generation rules for each column.

Optional Parameters

-SqlInstance

The target SQL Server instance or instances.

-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.

-Database

Specifies which databases to generate data for. If not provided, uses database names from the configuration file.
Use this to limit data generation to specific databases when your config file covers multiple databases.

-Locale

Sets the locale for generating culture-specific fake data like names, addresses, and phone numbers. Defaults to ’en’ for English.
Change this when you need realistic data for specific regions, such as ‘de’ for German or ‘fr’ for French test data.

-CharacterString

Defines the character set used for generating random string values. Defaults to alphanumeric characters.
Customize this when you need specific character patterns for testing, such as restricting to only uppercase letters or including special characters.

-Table

Limits data generation to specific tables only, overriding the full table list in the configuration file.
Useful when you need to populate just certain tables for testing or during incremental development work.

-Column

Restricts data generation to specific columns within the processed tables.
Use this to generate data for only certain columns during testing or when troubleshooting specific column configurations.

-ExcludeTable

Skips specific tables even if they’re included in the configuration file.
Use this to temporarily exclude problematic tables during testing or when you want to process most tables but skip a few.

-ExcludeColumn

Skips specific columns even if they’re included in the configuration file.
Helpful when troubleshooting column-specific issues or when you want to exclude sensitive columns temporarily.

-MaxValue

Overrides the maximum length for string columns, ignoring the data type’s natural limits. Lower data type limits still take precedence.
Useful for testing with shorter strings or when you need consistent string lengths across different environments.

-ExactLength

Forces generated strings to match the exact length of existing data in the column.
Use this when you need to preserve string length patterns for testing applications that expect specific data formats.

-ModulusFactor

Controls how frequently nullable columns receive NULL values by using modulus calculation. Default is every 10th row gets NULL.
Adjust this to increase or decrease NULL frequency in your test data to match realistic data distribution patterns.

-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with “sea of red” exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this “nice by default” feature off and enables you to catch exceptions with your own try/catch.

-WhatIf

If this switch is enabled, no actions are performed but informational messages will be displayed that explain what would happen if the command were to run.

-Confirm

If this switch is enabled, you will be prompted for confirmation before executing any operations that change state.