Invoke-DbaDbClone

Synopsis

Creates lightweight database clones containing schema and statistics but no table data

Description

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/

Syntax

Invoke-DbaDbClone
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [[-SqlCredential] <PSCredential>]
    [[-Database] <String[]>]
    [[-InputObject] <Database[]>]
    [[-CloneDatabase] <String[]>]
    [-ExcludeStatistics]
    [-ExcludeQueryStore]
    [-UpdateStatistics]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Invoke-DbaDbClone -SqlInstance sql2016 -Database mydb -CloneDatabase myclone

Clones mydb to myclone on sql2016

Example: 2

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

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

-InputObject

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.

-CloneDatabase

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.

-ExcludeStatistics

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

-ExcludeQueryStore

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.

-UpdateStatistics

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.

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

Outputs

Microsoft.SqlServer.Management.Smo.Database

Returns one Database object for each cloned database created. When cloning a source database to multiple clone names (via -CloneDatabase parameter with multiple values), one Database object is returned per clone created.

Default display properties (via Select-DefaultView from Get-DbaDatabase):

• ComputerName: The computer name of the SQL Server instance
• InstanceName: The SQL Server instance name
• SqlInstance: The full SQL Server instance name (computer\instance)
• Name: The name of the cloned database
• Status: Current database status (Normal, Offline, Recovering, etc.)
• IsAccessible: Boolean indicating if the database is currently accessible
• RecoveryModel: Database recovery model (Full, Simple, or BulkLogged)
• LogReuseWaitStatus: Status of transaction log reuse
• Size: Database size in megabytes
• Owner: Database owner login name
• Collation: Database collation setting
• Encrypted: Boolean indicating if Transparent Data Encryption (TDE) is enabled

*Additional properties available from the SMO Database object using Select-Object :

• CreateDate: DateTime when the database was created
• LastFullBackup: DateTime of the most recent full backup
• LastDiffBackup: DateTime of the most recent differential backup
• LastLogBackup: DateTime of the most recent transaction log backup
• DatabaseEngineEdition: Edition of SQL Server (Enterprise, Standard, Express, etc.)
• ID: Database ID assigned by SQL Server
• And many other SMO Database properties (see https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.database)