PowerShell – Copy SQL Server Tables – Structure, Data & Indexes To Another Database/Instance

Requirement:

Recently I got a request from a user that he wanted to copy a specific set of tables and their indexes into a new database to ship to the vendor for analysis. The problem was that the DB had thousands of tables (8,748 to be precise). Hunting and pecking for specific tables from that is possible but tedious. Even if I managed to do that, I still have to manually script out the indexes and run them in target as the native “Import/Export Wizard” does not do indexes. It only copies the table structure and data! I am not a big fan of point and click anyway.

Solution: Copy-SQLTable

PowerShell is my tool of choice and it took me a couple of hours to code/refine/test and blog. So, here is what I did to script the tables and their indexes and then copy the data over. It is pretty self-explanatory. I am sure a lot of you will find this very useful. I know that I will use it a lot.

The function comments:

PowerShell code gets manged by wordpress when it is in between block comments! I have separated out the code comments here so that the code block which is below this does not get messed up.

#
#######################
#
.SYNOPSIS
    Copies tables from source to target SQL instance. Optionally creates indexes

.DESCRIPTION
    It is usually a pain to copy with the UI wizard to copy data for specific tables especially
        if it included copying the indexes too! This function eases the pain!

.INPUTS
    Source and target information and table list to copy

.OUTPUTS
    None

.EXAMPLE 

        [string] $sourceInstance = 'MySourceServer\SourceInst'
        [string] $sourceDB = 'MySourceDB'
        [string] $destInstance = $sourceInstance
        [string] $destDB = 'MyTargetDB_ForVendor'
        [bool] $dropTargetTableIfExists = $true
        [bool] $copyIndexes = $true
        [bool] $copyData = $true
        [string[]] $tables = @('dbo.T_STA_DWH_SEC_INDUSTRY_RPT_DQM',
                    'dbo.T_STA_MAS_SEC_INDUSTRY',
                    'dbo.T_STA_DWH_SEC_CALC_RPT_DQM',
                    'dbo.T_STA_MAS_SEC_CALC',
                    'dbo.T_REF_MAS_CLASS_DESC',
                    'dbo.T_REF_MAS_CLASS_MAP',
                    'dbo.T_REF_MAS_CLASSIFICATION_CATEGORY',
                    'dbo.T_DYN_MAS_SAP_ACC_ENTRY',
                    'dbo.T_STA_DWH_SECURITIES_RPT_DQM',
                    'dbo.T_STA_MAS_SEC_CLASS',
                    'dbo.T_STA_MAS_SECURITIES',
                    'dbo.T_REF_MAS_COUNTRY',
                    'dbo.T_REF_MAS_CURRENCY',
                    'dbo.T_STA_BPS_SEC',
                    'dbo.T_STA_BPS_ULT',
                    'dbo.T_STA_DQM_SEC_CALC_CONTROL_RPT',
                    'dbo.T_STA_DQM_SEC_INDUSTRY_CONTROL_RPT',
                    'dbo.T_STA_DQM_SECURITY_CONTROL_RPT',
                    'dbo.T_STA_MAS_ISSUER')

        Copy-SQLTable `
            -SourceInstance $sourceInstance `
            -SourceDB $sourceDB `
            -DestInstance $destInstance `
            -DestDB $destDB `
            -DropTargetTableIfExists: $dropTargetTableIfExists `
            -CopyIndexes: $copyIndexes `
            -CopyData: $copyData `
            -Tables $tables `
            -Verbose
.NOTES 

Version History
    v1.0  - Jun 12, 2017. Jana Sattainathan [Twitter: @SQLJana] [Blog: sqljana.wordpress.com]

.LINK
    sqljana.wordpress.com
#
#

The function code:




function Copy-SQLTable
{
    [CmdletBinding()]
    param( 
 
        [Parameter(Mandatory=$true)]
        [string] $SourceInstance,

        [Parameter(Mandatory=$true)]
        [string] $SourceDB,        
        
        [Parameter(Mandatory=$true)]
        [string] $DestInstance,
        
        [Parameter(Mandatory=$true)]
        [string] $DestDB,
        
        [Parameter(Mandatory=$false)]
        [switch] $DropTargetTableIfExists = $false,

        [Parameter(Mandatory=$false)]
        [switch] $CopyIndexes = $true,
        
        [Parameter(Mandatory=$false)]
        [switch] $CopyData = $true,

        [Parameter(Mandatory=$true)]
        [string[]] $Tables,

        [Parameter(Mandatory=$false)]
        [int] $BulkCopyBatchSize = 10000,

        [Parameter(Mandatory=$false)]
        [int] $BulkCopyTimeout = 600   #10 minutes
 
    )
 
    [string] $fn = $MyInvocation.MyCommand
    [string] $stepName = "Begin [$fn]"   
 
    [string] $sourceConnString = "Data Source=$SourceInstance;Initial Catalog=$SourceDB;Integrated Security=True;"
    [string] $destConnString = "Data Source=$DestInstance;Initial Catalog=$DestDB;Integrated Security=True;"
    [int] $counter = 0
 
    try
    {    

        $stepName = "[$fn]: Import SQLPS module and initialize source connection"
        #---------------------------------------------------------------
        Write-Verbose $stepName 
        
        Import-Module 'SQLPS'
        $sourceServer = New-Object Microsoft.SqlServer.Management.Smo.Server $SourceInstance
        $sourceDatabase = $sourceServer.Databases[$SourceDB]
        $sourceConn  = New-Object System.Data.SqlClient.SQLConnection($sourceConnString)
        $sourceConn.Open()
    

        foreach($table in $sourceDatabase.Tables)
        {
            $tableName = $table.Name
            $schemaName = $table.Schema
            $tableAndSchema = "$schemaName.$tableName"

            if ($Tables.Contains($tableAndSchema))
            {        
                $counter = $counter + 1
                Write-Progress -Activity "Copy progress:" `
                            -PercentComplete ([int](100 * $counter / $Tables.Count)) `
                            -CurrentOperation ("Completed {0}% of the tables" -f ([int](100 * $counter / $Tables.Count))) `
                            -Status ("Working on table: [{0}]" -f $tableAndSchema) `
                            -Id 1

                Write-Verbose "[$fn]: ---------------------------------------------------------------"
                $stepName = "[$fn]: About to copy table [$tableAndSchema]"            
                Write-Verbose $stepName 
                Write-Verbose "[$fn]: ---------------------------------------------------------------"


                $stepName = "[$fn]: Create schema [$schemaName] in target if it does not exist"
                #---------------------------------------------------------------
                Write-Verbose $stepName 

                $schemaScript = "IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = '$schemaName')
                                    BEGIN
                                        EXEC('CREATE SCHEMA $schemaName')
                                    END"

                Invoke-Sqlcmd `
                            -ServerInstance $DestInstance `
                            -Database $DestDB `
                            -Query $schemaScript

                if ($DropTargetTableIfExists -eq $true)
                {
                    Write-Verbose "[$fn]: Drop table [$tableName] in target if it exists"
                    #---------------------------------------------------------------
                    Write-Verbose $stepName 

                    $schemaScript = "IF EXISTS (SELECT 1 WHERE OBJECT_ID('$tableAndSchema') IS NOT NULL)
                                        BEGIN
                                            EXEC('DROP TABLE $tableAndSchema')
                                        END"

                    Invoke-Sqlcmd `
                                -ServerInstance $DestInstance `
                                -Database $DestDB `
                                -Query $schemaScript
                }


                $stepName = "[$fn]: Get the source table script for [$tableName] and create in target"
                #---------------------------------------------------------------
                Write-Verbose $stepName 

                $Tablescript = ($table.Script() | Out-String)

                Invoke-Sqlcmd `
                            -ServerInstance $DestInstance `
                            -Database $DestDB `
                            -Query $Tablescript
            


                #Only copy if needed. There may be a need to just copy table structures!
                if ($CopyData -eq $true)
                {
                    $stepName = "[$fn]: Get data reader for source table"
                    #---------------------------------------------------------------
                    Write-Verbose $stepName 

                    $sql = "SELECT * FROM $tableAndSchema"
                    $sqlCommand = New-Object system.Data.SqlClient.SqlCommand($sql, $sourceConn) 
                    [System.Data.SqlClient.SqlDataReader] $sqlReader = $sqlCommand.ExecuteReader()

                    $stepName = "[$fn]: Copy data from source to destination for table"
                    #---------------------------------------------------------------
                    Write-Verbose $stepName 

                    $bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($destConnString, [System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity)
                    $bulkCopy.DestinationTableName = $tableAndSchema
                    $bulkCopy.BulkCopyTimeOut = $BulkCopyTimeout
                    $bulkCopy.BatchSize = $BulkCopyBatchSize
                    $bulkCopy.WriteToServer($sqlReader)
                    $sqlReader.Close()
                    $bulkCopy.Close()
                }


                #Do the index creations after the data load! That is the smarter thing to do.
                if ($CopyIndexes -eq $true)
                {
                    $stepName = "[$fn]: Create indexes for [$tableName] in target"
                    #---------------------------------------------------------------
                    Write-Verbose $stepName 

                    foreach($index in $table.Indexes )
                    {
                        Write-Verbose "Creating index [$($index.Name)] for [$tableName]"

                        $indexScript = ($index.script() | Out-String)

                        Invoke-Sqlcmd `
                            -ServerInstance $DestInstance `
                            -Database $DestDB `
                            -Query $indexScript
                    }
                }


            }
        }


        Write-Verbose 'Cleanup'
        #---------------------------------------------------------------

        $sourceConn.Close()


    }
    catch
    {
        [Exception]$ex = $_.Exception
        Throw "Unable to copy table(s). Error in step: `"{0}]`" `n{1}" -f `
                        $stepName, $ex.Message
    }
    finally
    {
        #Return value if any
    }
}        


Usage:

Below is a sample usage example where I try to copy a few tables from a source instance database to another database on the same instance (used as target).

        [string] $sourceInstance = 'MySourceServer\SourceInst'
        [string] $sourceDB = 'MySourceDB'
        [string] $destInstance = $sourceInstance
        [string] $destDB = 'MyTargetDB_ForVendor'
        [bool] $dropTargetTableIfExists = $true
        [bool] $copyIndexes = $true
        [bool] $copyData = $true
        [string[]] $tables = @('dbo.T_STA_DWH_SEC_INDUSTRY_RPT_DQM',
                    'dbo.T_STA_MAS_SEC_INDUSTRY',
                    'dbo.T_STA_DWH_SEC_CALC_RPT_DQM',
                    'dbo.T_STA_MAS_SEC_CALC',
                    'dbo.T_REF_MAS_CLASS_DESC',
                    'dbo.T_REF_MAS_CLASS_MAP',
                    'dbo.T_REF_MAS_CLASSIFICATION_CATEGORY',
                    'dbo.T_DYN_MAS_SAP_ACC_ENTRY',
                    'dbo.T_STA_DWH_SECURITIES_RPT_DQM',
                    'dbo.T_STA_MAS_SEC_CLASS',
                    'dbo.T_STA_MAS_SECURITIES',
                    'dbo.T_REF_MAS_COUNTRY',
                    'dbo.T_REF_MAS_CURRENCY',
                    'dbo.T_STA_BPS_SEC',
                    'dbo.T_STA_BPS_ULT',
                    'dbo.T_STA_DQM_SEC_CALC_CONTROL_RPT',
                    'dbo.T_STA_DQM_SEC_INDUSTRY_CONTROL_RPT',
                    'dbo.T_STA_DQM_SECURITY_CONTROL_RPT',
                    'dbo.T_STA_MAS_ISSUER')

        Copy-SQLTable `
            -SourceInstance $sourceInstance `
            -SourceDB $sourceDB `
            -DestInstance $destInstance `
            -DestDB $destDB `
            -DropTargetTableIfExists: $dropTargetTableIfExists `
            -CopyIndexes: $copyIndexes `
            -CopyData: $copyData `
            -Tables $tables `
            -Verbose

Below, you will see a screenshot of the function in action:
Copy-SQLTable

Features:

* Modular and generic to copy 1 or more tables
* Copy just table structures
* Create schemas in target based on the source table list
* Copy indexes (optionally)
* Copy data (optionally)
* Drop and recreate tables

Limitations

There are some limitations that I can think of but it should still be very usable for copying small/medium sized tables.

* No support for SQL username/password (only integrated security)
* No parallelism (serial copy)
* No progress information at the table row level for copy
* Table names have to be in the form schema.table without square brackets!

Code is not as good as I would like it to be but it works and in my tests, I was able to copy over tables with a few million rows without issues. Also, I am sure that a lot of bells and whistles can be added to this one.

If you have comments or suggestions, please comment below.

Advertisements

One thought on “PowerShell – Copy SQL Server Tables – Structure, Data & Indexes To Another Database/Instance

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s