Remove-DbaDbData

Synopsis

Truncates all user tables in specified databases to remove all data while preserving table structure.

Description

Removes all data from user tables by truncating each table in the specified databases. When foreign keys or views exist that would prevent truncation, the function automatically scripts them out, drops them temporarily, performs the truncation, then recreates the objects with their original definitions and permissions. This provides a fast way to clear databases for testing or development environments without having to rebuild schemas. The function excludes system databases and only processes user databases to prevent accidental damage to SQL Server internals.

Syntax

Remove-DbaDbData
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [[-SqlCredential] <PSCredential>]
    [[-Database] <String[]>]
    [[-ExcludeDatabase] <String[]>]
    [[-InputObject] <Object[]>]
    [[-Path] <String>]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Remove-DbaDbData -SqlInstance localhost -Database dbname

Removes all data from the dbname database on the local default SQL Server instance

Example: 2

PS C:\> Remove-DbaDbData -SqlInstance mssql1 -ExcludeDatabase DBA -Confirm:$False

Removes all data from all databases on mssql1 except the DBA Database. Doesn’t prompt for confirmation.

Example: 3

PS C:\> $svr = Connect-DbaInstance -SqlInstance mssql1
PS C:\> $svr | Remove-DbaDbData -Database AdventureWorks2017

Removes all data from AdventureWorks2017 on the mssql1 SQL Server Instance, uses piped input from Connect-DbaInstance.

Example: 4

PS C:\> Get-DbaDatabase -SqlInstance mssql1 -Database AdventureWorks2017 | Remove-DbaDbData

Removes all data from AdventureWorks2017 on the mssql1 SQL Server Instance, uses piped input from Get-DbaDatabase.

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 clear of data. Accepts wildcards for pattern matching.
When omitted, the function processes all user databases on the instance. System databases are always excluded for safety.

-ExcludeDatabase

Excludes specific databases from data removal operations. Use this to protect important databases when clearing multiple databases.
Commonly used with production or reference databases that should remain untouched during development environment resets.

-InputObject

Accepts piped database objects from Get-DbaDatabase or server connections from Connect-DbaInstance.
Use this for pipeline operations when you need to filter databases with complex criteria before clearing data.

-Path

Sets the temporary directory for storing drop and create scripts during the data removal process.
The function creates temporary SQL scripts for foreign keys and views, then automatically removes them when complete. Defaults to the configured dbatools export path.

-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.