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

go:

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

nice!

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:

Not bad. The login gets added to the destination server. A couple rows down

from that you'll find something like this:

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

same day!