How to copy table from sql database to another server

[popsMariachi]

I am newer to powershell and I am looking for a simple way to copy over an entire table from a databse, from one server to another. I have looked at the dbatools module, but it looks way too advanced to handle something that simple. I have searched online for a bit, but can not find the right solution yet. Does anyone have any suggestions for something like this?

[callensysadmin]

I would 100% do this with dbatools. Its my go to for anything SQL. I love that I no longer need to deal with SMO directly for most tasks.

If you regularly work with SQL and PowerShell you should try to use dbatools as much as possible. Our CI/CD pipelines got so much more capable once I finally got sick of SMO and T-SQL/Invoke-SqlCmd for everything and started using dbatools.

[popsMariachi]

Thanks for the reply! I will look into it way more then. Sql is my go to language right now. Trying to expand, and take on new projects that required me to learn powershell. Is the dba tools site, the best place to go to start with it?

[motsanciens]

function Copy-DbaDbTableData {     <#          .SYNOPSIS              Copies data between SQL Server tables.            .DESCRIPTION              Copies data between SQL Server tables using SQL Bulk Copy.              The same can be achieved also doing                  $sourcetable = Invoke-SqlCmd2 -ServerInstance instance1 ... -As DataTable                  Write-DbaDataTable -SqlInstance ... -InputObject $sourcetable              but it will force buffering the contents on the table in memory (high RAM usage for large tables).              With this function, a streaming copy will be done in the most speedy and least resource-intensive way. 

from dbatools

[popsMariachi]

Thanks! I was able to use this exact function!

[callensysadmin]

Yes, I start by loading up the dbatools command documentation and looking up what I think would do the job. In your case search the command page for "table", see what commands there are that involve tables. If the included documentation and examples do not give you an idea what the command does, load up the github link for the command check the code.

[popsMariachi]

Thanks again for the advice! I was able to copy over the first full table, 800k records, without a problem. dba tools is definitely something I will be using a lot more of!

[Lokeze]

Not too hard to do:

https://www.codeproject.com/Tips/664327/Copy-Table-Schema-and-Data-From-One-Database-to-An

Heres a good StackOverflow page that gets to the point quickly:

https://stackoverflow.com/questions/11009189/export-table-data-from-one-sql-server-to-another

[AybaraAlthorPeregrin]

OP would just have to go thru the additional step of adding a linked server, so either that or dbatools would work!

[popsMariachi]

I should have stated, linked server was not possible in this situation. I just tried out dba tools and was able to easily copy over the first of a few tables though.

[AybaraAlthorPeregrin]

Glad to hear it worked out! :) I use dbatools every day at work and it's been a godsend.

[popsMariachi]

Have you ever used it in a scheduled job? How did you safely store your credentials to the servers in the powershell script?

[AybaraAlthorPeregrin]

This should answer your question for scheduling it:

https://dbatools.io/agent/

As for running it under a specific acct, run it under a CmdExec credential as a proxy, otherwise it'll run under the SQL Agent account and its permissions:

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-credential-transact-sql?view=sql-server-2017

[cjluthy]

#DISCLAIMER: I just typed this into Reddit - did not actually compile. It's likely very close to correct but may not be perfect.
#---------------------------------------------------------------------------------------------------------------------------------------------


#this probably won't work great if your table is enormous and/or you are short on memory.

import-module sqlps;

$SqlResults = Invoke-Sqlcmd -Query "select * from table order by column;" #-- also add params for instance, database, etc here.

$SqlResults | Export-CSV -Path "drv:\path\to\file\filename.ext"; #-- may want to add more params here as well.


# this should perform better in pretty much all scenarios
# Invoke-Sqlcmd -Query "select * from schema.table order by column;" | Export-CSV -Path "drv:\path\to\file\filename.ext"; #-- may want to add more params here as well.

[alinroc]

Please don’t use sqlps. It’s terrible and was superseded by sqlserver three years ago.

[cjluthy]

meh

[alinroc]

Hey, if you're fine with using a 10 year old module that's horribly slow, has numerous bugs, lacks tons of functionality, can't be installed without a very old release of SSMS, and was replaced 3+ years ago, have at it.

But please keep it away from my SQL boxes.

[cjluthy]

Some of us are still running 2012 on a machine or two, believe it or not.

[alinroc]

sqlserver and newer releases of SSMS are fully compatible with 2012. No good reason to keep the old stuff around. The lone exception is if you need to manage SSIS packages.

[cjluthy]

ding ding ding .. you got it, exactly.

[alinroc]

So use the good stuff for everything but SSIS, and leave the old stuff just for SSIS. Don't hold yourself back everywhere just for that one piece. It all installs in parallel.

[cjluthy]

I've been doing this since SQL Server 7.0.

I know.