September 18, 2006 at 11:58 am
Hi everyone,
I need to move data from testing database to production database.
The table name is the same in two different database.
testing database is sitting on one sql server instance(msde) and production database
sitting on another sql server instance(sql server 2000).
Is there an easy way?
Thank you.
Betty
September 18, 2006 at 12:00 pm
How do you need to transfer the data??
Everything gets transfered?
Only new data gets added?
Data must be inserted and updated but not deleted?
September 18, 2006 at 12:04 pm
RGR'us,
Just some of the tables, only new data within specific period of time.
Data must be inserted into production database, no data need to be deleted or updated
Thanks.
Betty
September 18, 2006 at 12:12 pm
Can you make a linked server from the production database to the msde instance?
If you can't :
Make a select into statement that extracts the valid data to a new table.
Go to the production box and import that data back using the import data wizard.
Insert the data from the newly transfered table to the production table.
MAKE SURE YOU HAVE A BACKUP!!!.
September 18, 2006 at 12:37 pm
do I have to list all table column name like this:
Insert into table1_name_temp (value1, value2...)
Select * from table1_name where ....
Because there are many column/
Betty
September 18, 2006 at 1:07 pm
Betty,
You can use DTS to move the data. If the tables have the same schema and you're only moving new data, I recommend using the Import/Export Wizard and choosing "Append data" on the column mapping tab.
Greg
Greg
September 19, 2006 at 1:12 pm
I would be opposed to all of these solutions. Production and test should remain separated entities. If you do a direct import of any kind from test into production, you run a risk of importing bad data that someone been mucking around with in test.
Everything should be scripted. If it is a lot of data, then put it into a staging table first and run a script to migrate it from the staging table to the produciton table. Don't just blindly import data that hasn't been verified as 100% good.
September 19, 2006 at 1:21 pm
That's a good point. IIRC correctly this is a case where the prod server was accidentally pointed onto a test server. And only a few rows need to be ported back on the right server.
However I agree that this is still a dangerous task!
September 19, 2006 at 1:21 pm
yes, you are right. Actually I didn't do anything yet.
If I did, I would brought more problems since some of auto data or system assigned data are created based on testing database environment which would inflict data in the production database.
I am glad I just didn't do anything yet.
I will either extract data to file/temp table and check the file/tble to make sure the data is all right and then import/script into production database.
Any more concern? I always love it.
Betty
September 19, 2006 at 1:28 pm
What we have done in the past is to have a database that we called ThirdParty in dev/test/produciton. When we have a data source (from customer, third party vendor, etc.) that needs to get in to the system, we load the data into the dev ThirdParty database and write scripts to deploy it to the real database. This process is repeated in test and production. We always load and deploy, We never directly imported into the destination database at any level.
September 19, 2006 at 1:31 pm
Thanx for recalling us that nothing is more important than the data... even when you are certain that nothing can go wrong!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply