Re: Database Transfer Problem

#1

Most definitely you can take a backup with users in the DB.
SQL Server will back up the log after the data file to capture changes
made whilst the backup was taking place

Quote:

"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 in message
news:%23EyagFmVFHA.3588 (AT) TK2MSFTNGP10 (DOT) 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 (AT) nospam (DOT) com> wrote in message
news:jgeretz (AT) nospam (DOT) 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 -

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Database Transfer Problem

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: Database Transfer Problem - 05-11-2005 , 04:12 PM