What I have to do
I need to copy the data of a lot of tables (over 500) from a production database to a pre-production database, to better test our systems before deployment.
A lot of those tables are quite small, but some of them are very large, the largest has currenly around 55 million rows, but grows somewhat fast.
What a coworker suggested
A coworker was doing something similar in a test environment, and developed a script to do this, which basically did:
TRUNCATE [TableName1] SET IDENTITY_INSERT [TableName1] ON INSERT INTO [TableName1] (Col1, Col2,...) SELECT (Col1, Col2,...) FROM [TableName2] SET IDENTITY_INSERT [TableName1] OFF
And did this for around 30 tables he was running tests on. I tried to start with this approach, but when I tested with the first table (the 55 million rows one), it's just taking too long.
What is different between my coworkers' case and mine
I see 2 differences between what my coworker did and what I have to do:
- The largest table he worked with was far smaller than the largest one we have.
- All the tables he was copying from and copying to were on the same server, but I have to copy the tables from one server to another.
What I've tried
I first tried using Linked servers, and it seems to work since I see no error, but it's taking forever, and my boss wants this task to be performed every two weeks at night, so it's unfeasible for it to take so long.
Which would be the most efficient way to copy over 500 large tables to another database, hopefully in less than two hours?