Summary: Learn how to use Windows PowerShell to quickly and efficiently copy a table between two SQL Server instances.
Hey, Scripting Guy! How can I copy a table between two SQL Server instances?
—PY
Hello PY, Microsoft Scripting Guy, Ed Wilson, is here. Today is the last day for this week’s SQL Guest Blogger series. Today our guest is Yan Pan.
Yan Pan has 10 years working on various versions of SQL Server (MCITP SQL Server 2008, MCITP SQL Server 2005, MCDBA SQL Server 2000, plus OCA Oracle 10g), mainly in the financial services sector. She is a senior database architect, and she wrote a Wrox book titled Microsoft SQL Server 2008 Administration with Windows PowerShell. Besides SQL Server and Windows PowerShell, she also develops complex BI solutions with Microsoft technologies, works on other databases, such as Oracle and columnar databases, programs in .NET, and performs financial data development and research. If you would like to read more articles by her, visit her website at http://yanpansql.com/.
Very often, a DBA gets user requests to copy a table from a production to a development environment for testing purposes, or to synchronize a table in a reporting database with a production database. As a DBA, you can create an SSIS package with a Data Flow task in Business Intelligence Development Studio (BIDS), or you can launch the Import and Export Wizard from SQL Server Management Studio and let the wizard create an SSIS package for you.
However, you cannot reuse the same SSIS package to copy a different table with a different structure. You have to create a new package for the new table or modify the column mappings of an existing package in BIDS. It would be far more convenient to have a script that takes the names of the source server, source database, source table, destination server, destination database, and destination table as the parameters, and can be reused by simply providing different names.
Scripting can make a DBA’s life much easier. You might ask, “Why not use the bcp utility?” Yes, you can export the source table to a file and then import it to the destination table from the file. However, it requires creating a file on disk. We all know I/O is typically the most expensive resource with data manipulation activities. Of course, you can also use non-Microsoft tools, such as SQL Data Compare from Red Gate, but these tools come at a cost, and they are not usually optimized for your special use cases. They can be slow and resource intensive when the target table size is big.
Here I introduce a Windows PowerShell script that can be reused to copy the data from a source table to a destination table. This script uses the .NET SqlBulkCopy class with a streaming IDataReader to achieve the optimal performance (using a single thread).
Let us first define the parameters in the following param block. As you can see, the script requires a source server name, a source database name, a source table name, and a destination server name. The names of the destination database and table are optional. If you do not provide them, the script assumes that they are the same as the source. If you have a table in a non-default schema, you also need to specify the table names as two-part names. For example, if the user account under which you run the script maps to a user in the destination database with the default schema dbo, but you want to copy the data into an Order table in a non-default schema Test, then you need to specify the destination table name as Test.Order. The script also uses a switch parameter, Truncate. When this switch is included, the destination table is truncated and the existing data is removed before the copy.
Param (
[parameter(Mandatory = $true)]
[string] $SrcServer,
[parameter(Mandatory = $true)]
[string] $SrcDatabase,
[parameter(Mandatory = $true)]
[string] $SrcTable,
[parameter(Mandatory = $true)]
[string] $DestServer,
[string] $DestDatabase, # Name of the destination database is optional. When omitted, it is set to the source database name.
[string] $DestTable, # Name of the destination table is optional. When omitted, it is set to the source table name.
[switch] $Truncate # Include this switch to truncate the destination table before the copy.
)
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
}
Next, we prepare a SqlDataReader, a forward-only stream of rows from the source table. In our example, we have an Order table in the AdventureWorks database on the source server, YYCenter. Notice that we also create the helper function ConnectionString to return a connection string based on a server name and a database name.
Function ConnectionString([string] $ServerName, [string] $DbName)
{
“Data Source=$ServerName;Initial Catalog=$DbName;Integrated Security=True;”
}
$SrcConnStr = ConnectionString $SrcServer $SrcDatabase
$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()
We are now ready to do the bulk insert into the destination table.
Try
{
$DestConnStr = ConnectionString $DestServer $DestDatabase
$bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($DestConnStr, [System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity)
$bulkCopy.DestinationTableName = $DestTable
$bulkCopy.WriteToServer($sqlReader)
}
Catch [System.Exception]
{
$ex = $_.Exception
Write-Host $ex.Message
}
Finally
{
Write-Host “Table $SrcTable in $SrcDatabase database on $SrcServer has been copied to table $DestTable in $DestDatabase database on $DestServer”
$SqlReader.close()
$SrcConn.Close()
$SrcConn.Dispose()
$bulkCopy.Close()
}
You can also set a batch size, for example, 1000 rows, or increase the default time-out of 30 seconds to 60 seconds by setting the properties of the SqlBulkCopy instance as shown here.
$bulkCopy.BatchSize = 1000
$bulkCopy.BulkCopyTimeout = 0
Since Try/Catch/Finally block is only available in Windows PowerShell 2.0, we add the version tag into the script.
#requires -version 2.0
Let us put all the pieces of the script together. Here is the complete script Copy-SqlTable.ps1, and it can also be found in the Script Repository.
#requires -version 2.0
Param (
[parameter(Mandatory = $true)]
[string] $SrcServer,
[parameter(Mandatory = $true)]
[string] $SrcDatabase,
[parameter(Mandatory = $true)]
[string] $SrcTable,
[parameter(Mandatory = $true)]
[string] $DestServer,
[string] $DestDatabase, # Name of the destination database is optional. When omitted, it is set to the source database name.
[string] $DestTable, # Name of the destination table is optional. When omitted, it is set to the source table name.
[switch] $Truncate # Include this switch to truncate the destination table before the copy.
)
Function ConnectionString([string] $ServerName, [string] $DbName)
{
“Data Source=$ServerName;Initial Catalog=$DbName;Integrated Security=True;”
}
########## 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 = ConnectionString $SrcServer $SrcDatabase
$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 = ConnectionString $DestServer $DestDatabase
$bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($DestConnStr, [System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity)
$bulkCopy.DestinationTableName = $DestTable
$bulkCopy.WriteToServer($sqlReader)
}
Catch [System.Exception]
{
$ex = $_.Exception
Write-Host $ex.Message
}
Finally
{
Write-Host “Table $SrcTable in $SrcDatabase database on $SrcServer has been copied to table $DestTable in $DestDatabase database on $DestServer”
$SqlReader.close()
$SrcConn.Close()
$SrcConn.Dispose()
$bulkCopy.Close()
}
I used the script to copy the Production.ProductInventory table in the AdventureWorks sample database on YYCenter to the Production.ProductInventory table in the AdventureWorks database on YYCenter\SQL2008R2. The following image shows the output of the script on my computer.
Thanks, Yan Pan—that was an excellent article.
Well, this concludes SQL Guest Blogger Week. Join me tomorrow for the Weekend Scripter as I kick off the 2011 Scripting Games wrap-up articles.
I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.
Ed Wilson, Microsoft Scripting Guy
0 comments