1

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?

Improve this question
3
  • Why he TRUNCATE before INSERT? This way you are going to copy the structure of the table but not the data
    – Francesco Mantovani
    Jan 4, 2022 at 12:33
  • He truncates on [Table1], which is my preproduction, and the insert gets the data from [Table2], which wasn't truncated.
    – Iván Flores Vázquez
    Jan 4, 2022 at 15:22
  • Arrived at this point, given the complexity, you can start building a proper ETL process with SSIS
    – Francesco Mantovani
    Jan 4, 2022 at 15:38

2 Answers 2

Reset to default
3

Database backup and restoration is safest method to achieve what you are looking for.

You may use compression method and stripe(multiple files) to make size of your backup smaller and then copy the backup file to your second server and restore it.

You may also use bulk copy(BCP) for copying however that will again be at table level and not at database level.

Please also make a note of one important point - In case, you have any foreign key constraints on these tables, you need to copy them in the right sequence otherwise it would fail.

To avoid all these issues, backup and restoration is the most reliable, safest and fastest method.

Database backup contains all indexes, constraints and every objects inside database, it will be part of one bundle called database backup and you can restore it on second server.

Improve this answer
2
  • Would this present no issue (or take too long) with indexes and constraints?
    – Iván Flores Vázquez
    Jan 4, 2022 at 15:26
  • No, all indexes, constraints and all objects inside database will be part of one bundle called database backup and you can restore it on second server. About the size, I have suggested to use compression method and stripe. I will edit my answer.
    – Learning_DBAdmin
    Jan 5, 2022 at 5:35
0

An option could be the SQL Server Import and Export Wizard.

  1. Right click on the database > Tasks > Export Data...
  2. In Choose Data Source set SQL Server Native Client 11.0 enter image description here
  3. Do the same for the Choose a Destination
  4. Select Copy data form one or more tables or views

...and execute

enter image description here

Improve this answer
2
  • 1
    All good unless you have foreign key constraint.
    – Learning_DBAdmin
    Jan 4, 2022 at 12:54
  • Good point. What about Tasks > Export Data-tier Application... ? That is used for migration to Azure too and keeps constraints. Otherwise Transactional Replication could be an option?
    – Francesco Mantovani
    Jan 4, 2022 at 13:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.