Best practice to copy a lot of data between databases

SQL Server Integration Services

Stijn Fonck


Hi,

I have a database on sqlserver 2000 which contains some tables which receive Create/Read/Update statements from applications, and +-5 tables which contain a sort of read-only data: the applications are only reading from these tables.

Sometimes these 5 tables need to be updated with new data. Currently I am doing this as follows:
  1. Execute a long-running operation (e.g. 1 week), which will add new data, against a Test-database (which is a restore from a backup of the original database)
  2. Some people do some checks to see if the new data in the Test-database is correct
  3. If (2) is OK, the only thing which needs to be done is copying the data of the 5 tables in testdatabase to the 5 tables of the production-database.
For (3), I currently use a DTS-package which consists of a "Copy SQL Server Objects"-task. This task is configured to copy the 5 tables (objects) from testdatabase to productiondatabase. The data in these 5 tables is around 20GB, and this task takes a lot of time compared to a backup/restore of the same size of data. I already tried to speed it up by creating different filegroups and wanted to restore only 1 filegroup but you can read here that making a filegroup backup on a testdatabase and trying to restore it on a production database won't work. Is there some other way to speed this up Is my current way of working good practice

Thx!

Top

Re: Best practice to copy a lot of data between databases

Philippe Cand


I think that 20GB of data should be copied in 10 to 20 minutes at the most if you use standard source and destination with query or table source.

Like a standard query. Stay away from the copy SQL server Objects for large objects.

Set the destination to be like tb_DestTemp. A temp table with no indexes.

Use something like the bellow to switch to prod name when the load is complete, Add the necessary indexes creation statements.

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[tb_Dest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [tb_Dest]
EXEC sp_rename 'tb_DestTemp', 'tb_Dest'



Top