Copy SQL table from one server to another using Powershell

Often, a DBA like us gets user requests to copy a table data from one instance to another. In that case, we first see the amount of data present inside the table then we decide a method to have the data transferred. Normally we choose SSIS method and ofcourse that is a suggested way to transfer data between two SQL Server instances. However, you can’t reuse the same SSIS package to copy a different table having 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. Also SSIS would not work if a connection can not be made between two instances, In my previous blog post I have shown how to copy SQL table from one server to another using BCP, which is a very convenient method for large tables, where a connection can not be made directly between two SQL Server instances. We have another method to transfer table data using Powershell!! Powershell is a very powerful scripting tool built on the .NET Framework. Here I have used SqlBulkCopy method to transfer table data between two instances.

You need to change the execution policy in powershell first. Either you have to set it 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”

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

8 thoughts on “Copy SQL table from one server to another using Powershell

  • March 13, 2015 at 12:38 pm
    Permalink

    i have tried this but it is saying login failed for sa , but the creds are fine . Please suggest

    Reply
    • March 14, 2015 at 11:27 pm
      Permalink

      Harsha, There are two SQL servers to be connected.. One is the source and the other one is destination. You will have to pass through into two SQL servers by providing their credentials. Even if you are transferring data within the same instance and between two different databases, then also you will have to provide the same credentials twice one for source and another for destination.

      Reply
  • February 3, 2016 at 4:19 pm
    Permalink

    BCP VS Powershell which would be faster in copying the table across two databases.

    Reply
    • February 4, 2016 at 9:57 pm
      Permalink

      Technically both are same and uses Data.SqlClient.SqlBulkCopy method but I have noticed powershell works lil faster.

      Reply
  • August 2, 2017 at 12:26 am
    Permalink

    Could this script be modified to use Windows Integrated Security if you were logged into an account that had permissions on both systems like the SQL Server Agent user?

    Reply
      • October 24, 2017 at 12:55 am
        Permalink

        Prasad, were you ever able to get your utility to work with WIndows Integrated Security?

        Reply

Leave a Reply

%d bloggers like this: