My experience on copying a database. It seems so simple:

- Expanding the "server name" combobox takes waaaay too long, and it still
shows no-longer-existing servers, while it forgets whatever existing server
I ever typed in there.

Next step...

- Whatever happened to "Copy SQL Server objects"? I see no way to copy UDFs
SPs, Triggers, you name it. Do I have to do that manually?!

Alright, lets copy tables and views. Next step.

Yes, all tables. Next step. Execute immediately? Duh, of course. Next step.

Ah, I must click Finish. So we're done already??

Next step. Guess that "Finish" button was supposed to be a "Next" button.
It's copying...

- Hey MS guys, how about some progress bar? I really like to know how long
it'll take. It doesn't take 10 minutes to query the number of rows in each
table and figure out an ETA. You can do that in the next SP, if you ask me.

- "Execute transfer package for <tablename>" is not really a helpful
description of what is really happening there. It should probably be
"Execute CREATE TABLE statement" or something like that.

Ok, so I'm done. Yeah, I wish. Now I need to copy all other objects. But
first, let's check what has been copied. Thought so:

Primary keys: GONE
Indexes: GONE
Triggers: GONE
Relationships: NOT COPIED
Stored procedures: NOT COPIED
User-defined functions: NOT COPIED
Full-text catalogs: NOT COPIED

SPs and UDFs are easily copied manually. As for the rest... It's gonna be a
long night. Thanks again, Microsoft, for busting out a feature that actually
worked perfectly in the good ol' Enterprise Manager.


Or, am I missing something?

Martijn Saly (mart***@thany.org) writes:
I will have to admit that I am bit lost here. From your comment at the
end about Enterprise Manager, I conclude that you are on SQL 2005.

On SQL 2005, to copy a database, right-click the database in Object
Browser, and from the Tasks submenu, select Copy Database at the bottom.

There are two main options, detach/attach and the SMO method. The first
method is snap, but presumes that one of the involved SQL Server can
access the files on the other server. (I think it runs from the target
server, but I have not tried.)

Thus in many cases, you will wind up with the SMO method, which will
script the database, run the script on the other end, and copy the data as
well. The problem with the SMO method is that it is essentially still
in beta. That is, there are plenty of bugs. You can search for
"Copy Database Wizard" or CDW on
http://lab.msdn.microsoft.com/ProductFeedback/
to see what is known at this point.

You might have tried Export Data from the Tasks menu. I have not looked
into what that one does. I had more than enough with testing CDW in
the beta programme. In real life, I don't use any of this stuff, as I
have all code under version control.

Erland Sommarskog wrote:
Show quoteHide quoteYes well, that's not my point. The point is that they removed a feature that
I used every time I had to copy a database from live environment to
development environment (or the other way around).

I don't think they tested it at all. All the default options (as if there
are any) produces an error:

The specified @subsystem_name ('SSIS') does not exist. (Microsoft SQL
Server, Error: 14262)

Again, an unneccesary annoyance.

Export Data is exactly what needs to be improved rigorously!

And code is under version control, but version control cannot copy databases...

Martijn Saly (mart***@thany.org) writes:
SSIS = SQL Server Integration Services, the predecessror to DTS. You need to
install Integration Services for Export Data and Copy Database to work.

If you want to copy more than data, I believe that you have to use
the Copy Database Wizard.

No, but with well-devloped methods, you can build an empty database
from scratch. Copying the data is easily done by running a SELECT
over sys.tables to generate BCP commands.

SQL 2005 Flat File source (in Import wizard) missing
What's the best way to keep a live db and an archive db?
run Profiler on a specific SP/Query/Function
Need Help with "System.Data.SqlClient.Sqlerror: Cannot open backup device (DLT tape) ...
Displaying parms in Profiler when using DSN