Author | Chrissy LeMaire (@cl), netnerds.net |
Availability | Windows, Linux, macOS |
Want to see the source code for this command? Check out Invoke-DbaDbClone on GitHub.
Want to see the Bill Of Health for this command? Check out Invoke-DbaDbClone.
Creates lightweight database clones containing schema and statistics but no table data
Creates schema-only database clones using SQL Server's DBCC CLONEDATABASE command. The cloned database contains all database objects (tables, indexes, views, procedures) and statistics, but no actual table data.
This is particularly valuable for performance troubleshooting scenarios where you need to analyze query execution plans and optimizer behavior without the storage overhead of copying entire tables. DBAs commonly use this for reproducing performance issues in test environments or sharing database structures with vendors for support cases.
Read more:
- https://sqlperformance.com/2016/08/sql-statistics/expanding-dbcc-clonedatabase
- https://support.microsoft.com/en-us/help/3177838/how-to-use-dbcc-clonedatabase-to-generate-a-schema-and-statistics-only
Thanks to Microsoft Tiger Team for the code and idea https://github.com/Microsoft/tigertoolbox/
Invoke-DbaDbClone
[[-SqlInstance] <DbaInstanceParameter[]>]
[[-SqlCredential] <PSCredential>]
[[-Database] <String[]>]
[[-InputObject] <Database[]>]
[[-CloneDatabase] <String[]>]
[-ExcludeStatistics]
[-ExcludeQueryStore]
[-UpdateStatistics]
[-EnableException]
[-WhatIf]
[-Confirm]
[<CommonParameters>]
PS C:\> Invoke-DbaDbClone -SqlInstance sql2016 -Database mydb -CloneDatabase myclone
Clones mydb to myclone on sql2016
PS C:\> Get-DbaDatabase -SqlInstance sql2016 -Database mydb | Invoke-DbaDbClone -CloneDatabase myclone, myclone2 -UpdateStatistics
Updates the statistics of mydb then clones to myclone and myclone2
The target SQL Server instance or instances.
Alias | |
Required | False |
Pipeline | false |
Default Value |
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.
Alias | |
Required | False |
Pipeline | false |
Default Value |
Specifies the source database(s) to clone from the SQL Server instance. Accepts multiple database names for batch operations.
Use this when connecting directly to an instance rather than piping database objects from Get-DbaDatabase.
Alias | |
Required | False |
Pipeline | false |
Default Value |
Accepts database objects from Get-DbaDatabase through the pipeline, allowing for filtered operations.
This method provides more flexibility than the Database parameter for complex database selection scenarios.
Alias | |
Required | False |
Pipeline | true (ByValue) |
Default Value |
Specifies the name(s) for the new cloned database(s). If not provided, defaults to adding '_clone' suffix to the source database name.
Each clone must have a unique name on the target instance and cannot already exist.
Alias | |
Required | False |
Pipeline | false |
Default Value |
Excludes table and index statistics from the cloned database, creating only the schema structure without statistical metadata.
Use this when you only need the database structure for schema comparison or when statistics would interfere with your testing scenario. Requires SQL Server 2014 SP2 CU3+ or SQL Server 2016 SP1+.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
Excludes Query Store data from the cloned database, preventing historical query execution data from being copied.
Use this when you want a clean slate for query performance analysis or when Query Store data is not relevant to your testing scenario. Requires SQL Server 2016 SP1 or higher.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
Updates column store index statistics in the source database before cloning using Microsoft Tiger Team methodology.
Use this when working with column store indexes to ensure the clone contains current statistical information for accurate query plan generation.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
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.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
Shows what would happen if the command were to run. No actions are actually performed.
Alias | wi |
Required | False |
Pipeline | false |
Default Value |
Prompts you for confirmation before executing any changing operations within the command.
Alias | cf |
Required | False |
Pipeline | false |
Default Value |