9

I have a massive table, let's say 500,000 rows. I want to copy it (schema and data) from one server to another. This is not an upsert or any kind of update; It's a one-off straight copy and paste. What are the idiomatic approaches to this?

I've tried:

  • Restoring a backup from one server on to another. This is impractical, because SQL Server notoriously cannot restore tables from a backup; It can only restore databases. And my database is huge!
  • Using SSMS to script the table's data as a sequence of INSERT statements. This is impractical, because the inserts have to be done row by agonising row. I suspect that this also does awful things to the transaction log, but nobody has attacked me for this yet (I'm running such a script right now, it's going to take hours).
Improve this question
0

5 Answers 5

Reset to default
18
  • Create a new DB on the same server.
  • Use SELECT INTO to clone the table into that DB.
  • Backup that now-tiny DB.
  • Now you have a backup containing the data that you want, and nothing else, so it can't be too large. If it is, then your issue is the data, not the means of moving it.
  • Copy the backup to the new server.
  • Restore the backup as a new temporary DB on the new server.
  • Copy the data from the temporary DB on the new server into the permanent DB on the new server.
Improve this answer
0
12

What I've used a lot is native SQL Server Import and Export Wizard.

In SSMS's Object Explorer -> Source SQL Server -> Right click database -> Tasks -> Export Data

Choose a Data Source: Microsoft OLE DB Provider for SQL Server.
Server Name: source server.

Choose a Destination: Microsoft OLE DB Provider for SQL Server.
Server Name: destination server.

Specify table copy or query: Copy data from one or more tables or views.

Note that this will copy table as a Heap (columns and data are there, but no index), you will need to script and create indexes, constraints etc. on destination table manually (which is not hard if there are not many of them).

Improve this answer
10

One option is to use copy-dbadbtabledata from https://docs.dbatools.io/ powershell module.

copy-dbadbTableData -SqlInstance sourceserver -Database sourcedatabase -Table dbo.sometablename -Destination destinationserver -DestinationDatabase destinationdatabase -DestinationTable dbo.sometablename

you can also add parameters if the table doesnt already exist in destination (-AutoCreateTable) or if it does and has data in it (-truncate).

Another option would be to use BCP.

bcp sourcedatabase.dbo.sometablename out D:\BCP\table.bcp -c -U<login_id> -Ssourceserver bcp destinationdatabase.dbo.sometablename IN D:\BCP\table.bcp -c -T
Improve this answer
3
  • How do these compare? I'm only at the start of my dbatools journey, but I know that is it quite modern and that BCP is quite old. Also, am I right to think that Copy-DbaDbTableData can move things cross-serer? If so, it may answer this question. Commented Jan 8, 2024 at 21:34
  • I think they both ultimately do the same thing (BULK INSERT). dbatools is one command vs two for bcp, so more concise. can definitely move data cross server. Commented Jan 9, 2024 at 14:36
  • 1
    copy-dbadbTableData has proven to be awesome, but be warned that its very much optional and useful -Query parameter needs to have output that matches the schema of its -Table parameter. You cannot specify -Query without specifying -Table or something equivalent. Commented Apr 9, 2024 at 19:46
3

Another option is a tool like SQL Examiner Suite. It's not free, but has been a very helpful tool for me over time:

SQL Examiner Suite 2022 includes two award-winning products in one convenient package. SQL Examiner compares and synchronizes database schemas, while SQL Data Examiner performs the same task for actual data stored in databases.

SQL Examiner Suite is a comprehensive solution performing fully automated comparison and synchronization of any two databases complete their structures and data. Databases in a variety of formats are supported, including all versions and editions of MS SQL Server from version 7.0 to 2019, PostgreSQL, SQL Azure Database, as well as most basic structures and objects of Oracle and MySQL databases. SQL Examiner fully supports all types of database objects found in MS SQL databases, and correctly synchronizes between different versions of MS SQL.

It's very helpful at highlighting the differences and synchronizing changes automatically. It can be scheduled and ran via the command line, and can also generate the synchronization scripts for later use.

Note I am not affiliated with this software in any way, other than as a satisfied user.

The above is helpful for one time type of syncs, but if you need continuous automated data and schema synchronizations, there are other native features in SQL Server I can mention.

Improve this answer
1
  • 1
    This just reminded me. Visual studio with SQL Server Data tools can also do a data sync. Commented Nov 24, 2023 at 20:14
3

SQLServer Integration Services(SSIS) is also an effecient way can be used to move data to and from servers. It also has the following additional benefits:

  • Speed
  • Scheduling
  • Deploy indipendently from source/destination databases
  • Leverage readable replicas if available
  • Control flow for error handling
  • Monitoring dashboard
  • Ready for complex dataflow/pipeline

Regards

Improve this answer
0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.