MS SQL: using BCP + Powershell + Bulk Copy to sync your table to another server

The usage scenario is: you have a huge log table (or some tables like that) and you want to sync it from your production server to another server (slaver server) for analyzing daily. This will help to reduce workload of production server in analyzing as also as backup your data on slaver server. You even can delete old data (not used more) on your production server to minimize the size of production data.

Below steps to setup this sync job:

1. Use BCP to export & import data
On production server, run bcp command to export data, for example:
#bcp "SELECT * FROM log_table" queryout "C:\log_table.bcp" -N -S "PRODUCTION_SERVER_NAME" -d "DB_NAME" -U "USER_NAME" -P "PASSWORD"

After that, upload this log_table.bcp file to your slaver server, create a log_table having same structure as on production server, then run a command like example below to import data to your log_table:
#bcp "log_table" IN "E:\log_table.bcp" -N -S "SERVER_NAME" -d "DB_NAME" -U "USER_NAME" -P "PASSWORD"

2. Make Powershell script to bulk copy new data from production server to slaver server
Below is my sample script:

#Set-ExecutionPolicy Unrestricted
#2 params needed to provide for the script
Param (
[parameter(Mandatory = $true)]
[string] $LogTable,
[parameter(Mandatory = $true)]
[int] $Syncrows
)

$SrcServer = "PRODUCTION_SERVER"
$SrcDatabase = "PRODUCTION_DB"
$SrcUser = "USER_NAME"
$SrcPwd = "PASSWORD"
$SrcTable = $LogTable

$DestServer = "SLAVER_SERVER"
$DestDatabase = "SLAVER_DB"
$DestUser = "USER_NAME"
$DestPwd = "PASSWORD"
$DestTable = $LogTable

#create connection string
Function ConnectionString ([string] $ServerName, [string] $DbName, [string] $User, [string] $Pwd)
{
    "Server=$ServerName;uid=$User;pwd=$Pwd;Database=$DbName;Integrated Security=False;"
}

# executes a query and populates the $datatable with the data
function ExecuteSqlQuery ($ConnectionString, $SQLQuery) {
    $Connection = New-Object System.Data.SQLClient.SQLConnection
    $Connection.ConnectionString = $ConnectionString
    $Connection.Open()
    $Command = New-Object System.Data.SQLClient.SQLCommand
    $Command.Connection = $Connection
    $Command.CommandText = $SQLQuery
    $Reader = $Command.ExecuteReader()
    $Datatable = New-Object System.Data.DataTable
    $Datatable.Load($Reader)
    $Connection.Close()
    $Connection.Dispose()
    return ,$Datatable #work arround to get right datatable after unrolling arrays/collections
}

#get max id of last synced rows
$DestConnStr = ConnectionString $DestServer $DestDatabase $DestUser $DestPwd
$Query = "SELECT MAX(Pid) FROM " + $DestTable
$resultsDataTable = ExecuteSqlQuery $DestConnStr $Query
$LastRow = $resultsDataTable.Rows[0][0]

#bulk copy
$SrcConnStr = ConnectionString $SrcServer $SrcDatabase $SrcUser $SrcPwd
$SrcConn  = New-Object System.Data.SqlClient.SQLConnection($SrcConnStr)
if($Syncrows -eq 0) {
    $CmdText = "SELECT * FROM $SrcTable WHERE row_id>$LastRow"
}
else {
    $CmdText = "SELECT TOP ($Syncrows) * FROM $SrcTable WHERE row_id>$LastRow"
}
#Write-Host "$CmdText"
$SqlCommand = New-Object system.Data.SqlClient.SqlCommand($CmdText, $SrcConn)
$SrcConn.Open()
[System.Data.SqlClient.SqlDataReader] $SqlReader = $SqlCommand.ExecuteReader()
Try
{
   $logmsg = "$LogTable : Bulk copy completed!"
   $bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($DestConnStr, [System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity)
   $bulkCopy.DestinationTableName = $DestTable
   $bulkCopy.BatchSize = 100 #rows
   $bulkCopy.BulkCopyTimeout = 1800 #seconds
   $bulkCopy.WriteToServer($SqlReader)
}
Catch [System.Exception]
{
   $ex = $_.Exception
   $logmsg = $ex.Message
}
Finally
{
   $currenttime = Get-Date -format s
   Add-Content D:\logstatus.txt "`n$currenttime $logmsg"
   $SqlReader.Close()
   $SrcConn.Close()
   $SrcConn.Dispose()
   $bulkCopy.Close()
}

The above script will use BulkCopy to copy rows having id > id synced. You can limit number of rows for syncing via $Syncrows param. If $Syncrows=0, it will get all new rows to copy.

3. Create task schedule for copying new data
Use Task Scheduler to create a new task for running the script file, you can select your schedule as your demand. Remember to add below line in Action setting for passing Execution Policy:

-ExecutionPolicy Bypass D:\synctbl.ps1 -LogTable log_table -Syncrows 10000

That's all. Happy coding! Any questions?