Often you need to copy tables between Sql Server database servers. In your environment, you cannot use or setup linked servers. How do you accomplish this without restoring the entire database on the destination server? Read along to see an easy way.
Read more: Copy SQL Server tables between servers easily with dbatools.A scenario you will often find as a DBA, is to copy over table data to a different environment. The users do not want the entire database, but just the one table.
Using dbatools, this is very simple. To read more about setting up dbatools, please refer to my earlier blog posts.
Here is my current TEST database. It does not have any tables at this time.
After having dbatools installed, this simple command will copy a table from one database server to another.
Copy-DbaDbTableData -sqlinstance SOURCEINSTANCE -database warehouse -table dbo.aging -destination DESTINATIONINSTANCE -destinationdatabase Test -autocreate
The parameters you will need to change are easy enough:
SOURCEINSTANCE, TABLE, DESTINATIONINSTANCE, and DESTINATIONDATABASE. If you add -Autocreate, it will create the table automatically for you.
(why is this screenshot so small? You will need to trust me)
This is it running and showing progress.
This is the results of the command finishing. It shows (really it does), the table name, servers, number of rows transferred and time it took to complete.
If we look in the test database, you can see that the table has been copied over.
There are always multiple ways to accomplish tasks in Sql Server. Using dbatools is a huge time saver.
Jim Salasek's SQL Server Blog 