We have been using the 'Copy SQL Server Objects' with success for some time
to copy an entire database to another server. Recent changes to our database
infrastructure cause DTS to fail:
1) We have a check constraint which uses a user function (which refers to
the table on the check constraint
2) We have a view which refers to another view

DTS fails on both. DTS fails on the check constraint and stops. if I remove
the check constraint it will fail on the view. See errors below.

Any hints how to solve this would be appreciated.

Jonathan Orgel

Errors:
[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
'dbo.SRS_NumberRWSubjects'

[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
'VMSGRECIPIENTS2'

Redefine the package. Copy objects just uses an alphabetical order. If you
define this to explicitly define which objects are moving, you can specify
the order which will move the dependent objects first.

--
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com


"Jonathan Orgel" <Jonathan (AT) srssoft (DOT) com> wrote

Easier said than done. Our database is a constantly evolving conglomeration
of hundreds of objects. We don't relish having to constantly evolve the DTS
package as well. That's why we are using the more generic transfer
methodology to transfer the entire database, rather than identifying each
object specifically.

Also, it does not seem that objects are copied in alphabetic order, it seems
to have more to do with creation / last modification date. By making an
innocuous change to the object we are now able to complete the transfer. We
did not rename the object, yet we managed to change its transfer sequence.

Nice going Jonathan! :-)

- Joe Geretz -

"Michael Hotek" <mike (AT) solidqualitylearning (DOT) com> wrote

Hello Joseph,

I cannot help but think that if you are using the Transfer Objects task to
move a whole DB everytime that it will be a lot slower and error prone than
a simple BACKUP/RESTORE.

You can then run scripts on the restored database to add/remove users/logins
etc if you need to

Is this an option?


Allan

Hi Allan,

You're definitely on target as far as reliability is concerned. However, to
make this work for us in our application context we'd need to make
Backup/Restore as transparent to the customer as our DTS package currently
is. Our customers don't typically know a database from a monkey wrench. The
advantage to DTS is we set it up for them and it just runs (well, usually
:-) for them silently in the background. It seems to me that logistically,
Backup / Restore might be a bit more difficult to set up in this manner, but
if you have any suggestion on how to do this, I'm eager to hear.

Thanks!

- Joe Geretz -

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

Hello Joseph,

You can still script the whole process and you could even put the steps into
a DTS package if it helps to visualise the process. I would also look at
sp_attach_db and sp_detach_db

Allan