How to move data in development database to production database since a date onwards?

Question

I have an application which has two database servers: one is the development database server and the other one is a production database server. Accidentaly, a couple of months back I published the application for use and it was pointing towards the development database.

Since it was pointing to the development database, all the information inserted or restored went in to the development database.

I want to transfer the data which went into the development database from july to september to the production database.

Is there a way to do this?

Answer 1

You can use DTSWizard to transfer data between two database. Write a SQL query to pull only the data you need.

Edit: I didn't realize it had fancy new name, SSIS.

Here's a good tutotrial for SQL Server 2005 Import / Export Wizard.

Answer 2

Create an SSIS package that uses the development database as the source and the production database as the destination and run a query to pull the data from development into production.

By the way, we have had this happen to us once.

Answer 3

The big issue here is to identify what changed. If you have datetime stamps in all your tables, this should be fairly easy.

If you do not have date time stamps, you will have to compare data. So let's call your database ConsumerData. If you have a backup of the DEV environment from the day before the production instance of the app pointed to it, you can restore it as ConsumerDataNew.

There are multiple database comparison utilities (Redgate SQL Compare) that will let you compare tables across the databases (ConsumerData and ConsumerDataNew) and give out results as SQL statements that you can then run in Production.

You will, of course, have to identify which records in Dev were made by development ppl vs. which ones were made by the Production Application.

Answer 4

But how do I pull up the data between july to september, since there are no date stamps at all.

Answer 5

Red-Gate has a tool called SQL Compare to compare the schema and SQL Data compare to actually compare the data.

This might be helpful.

Answer 6

the database has 21 tables all connected to each other,.... so its just not a single table. thats the reason it will be difficult for me to check each table and compare it. if there is some tool that does the comparison from the provided date...then i guess that wil solve my query