May 20, 2002 at 5:04 am
I have set up a DTS to backup our live db.
However, it seems to error, giving foreign key constraint violations. My first thought was that since the master table was backed up, the tables with the foreign keys had been updated on the live server and so threw errors as it couldnt find the primary records in the master table.
I would have thought that a Transfer All Objects would take account of this possibility. Is there something I missed when I set it up, or another way to do the backup. Perhaps running it through a Stored Procedure.
I am in no way a DBA, just getting by as I can, so any help will be appreciated.
We are using SQL Server 7/
Thanks,
Andy HAll.
May 20, 2002 at 5:12 am
Transfer all objects does just that transfers them. They go in no particular order so yes if you have a constraint enabled that it breaks it fails. You can remove this problem with the foreing key contraint by disabling them on the receiving server so they do not iterfere. Then if you need to bring the other server up enabled the constraints. Or set up DTS to send in a specific order so the constraints match.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 20, 2002 at 5:50 am
Thanks for the reply, but is there no better way to do a full backup of a live db to a local db without altering the structure.
If I take the contraints off, then when I try to put them on again it will fail for the same reasons it is failing now. There is no order to do my tables so that they would not error. I really need an instance of the entire database to be backed up.
The only way I can think of getting around it is to take the live server offline while the backup takes place so that nothing can be added to the server.
May 20, 2002 at 6:18 am
Withut using replication or doing a file backup I have not seen a way to deal with DTS as a backup mechanism when their are constraints in place without disableing them. However you can restart the constraints with the NOCHECK option to prevent errors on existing data. (See SQL BOL)
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 20, 2002 at 6:42 am
I am open to any ways of backing up the database. If there is a better way than a DTS package, I am open to suggestions. To be honest, I have only "heard" of replication, and never tried a file backup. Would this be easier or more reliable?
May 20, 2002 at 7:00 am
Absolutely! I use the file backup and restore to another location nightly for several "replication" efforts and it works great. Simply backup the database to disk on your source server and restore from your destination server pointing to the backup file you created on the source server. You can even specify a file via UNC path (\\servername\path\file).
Hope this helps.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
May 20, 2002 at 7:52 am
I prefer the file backup method as you get a copy of the database then do a restore to the other server. The only thing you need to keep in mind is any accounts that have access to this database need to be setup on the other server and when you get thru restoring the database so you do not have to make any changes to grant access you will need to use sp_change_user_login 'Auto-Fix' to get them properly mapped together. (See BOL for more info). Also with the file backup method you can backup the transaction log periodically to make backups faster during the day but ot have a more current copy available, but you will need to plan this out so you can leave the DB in a loading state between TL restores and when you need to go live recover the DB and fix the logons so you can be up quickly.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply