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 -
[quoted text, click to view] "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
news:%23EyagFmVFHA.3588@TK2MSFTNGP10.phx.gbl...
> 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@nospam.com> wrote in message
> news:jgeretz@nospam.com:
>
>> 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 -
>
