copying data from production to test server without erasing schema

Question

Using sql server 2000, I would like to take my production data and put it in my test database, but I don't want to overwrite the schema of the test database as there are fields in it that I haven't added to production yet. Can this be done? I should add that these databases are on different servers.

Answer 1

If you use a tool to generate scripts that explicitly names columns (such as SSIS), it should work, as long as there aren't columns in your production database that don't exist in dev.

Answer 2

Use something like OmBelt's exportSQLServer to SQL tool. (ombelt.com)

The inserts it makes specify fields so you should be okay. If not, mass-edit them.

I find it greatly simplifies SQLServer to have SQL dumps like other databases.

Answer 3

Use the INSERT INTO SELECT sentence to bulk from prod tables to your test environment avoiding your test fields that doesn't match in prod.

Answer 4

I think the Import/Export Wizard exists in SQL Server 2000. Wizards are generally irritating, but it only takes a few minutes and you probably already have it installed.

The wizard can be used to create a DTS package that can append all of the data from your production database to the end of tables in another database that already exists. If you have any new columns that are not null and don't have defaults I'm not sure how well that will be handled though.

You'll need to provide your production database as the source and the development database as the destination, then make sure to check the "append" option for new data rows.

Edit: I should note, this does work across servers, but I've only tried it from one SQL Server instance to another. In theory it works as long as the destination server supports ODBC, but I can't vouch for that.

Answer 5

I use Red-Gate software's SQL Data Compare. It will do diffs of the data in both databases and generate the appropriate transactional update scripts. It will exclude any columns that don't exist in both tables, so you should be fine even if you've added or deleted columns in your test database.