SQL 2005: Mass data copy
I need to copy data from a production database to a test database. There is a large amount of data and over 2000+ tables. The schema of the test database must be left alone, so I cannot do a backup and restore of the production database.
Once I have deleted all the data from the test database, I use the Export Wizard to move the data across. It appears to be moving all the records, but the thing I'm seeing is that Identity fields don't retain their values and so parent-child tables don't work. I have checked the "Enable Identity Insert" flag in the wizard, but it does not seem to implement it during the copy. If I compare a sequence field between the production db and test db, the values are different.
However, if I use the wizard to copy a single table using the "Enable Identity Insert" flag set, the sequence values are retained and everything works fine. The problem is, I can't do individual table moves or groups of tables in order to try to get this to work.
Is there another way to get the export wizard to work properly, or is there another (free) utility that would do this?
Once I have deleted all the data from the test database, I use the Export Wizard to move the data across. It appears to be moving all the records, but the thing I'm seeing is that Identity fields don't retain their values and so parent-child tables don't work. I have checked the "Enable Identity Insert" flag in the wizard, but it does not seem to implement it during the copy. If I compare a sequence field between the production db and test db, the values are different.
However, if I use the wizard to copy a single table using the "Enable Identity Insert" flag set, the sequence values are retained and everything works fine. The problem is, I can't do individual table moves or groups of tables in order to try to get this to work.
Is there another way to get the export wizard to work properly, or is there another (free) utility that would do this?
MuffyBunny
You could use SSIS
ASKER
that was one of my thoughts, but how do I set it up to loop through 2000 tables?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
good link