| Author |
Topic |
|
nmg196
Yak Posting Veteran
70 Posts |
 Posted - 2007-11-27 : 06:06:29
|
| Hi,I'm trying to replace all the data in a database on a production server with that from a development server. However if I try and do this, SQL Server seems to copy the data in the wrong order (or calls TRUNCATE TABLE in the wrong order) such that it fails because of the relationships set up between the tables.Eg it will try and call "TRUNCATE TABLE Contacts" before it calls "TRUNCATE TABLE CompaniesToContacts". This won't work because it causes errors such as "cannot truncate table tblContacts because it references a foreign key constraint". If I delete all the data manually, then I just get similar errors when it tries to INSERT data. Eg it will try and populate link tables before it's put the data in the main tables.How can I replace all the data in the live server database without deleting all the relationships? Surely there's a way of doing this, but SQL Server seems to make easy tasks very difficult!ThanksNick.... |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-11-27 : 06:18:14
|
| 1. Don't use TRUNCATE TABLE. Use Delete statement instead2. You can disable integrity constraints temporarily by using ALTER TABLE...NOCHECK CONSTRAINT constraint_nameHarsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
nmg196
Yak Posting Veteran
70 Posts |
Posted - 2007-11-28 : 05:47:46
|
| But there are dozens of tables and disabling constraints this way would take hours. I know SQL Server isn't exactly Microsoft's best product but surely SQL Server supports simply copying data between two identical databases?! People must do this every day.Why do you suggest using DELETE rather than TRUNCATE TABLE? Also, even with an empty database, I still get the problem that the copy tables wizard still copies the tables in the wrong order. This was a known bug in SQL 2000 until a recent service pack which fixed it, but it seems this problem was reintroduced in SQL 2005 as I've tried using SQL 2005 Management Studio to copy the data too :( Unfortunatly, you can't apply the service pack for SQL 2000 on a Windows Vista machine, so it looks like I might have to downgrade to XP after all (reason number 216 why I should never have upgraded to Vista). |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-29 : 02:52:01
|
| Does backup/restore work in this case? |
|
|
|
nmg196
Yak Posting Veteran
70 Posts |
Posted - 2007-11-29 : 04:31:34
|
| The problem is, I don't want to copy ALL the tables, just about 80% of them. Some of the tables on the destination server contain the 'live' information which must not be overwritten (user registrations, stats etc). |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2007-11-29 : 04:35:30
|
| How about setting up a DTS task to copy the tables in a certain order?This shouldn't take more than an hour to do (depending on amount of tables). |
|
|
|
nmg196
Yak Posting Veteran
70 Posts |
Posted - 2007-11-29 : 04:41:06
|
| Yeah I think that's what I'm going to have to do. It will probably take me much more than an hour as I've never created a DTS package manually before. I just refused to believe that something as simple as copying data couldn't be achieved using the built-in tools :( I've also had the suggestion of using Red-Gate's SQL Data Compare, so I'll give that a go first and post back with my findings. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2007-11-29 : 04:54:04
|
| It's fairly easy to create. Just define your connections, define a SQL task to run the truncates first, then each data pump task should be easy to define, you just have to make sure you get them in the right order. Once all your data pump tasks are defined and set up, just put the SQL task first and join it to your first connection with an on success task. |
|
|
|
|
Copying data problem (SQL to SQL)