patternsqlModerate

What are the idiomatic approaches to copying a table (and all of its data) from one server to another?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

thewhatallareserveridiomaticitsonecopyinganother

Problem

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).

Solution

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.

Context

StackExchange Database Administrators Q#333426, answer score: 16

Revisions (0)

No revisions yet.