Moving Data Between SQL Servers with Powershell

Scripting data is a pain, especially if you do not want the entire contents of a table. OpenRowset is verbose and clunky. And working with linked servers may not be the seventh layer of hell, but you’re going to smell some smoke. What I really want is to pass some parameters to a function and be done with it. Hello, PowerShell. Here’s how it works:

Create a test table on two different servers

CREATE TABLE EmailAddress
(
     AddressID INT NOT NULL,
     EmailAddress VARCHAR(100) NOT NULL
) ;

Create some test data on one server.

WITH email AS
(
     SELECT 1 AS id, 'bgates@hotmail.com' AS EmailAddress
     UNION ALL
     SELECT 2, 'mzuckerberg@facebook.com' 
)
INSERT INTO EmailAddress
SELECT id,EmailAddress FROM email 

Here’s the function

function Move-Data 
{

Param
(
$sourceServerName ,
$sourceDatabaseName,
$destinationServerName,
$destinationDatabaseName,
$destinationTableName,
$query
)

$dataTable = Invoke-Sqlcmd -Query $query -Database $sourceDatabaseName -ServerInstance $sourceServerName ;

$connectionString = "Data Source=$destinationServerName;Integrated Security=true;Initial Catalog=$destinationDatabaseName;" ;
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString ;
$bulkCopy.DestinationTableName = "$destinationTableName" ;
$bulkCopy.WriteToServer($dataTable) ;
}

Call the function

$query = "SELECT AddressID,EmailAddress FROM EmailAddresses WHERE AddressID = 1" ;
Move-Data server1 MyDB server2 MyDB EmailAddress $query
Advertisements

2 Responses to Moving Data Between SQL Servers with Powershell

  1. Anthony says:

    In your Function code, in the parameters there is a spelling error:
    $desinationDatabaseName **Missing t in destination** should be $destinationDatabaseName

    Took ages to figure out why this wasn’t working! Added the t and it worked perfectly!

    Thanks for the script!

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

%d bloggers like this: