New-DbaDbDataGeneratorConfig

Want to see the source code for this command? Check out New-DbaDbDataGeneratorConfig on GitHub.
Want to see the Bill Of Health for this command? Check out New-DbaDbDataGeneratorConfig.

Synopsis

Creates JSON configuration files for generating realistic test data in SQL Server database tables

Description

Analyzes database table structures and generates JSON configuration files that define how to populate each column with realistic fake data. The function examines column names, data types, constraints, and relationships to intelligently map appropriate data generation rules using the Bogus library. Column names matching common patterns (like "Address", "Email", "Phone") automatically get contextually appropriate fake data types, while other columns get sensible defaults based on their SQL data types.

These configuration files serve as the blueprint for Invoke-DbaDbDataGenerator, allowing DBAs to create development databases with realistic test data instead of using production data. Perfect for building demo environments, testing applications with meaningful datasets, or creating training databases that mirror production schemas but contain no sensitive information.

The function handles identity columns, foreign key relationships, unique indexes, and nullable constraints while skipping unsupported column types like computed columns, spatial data types, and XML. Configuration files are saved with the naming convention "servername.databasename.DataGeneratorConfig.json" for easy identification and reuse.

Read more here:
https://sachabarbs.wordpress.com/2018/06/11/bogus-simple-fake-data-tool/
https://github.com/bchavez/Bogus

Syntax

New-DbaDbDataGeneratorConfig
    [-SqlInstance] <DbaInstanceParameter[]>
    [[-SqlCredential] <PSCredential>]
    [[-Database] <String[]>]
    [[-Table] <String[]>]
    [-ResetIdentity]
    [-TruncateTable]
    [[-Rows] <Int32>]
    [-Path] <String>
    [-Force]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> New-DbaDbDataGeneratorConfig -SqlInstance SQLDB1 -Database DB1 -Path C:\Temp\clone

Process all tables and columns for database DB1 on instance SQLDB1

Example: 2

PS C:\> New-DbaDbDataGeneratorConfig -SqlInstance SQLDB1 -Database DB1 -Table Customer -Path C:\Temp\clone

Process only table Customer with all the columns

Required Parameters

-SqlInstance

The target SQL Server instance or instances.

-Path

Specifies the directory where JSON configuration files will be saved. Files are named using the pattern "servername.databasename.DataGeneratorConfig.json".
Choose a location accessible to your development team since these config files will be used by Invoke-DbaDbDataGenerator to create the actual test data.

Optional Parameters

-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 analyze for data generation configuration creation. Accepts multiple database names.
Use this when you need to create test data configs for specific databases instead of all databases on the instance.

-Table

Specifies which tables to include in the data generation configuration. Accepts multiple table names and supports wildcards.
Use this when you only need test data for specific tables rather than analyzing the entire database schema.

-ResetIdentity

Controls whether identity columns should reset to their seed values when generating test data. When enabled, identity values start from the original seed.
Use this when you need predictable, consistent identity values across test data generation runs instead of continuing from existing maximum values.

-TruncateTable

Enables table truncation before inserting generated test data. When specified, existing data is removed before populating with fake data.
Use this when you need clean test environments or want to replace all existing data rather than appending to current table contents.

-Rows

Sets the number of test data rows to generate for each table in the configuration. Defaults to 1000 rows per table.
Adjust this based on your testing needs - use smaller values for development environments or larger values for performance testing scenarios.

-Force

Allows the function to create the specified Path directory if it doesn't exist. Without this switch, the function will fail if the target directory is missing.
Use this when setting up new test data workflows where the output directory structure hasn't been established yet.

-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

Shows what would happen if the command were to run. No actions are actually performed.

-Confirm

Prompts you for confirmation before executing any changing operations within the command.