Copy Database Wizard and DTS Transfer Database Task don't work

Hello,
I work with two SQL-Server 2000 computers. Both hav SP4 for SQL Server
2000. Both belong to the same domain and can see each other without
problems. A mutual registration of the other sql server in Enterprise
Manager is no problem.
One Server's operating system is win2003Server, the other one's is
Win2000Server.
In both servers the domain account I use for logging in and for
authentification purposes is a member of the server's local
administrators group. And the local administrators have sysadmin rights
in sql server 2000 via builtin\administrators.
After some tests I added my windows domain account explicitly as
sysadmin in both SQL-Servers.
The security mode is "mixed" (SQL-Login and Windows).
Trying to transfer a Test database with 1MB space fails. No matter
whether I use a Transfer Database Task or whether I use the Copy
Database Wizard (which also uses DTS in the background).
The Transfer Database Task needs FIVE minutes for the dialog window
"Transfer Database Properties" to reappear after clicking on the
tab-card [file locations].
Below the grid is a green check mark: Files are ready to be moved or
copied.".
Below that is a red X: Files on the source have the same name as on the
destination or there is not enough free disk space on the destination.
On both servers there is plenty of space (dozens of GB).
In the end I only get the error description "Unspecified error".
Doing the same with the Copy Database wizard" in the Grid beside the
..mdf file and the .ldf file shows the STATUS: OK
Below, again two error messages:
Name conflict: Type a new name into the Destination Files fields
Disk space: Change the destination or free disk space
At the same time is shown the space needed and available:
E: Available Space: 56956,00 MB, Required Space: 2,00 MB.
So I run the job in the wizard immediately and get the following
errors:
Step Error Source: Microsoft SQL-DMO (ODBC SQLState: 28000)
Step Error Description:[Microsoft][ODBC SQL Server Driver][SQL
Server]Login failed for user '(null)'. Reason: Not associated with a
trusted SQL Server connection.
Step Error code: 80004005
Step Error Help File:SQLDMO80.hlp
Step Error Help Context ID:700
Apparently DTS uses odbc and can not establish the connection despite
Sysadmin rights on both servers. I tried to specify the account sa of
sql server for authentication instead of using windows authentication
but that yields the same result.
Would someone know the solution?
Thank you.
Andreas.meyer@.dmc-group.de wrote:
> Hello,
> I work with two SQL-Server 2000 computers. Both hav SP4 for SQL Server
> 2000. Both belong to the same domain and can see each other without
> problems. A mutual registration of the other sql server in Enterprise
> Manager is no problem.
> One Server's operating system is win2003Server, the other one's is
> Win2000Server.
> In both servers the domain account I use for logging in and for
> authentification purposes is a member of the server's local
> administrators group. And the local administrators have sysadmin rights
> in sql server 2000 via builtin\administrators.
> After some tests I added my windows domain account explicitly as
> sysadmin in both SQL-Servers.
> The security mode is "mixed" (SQL-Login and Windows).
> Trying to transfer a Test database with 1MB space fails. No matter
> whether I use a Transfer Database Task or whether I use the Copy
> Database Wizard (which also uses DTS in the background).
> The Transfer Database Task needs FIVE minutes for the dialog window
> "Transfer Database Properties" to reappear after clicking on the
> tab-card [file locations].
> Below the grid is a green check mark: Files are ready to be moved or
> copied.".
> Below that is a red X: Files on the source have the same name as on the
> destination or there is not enough free disk space on the destination.
> On both servers there is plenty of space (dozens of GB).
> In the end I only get the error description "Unspecified error".
> Doing the same with the Copy Database wizard" in the Grid beside the
> .mdf file and the .ldf file shows the STATUS: OK
> Below, again two error messages:
> Name conflict: Type a new name into the Destination Files fields
> Disk space: Change the destination or free disk space
> At the same time is shown the space needed and available:
> E: Available Space: 56956,00 MB, Required Space: 2,00 MB.
> So I run the job in the wizard immediately and get the following
> errors:
> Step Error Source: Microsoft SQL-DMO (ODBC SQLState: 28000)
> Step Error Description:[Microsoft][ODBC SQL Server Driver][SQL
> Server]Login failed for user '(null)'. Reason: Not associated with a
> trusted SQL Server connection.
> Step Error code: 80004005
> Step Error Help File:SQLDMO80.hlp
> Step Error Help Context ID:700
> Apparently DTS uses odbc and can not establish the connection despite
> Sysadmin rights on both servers. I tried to specify the account sa of
> sql server for authentication instead of using windows authentication
> but that yields the same result.
> Would someone know the solution?
> Thank you.
>
Save yourself the headache and stop using these wizards for such a
simple task. Simply take a backup of the source database, then restore
that onto the destination server. The syntax for the BACKUP and RESTORE
commands is pretty simple, see Books Online for details.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Hi Tracy,
of course, I did already.
However, there is so much fuss being made of what sql server 2000 can
do.
Also it is so much stressed in the exams: and dare you you wouldn't
know what the copy database wizard or DTS is able to to and that to use
it is so much easier and surely the best solution when asked for "the
least administrative effort", etc.
Then you try with one of the simplest tasks and in practice it just
fails in an environment where it is truly hard that it wouldn't work.
Yet it doesn't.
Nothing left any more of all the glory...
This is annoying.
Then again, I would like to know the reason for the error message in
odbc.
My impression is: a lot is just marketing but not working, really.
Sincerely
Andreas
Tracy McKibben schrieb:

> Andreas.meyer@.dmc-group.de wrote:
> Save yourself the headache and stop using these wizards for such a
> simple task. Simply take a backup of the source database, then restore
> that onto the destination server. The syntax for the BACKUP and RESTORE
> commands is pretty simple, see Books Online for details.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com