PROD to DEV constraints fail
- Hi,I have been performing backup/restores once a month on the previous night's production backup to freshen up the data on our dev server. This has been a workable solution, but our manager is hoping that we could do a DTS transformation on a more regular basis that would only commit the new data since the last restore. I attempted to do a SS 2K - SS 2K copy objects, but it fails, citing a foreign key constraint failure. I'm sure I'm only having the first error reported to me, and not all of them.I'm a C programmer by profession. This database work is an extension of my duties, as if anyone doesn't have his duties extended these days, so I'd appreciate any help, even anything that might be very obvious to a database veteran.For your context when answering, the first process I tried did not create destination objects, copied the data only (append data), no collation, copied all objects, used default options. The second time I tried the process, I did create destination objects, did not drop destination objects first, replaced existing data, and the rest was the same from before.What I'm hoping to learn is that there's a particular way to do this properly, even if it means running multiple DTS tasks, which I learned can be packaged and scheduled.Thank you.
-
A foreign key constraint is a relationship between tables to enforce data integrity. It basically checks for the existence of a key in one table before allowing the record in the second table with that key to be inserted/deleted etc.
For example if you had an Orders and Products table it will stop the creation of orders for products that don't exist in the Products table.
You can probably solve your problem by changing the sequence in which you are importing your tables. ie For the above example load the products table first.
You could also just remove the constraints from your dev environment tables. Depends how important the data integrity is in this environment.
Daniel
-
just my 2ct to add to Daniel's reply :
another downside with DTS it when there are schema-changes !
IMO for this prod to dev stuff, the easyest way is to automate a restore from a backup. Schedule it as a sql-agent-job after dev-working-hours.
There are tools like DB-Ghost and SQL Data Compare that are designed to do what you are aiming for. They are not _that_ $$ .
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
If you want to use DTS, here is a script that should disable triggers and foreign key contraints using the undocumented stored procedure sp_msforeachtable.
-- Disable all constraints and triggers
sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
go
sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"
go
After running DTS, run the following:
--Enable all constraints and triggers
sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
go
sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? ENABLE TRIGGER all"
go
alzdba is correct, schema changes can mess you up. I use Red-Gate's SQL-Compare to see if there are any changes in the structure.
Steve
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply