We've got one article on the site already about copying databases (Copying
a Database from Server to Server) but one technique it left out (I think it
was written for SQL 7) was using the Copy Database Wizard. I'm going to do a
brief overview of how to use it...hey, it IS a wizard you know...and point out a
couple things you might find interesting as we go.
Before you even start, remember this - you have to be able to put the
database in single user mode for the copy (or move) to succeed. The wizard
doesn't give you the option to kill any existing connections. That said, here we
The copy wizard is available from the databases node in Enterprise Manager.
This makes sense since if you could access it from the db you're trying to copy
you'd have a connection open! Next step is basic.
Here is where it starts to get interesting. You can copy or move one or more
databases, or even a combination of the two. The wizard does give you some
helpful info about naming conflicts, unfortunately it doesn't give you a way to
change the name on the fly. If you want to copy Northwind to the second server,
you would first have to rename it on one of the two servers, or drop it from the
second server. Not a huge deal, but an option to overwrite or rename would be
Here you get the option to change where the files go on the second server,
see the following image for how that part works.
Here's another place where it gets interesting. The default here is all
checkboxes selected. In my experience that is rarely what I want, normally I
just want the database and any associated logins.
You get the option to schedule it for later. This is handy if you're not able
to put the db in single user mode during the day and want to get the copy done
at night. The caveat from the beginning applies - if one user leaves a
connection open to the db the copy will fail! You can always edit the package or
add a step to the job that kills any existing connections first. The other thing
is that if you want to run the copy on a recurring basis you'll have to add a
step that drops the db from the destination server or the copy will fail
As the task runs (and you click more info - the "less info" button
below) you can see the flow a little more clearly. The detach, copy, attach is
pretty clear, but what about the resolving logins?
If you profile the connection and look for statements related to logins, one
you may find is something like this:
|-- 'sp_addlogin' was found in the text of this event.
-- The text has been replaced with this comment for security reasons.
Not bad. The login gets added to the destination server. A couple rows down
from that you'll find something like this:
set CopyDBTest..sysusers.sid = 0xB4ABE04A812B8243A2F02C16CCD5B507 from
CopyDBTest..sysusers where CopyDBTest..sysusers.sid = 0xFEF4701DA6DFEE4F917442C6FD830470
In this case the login already existed on the destination server, but without
the extra step of sync'ing the SID the user in the database would be orphaned.
This is something you normally fix by running sp_change_users_login. For more
info on orphaned users see a great article by Neil
Boyle and a follow
up article I wrote. So at this point the db now exists on the second server
and the users are accessible. We're done. Right?
Well, what about passwords? For NT logins there is nothing to worry about,
but for SQL logins....does the wizard update the password on the second server?
No. That's not entirely bad, but it's less than helpful too. An option to set
the password or to leave as is would be nice. Remember this only happens if the
login already existed on the second server.
Still with me?
The wizard also offers the move option. This works almost exactly the same,
the only difference being at the end of the wizard the original db doesn't get
re-attached to the source server. It gets deleted, right? No, it errs on the
safe side and leaves both mdf and ldf sitting there....forever...until you
decide to delete them manually. Not the worst behavior, but again, I can see
where a user might expect a different behavior!
Another...interesting...behavior is that it won't copy system databases. This
makes sense for master of course, since the detach/reattach method doesn't work
for it, and I'll agree that copying tempdb even if you could seems pointless,
but what about msdb and model? Both of those can be potentially useful on
another server and there is nothing stopping you from using detach/attach
manually, just not through the wizard.
Wait, there's one more! You can't copy replicated databases. This isn't just
a restriction of the wizard since you can't detach a replicated db without
removing replication first.
Comments? Love the wizard or hate it? I generally respond to comments the