Re: Copy SQL Server Objects Fails for certain views

Hi Allan,

> 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'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@xxxxxxxxxxxxxxxxxx> wrote in message
news:d4c9a65237e998c7decb051d4258@xxxxxxxxxxxxxxxxxxxxxxx
> 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
>
>>> Redefine the package. Copy objects just uses an alphabetical order.
>>> If you define this to explicitly define which objects are moving,
>>>
>> 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@xxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:OJOVDuNDGHA.3876@xxxxxxxxxxxxxxxxxxxxxxx
>>
>>> 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@xxxxxxxxxxx> wrote in message
>>> news:ee1MIoJDGHA.3920@xxxxxxxxxxxxxxxxxxxxxxx
>>>
>>>> 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'
>>>>
>
>


.

Relevant Pages

  • Re: Insert Error: Column name or number of supplied values does no
    ... I did use a log file which gets the following: ... As to running interactively, if you don't mean using the database wizard, I ... > table structures are different between your prod and dev SQL Servers. ... > Can you run the DTS package interactively and see on which table you are ...
    (microsoft.public.sqlserver.dts)
  • Re: Advice sought on use of DTS [Long]
    ... it may help to know that you can disable triggers temporarily do ... You may want to set up a sql job to copy over the remote database (the ... Can you programmatically tell a DTS package which databases to update? ...
    (microsoft.public.sqlserver.dts)
  • Re: Reading UDL files in DTS package- not working!!
    ... If you have changed the database in the UDL this is not reflected in the ... transform task, so if the Source on the task is shown as ... > re-open and re-save the DTS package, ...
    (microsoft.public.sqlserver.dts)
  • Re: Help with dtsrun and dts package.
    ... I dropped the tables from the database and that took care of the table ... The SQL server 2000 being used to create the dts package is on a different ... the MSDE master database but the message is that you cannot restore a SQL ... Can I create a dts package without using SQL 2000 Enterprise manager? ...
    (microsoft.public.sqlserver.odbc)
  • Re: Copy database fails - failed to copy file .. OMWWIZC ..
    ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. ... I was using BACKUP and RESTORE when COPYING a database. ... A colleague attended a training course and the trainer strongly recommended not using BACKUP/RESTORE when MOVING a database and we assumed the same held true for COPYING a database. ... I can successfully copy dbtest from one instance to another on the prod> server. ...
    (microsoft.public.sqlserver.dts)