PowerShell : Copy a table from one server to another


In general, a DBA like us gets requests to copy a table data from one SQL Server instance to another. In that case, a DBA first sees the amount of data present in the table and then he decides a method to transfer. Normally, a DBA selects Import Export method or SSIS and of course that is a suggested way to transfer data between two SQL Server instances.

Downside of using SSIS is that the same SSIS package can not be reused to copy a different table which has different table structure. You will have to create a new package for the new table or modify the column mappings of an existing package in BIDS. Here, PowerShell seems to be a very powerful scripting tool built on the .NET Framework. Now, I am showing as how SqlBulkCopy method is used to transfer table data between two instances.

First you need to change the execution policy. Either it has to set as Bypass or Unrestricted, since it has to communicate over the network. By default it is set to “Restricted”

Start → “Windows PowerShell” → Right Click “Run as administrator” → “Set-ExecutionPolicy Unrestricted”

Now, change the below code as per your need. Remember, you may not need SA user credential all the time.

 $SrcServer = "Machine1" #Source Server Name
 $SrcDatabase = "AdventureWorks2012" #Source Database Name
 $SrcUser = "sa" #Source Login : User Name
 $SrcPwd = "sa123" #Source Login : Password
 $DestServer = "Machine2" #Destination Server Name
 $DestDatabase = "dbTester" #Destination Database Name
 $DestUser = "sa" #Destination Login : User Name
 $DestPwd = "sa123" #Destination Login : Password
 $SrcTable = "[Person].[Person]" #Source Table Name
 $DestTable = "Person" #Destination Table Name
 $BatchSize = 10 #Batch Size
 $TimeOut = 180 #Timeout period
 
 Function ConnectionStringS ([string] $ServerName, [string] $DbName, [string] $User, [string] $Pwd)
 {
 "Server=$ServerName;uid=$User; pwd=$Pwd;Database=$DbName;Integrated Security=False;"
 }

########## Main body ############
 If ($DestDatabase.Length –eq 0) {
 $DestDatabase = $SrcDatabase
 }
 If ($DestTable.Length –eq 0) {
 $DestTable = $SrcTable
 }
 #If ($Truncate) {
 #$TruncateSql = "TRUNCATE TABLE " + $DestTable
 # Sqlcmd -S $DestServer -d $DestDatabase -Q $TruncateSql
 #}
 #$SrcConnStr = New-Object System.Data.SqlClient.SqlConnection
 #$SrcConn.ConnectionString  = "Server=$SrcServer;Database=$SrcDatabase; User Id=$SrcUser; Password=$SrcPwd;"
 $SrcConnStr = ConnectionStringS $SrcServer $SrcDatabase $SrcUser $SrcPwd
 $SrcConn  = New-Object System.Data.SqlClient.SQLConnection($SrcConnStr)
 $CmdText = "SELECT * FROM " + $SrcTable
 $SqlCommand = New-Object system.Data.SqlClient.SqlCommand($CmdText, $SrcConn)
 $SrcConn.Open()
 [System.Data.SqlClient.SqlDataReader] $SqlReader = $SqlCommand.ExecuteReader()

Try
 {
 $DestConnStr = ConnectionStringS $DestServer $DestDatabase $DestUser $DestPwd
 $bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($DestConnStr, [System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity)
 $bulkCopy.DestinationTableName = $DestTable
 $bulkCopy.BatchSize = $BatchSize
 $bulkCopy.BulkCopyTimeout = $TimeOut
 $bulkCopy.WriteToServer($sqlReader)
 }
 Catch [System.Exception]
 {
 $ex = $_.Exception
 Write-Host $ex.Message
 }
  Finally
 {
 Write-Host "Bulk copy completed"
 $SqlReader.close()
 $SrcConn.Close()
 $SrcConn.Dispose()
 $bulkCopy.Close()
 }