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?
Who is Participating?
You could use SSIS
wppiexpertsAuthor Commented:
that was one of my thoughts, but how do I set it up to loop through 2000 tables?
wppiexpertsAuthor Commented:
good link
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.