I am in the process of consolidating 17 SQL servers into 3 (yes they
extremely under-utilized). I am trying to use the Copy Database Wizard to do
this, mostly because there are a couple hundred databases that need to be
moved and it would be very tedious to do each one via backup and restore or
detach and attach. I am having some issues with moving the logins but I can
deal with that. My biggest problem is one of my target servers doesn't seem
to want to accept anything from the CDW. When attempting to copy from Server
A to Server B, it just locks up with no error message, no status screen,
nothing. I can copy from A to C, or even B to C, but B just can't be the
target for some reason. Is there a setting or configuration that I'm
missing? Any help would be greatly appreciated. Thanks in advance.

Ryan

Here is an article that list common "Copy DB Wizard" errors. I don't know
if yours is listed or not.
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues

Here are some other associated links that you may find useful:

http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/featu...le.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server


Personally I have never had anything but trouble with the wizard and found
that scripting the backup & restore or attach was much easier, faster and
cleaner.

--
Andrew J. Kelly SQL MVP


"Ryan" <Ryan (AT) discussions (DOT) microsoft.com> wrote

Even though you have a few dbs to back up I would still be using BACKUP RESTORE

You can generate the syntax to do this

SELECT 'BACKUP DATABASE ' + name + ' TO DISK = '<location>'
FROM master.dbo.Sysdatabases

You can then generate a script to do the restores as well.

I dislike the CDW as it has some quirks as you are experiencing

"Ryan" wrote:

Thanks Allan. Does the Backup and Restore take all of the logins, jobs, etc
with it?

"Allan Mitchell" wrote:

Thanks Andrew. I've seen you post this response before and have read through
all of the links, but none say anything about it just locking up. I can
certainly use the backup and restore or detach and attach methods, but I'd
still like to use DTS to move the logins, jobs, etc. I'm thinking it may be
a permission issue on my target server but I can't pin point it. It looks
like I have the exact same permissions on all of my servers.

"Andrew J. Kelly" wrote:

Is the account you are running DTS under a domain account and does it have
sa rights to the target server? Beyond that I don't know what to tell you
as the CDW has not been reliable enough for me to use so I just plain
don't<g>. Have you tried the other method in this KB?

http://support.microsoft.com/default...b;en-us;246133


--
Andrew J. Kelly SQL MVP


"Ryan" <Ryan (AT) discussions (DOT) microsoft.com> wrote

Logins no
Users yes (see Andrew's post)
Jobs - these are held in MSDB but you can script the jobs out by server.


"Ryan" <Ryan (AT) discussions (DOT) microsoft.com> wrote