SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Using the Copy Database Wizard

By Andy Warren,

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:

-- '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:

update CopyDBTest..sysusers 
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 same day!

Total article views: 10930 | Views in the last 30 days: 4
Related Articles

SQL Server: List Explicit Permission on Database for Login and Database Role

As part of security audit of login's, we have discussed earlier about the orphan users , listing se...


Error using Copy Database wizard

Im getting an error message when i use the copy database wizard


Auditing login and Database Information on servers

login and database info


Access to SQL Server: The Upsizing Wizard

SQL Server 2000 is the basis for the new Access database storage. However there are still many cases...


moving databases,logins

moving database,logins

sql server 7