We're backing up our database by using DTS to transfer it to another server.
The DTS package is very simple, it consists of one 'Copy SQL Server Objects'
task. This task is set to drop all objects, recreate them, transfer the data
and then restore all constraints and relationships.

We're seeing sporadic problems in the final step. For example:

ALTER TABLE statement conflicted with with COLUMN FOREIGN KEY constraint
'R_114'...

The funny thing is, that there's no consistency to exactly which constraint
will fail. Sometimes it's on one table, sometimes on another.

I suspect that the problem is due to the fact that the database is being
accessed while the transfer is taking place. Thus after Table1 has been
transfered, while Table2 is being transfered, an update is applied to the
live database, placing new related entries into Table1 and Table4. When DTS
gets around to Table4, it picks up the new record and transfers it to the
target database. Thus we end up with an entry in Table4, but no related
entry in Table1. Thus the placement of the constraint into the target DB
fails.

This is my suspicion. Can anyone confirm that this is indeed the way DTS
will behave, that DTS 'Copy SQL Server Objects' task is NOT isolated from
database changes which are taking place while the task is executing?

Second, even if this is the default behavior, is there any way to set up
such isolation, so that the task runs agains a 'static snapshot' of the
source database as of the time the task begins to execute?

Third, if this is not possible, I'd be very grateful for any suggestions on
how we can accomplish this.

Thanks for your help,

- Joe Geretz -

Yep sounds about right to me.

If I had to transfer a whole DB from A --> B then I would be using
BACKUP and RESTORE.

Allan

"Joseph Geretz" <jgeretz (AT) nospam (DOT) com> wrote

Thanks Allan,

Can you tell me a bit more about the Backup option? Can I run backup while
users are accessing the database? If so, will backup be isolated from
database changes which are taking place while the backup is running? If
either of these is not the case, then I'll need to kick all users out of the
database in order to get a guaranteed consistent backup. If so, I may as
well kick all users out and run the DTS package.

Thanks for any advice which you can provide.

- Joe Geretz -

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote