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.

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

    1. Thanks Don. I am well. Hope you are too. Hope to see you this Thursday.

      I will follow up with the contribution to dbatools. Thanks for the link.

      1. I looked at that cmdlet first but it only copies data between preexisting tables. It seems they have the intention to create a copy-dbatableschema at some point which I thought your solution might be able to contribute to.

  1. What a great find! I’m so happy I found this because I was struggling to programmatically do this without using an intermediate step which bcp requires!

  2. Hello,
    Thanks for the tips !
    But in your example, instead of having a list in “hard” on the script, how could i implement a list of table that could be listed in a txt file ? ( sorry i’m a beginner in PS )

    1. Hi Max. In PowerShell, reading from a text file into an array variable is quite simple
      [string[]]$tables = Get-Content -Path ‘C:\USER\Documents\TableList.txt’

      Then, you just pass the above array to my function just as shown in the example in the section “Usage:”. In fact, all you need to do is replace the line in “Usage” that says “[string[]] $tables = @(‘dbo…” with this above line that reads from text file.

      1. Hello Sir
        Thank you so much for your answer
        it was really helpful
        But maybe i can ask you some help again.
        I’m working on a projet to export on sql file the table
        I could find this “https://gist.github.com/vincpa/1755925” which is working on the DBserver “locally” very well
        But if i’m using the script remotely on my laptop ( of course i have added the connexion to sql server on the script), the script is unable to write the datas. i have checked the variables and all seems ok.
        He is able to create the folders, not the files.
        Any ideas?

  3. Hi Jana,
    Really great function thank you. Saves so much time when compared to using SQL Import/Export wizard or writing T-SQL to do the same.
    I would second Donal’s comment that it will be a valued contribution to dbatools.io
    Cheers
    Kane

    1. Hi Kane,
      Thank you for the feedback and the kind words. I complete agree that I should make it part of dbatools but I have not had the time to make it happen as this has to evolve to be more generic with additional error handling to fit the dbatools standard. I am fully open to anyone taking this code and making it a part of dbatools.

      Thanks, Jana

  4. Thank you, great script – To add status you just need two lines in the bulkcopy definition (this using verbose, i.e. add -verbose to see):
    $bulkCopy.NotifyAfter=$NotifySize
    $bulkCopy.Add_SQlRowscopied( {Write-Verbose “$($args[1].RowsCopied) rows copied”} )

    Nice little addition; you can alternatively give a progress bar instead of Write-Verbose with Write-Progress but to be useful you need to count rows first.

    Thanks for the script and ideas,

    Steve

  5. Hi Jana,
    I find your query very helpful. However, I need to sync schema between tables. The number of tables may vary. How to modify your script to sync all tables in a database without having to manually adding table names to an array. I would like the array to have name from sys.tables.

    I will greatly appreciate your quick response

    Thanks.

    1. Hi Bhaktapur,

      If you are redoing all tables, you could just do a backup of the source database and restore the target database :-).

      Alternatively, if you have to get all the tables you could do something like this:

      [string[]] $tables = Invoke-Sqlcmd -ServerInstance “MySQLInstance” -Database “MyDB” -Query “select SCHEMA_NAME(schema_id)+’.name’ as SchemaDotTable from sys.tables” -OutputAs DataRow | select -ExpandProperty SchemaDotTable

      The rest of the code in the “Usage” could remain. The above code is just making an array of all the tables by querying the source instance. You would have to replace with appropriate values for instance and database names.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s