June 18, 2010 at 9:24 am
Guys I need some advice on how to move data between a test environment and a production environment. Currently I'm using SQL server 2008 when right now I use the import/export wizard to move data from test to production however, when I move from test to production I loose my primary keys. So I must ask how should I be moving the data in order to have everything come over intact?
June 18, 2010 at 1:54 pm
You can use Backup/Restore to restore the whole database instead of just the data.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 18, 2010 at 2:30 pm
I don't need the entire database I only need tables I'm moving changes from test to production. There has to be a way to do this
June 18, 2010 at 3:04 pm
Sorry - I misread this, thought you were moving from Production to Test.
Can you explain what you mean when you say you are losing your primary keys? I don't understand what issue you are having here.
Are you making schema changes and trying to move those? Or are you only trying to move data changes? If you are making schema changes to the tables, then it would be better to script out the changes and apply the changes to production instead of trying to replace the tables.
You might want to consider SQL Compare and SQL Data Compare from Redgate. These tools will help you migrate changes from one environment to another.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 19, 2010 at 2:29 am
To retain constraints in the new database and no need of data, you can use 'Generate Script' available in Sql Server Management Studio.
Steps to proceed:
1. Right Click the desired source database.
2. Click 'Tasks'
3. Click 'Generate Scripts...'
4. In the wizard dialog widow, click Next
5. Two options will be provided. a)Select entire db and their objects and b) Select specific objects. Select first option to get database design of all objects.
6. Output can be stored in three ways. a) A sql file b) Save to Clipboard and c)Save to new query window.
Then it will generate SQL Script to create database, tables, SP etc all objects with all constraints like Primary / Foreign keys, Index etc. In the destination database, execute this generated script, it will overwrite exisitng database with new database design.
June 21, 2010 at 12:03 am
i am not sure but if you want to migrate the data including primary keys to production database, you can use set identity insert on and off to explicitly insert primary key column values (assuming PK is an identity column)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply