Today, I wasted six hours trying every way I could think of to transfer data between a remote SqlServer2000 server and a local
SqlServer2005 server.

Prior to "upgrading" to this ridiculously poorly designed piece of trash known as SqlServer2005 that process would've taken about 3
minutes, most of which would be involved in the actual download of data. You see, there was this little thing in Enterprise Manager
known as the DTS Transfer Wizard that, by checking a handful of options, would quickly and reliably shuttle data back and forth
between two instances of SqlServer2000.

However, some mental defective, or group of mental defectives, up in Redmond thought it would be neat if they totally changed the
server management tools for SqlServer2005. This would allow them to add all sorts of wonderfully cool new features that I'm sure
gave them all wet dreams.

Too bad that along the way they eliminated the one workhorse capability that I (and, based on what I've read on the web, many
others) relied on to do actual work: the Transfer wizard. But who cares about whether your customers can actually get their work
done? "Who needs customers, we're a monopoly!" must be the mantra on the Microsoft campus!

For the record, I have tried the Transfer Database Task, the Transfer Sql Server Objects method, and, in a fit of desperation, the
old Enterprise Manager. I also tried some import capability in the Management Studio. None of them worked. They all failed, for
different reasons. Transfer Database claimed it couldn't find the path on the target server -- which is really funny, since it
clearly found it when it had to >>delete<< the target database files-- Transfer Objects kept puking on supposedly invalid foreign
key constraints -- all of which are perfectly valid, and, indeed, required for successful operation of the database -- Enterprise
Manager apparently doesn't work with SqlServer2005, and I forget why the Management Studio method failed. Oh, and unless you wonder
why it took me six hours to try four things, it's because I tried ever variant and iteration I could think of on each of them.

How an entire team of software architects, programmers, managers and testing people could've fracked up as badly as this is beyond
me. This level of crap would be mindblowing from some smaller startup, let alone the largest software development company in the
world.

It will be a cold day in hell before I ever purchase another "upgrade" of server software from Microsoft.

- Mark

Hi Mark,
I do understand your concerns with SQL Server 2005. Many new features are
introduced into SQL Server 2005 which are not familar to customers
especially for those who have used to SQL Server 2000.

Also, I would like your understanding that Microsoft is not a monopoly in
database field, and apparently Oracle and DB2 rank prior to SQL Server in
market. Microsoft SQL Server indeed have a lot of places which need to
study from peers.

As a support engineer of SQL Server, I will try my best to assit you if you
encounter any issues. For now, I understand that you were trying to
transfer the data from your SQL Server 2000 to SQL Server 2005.
I am not clear if you have some special needs on the data transfer. Per my
experiences, it is easy to transfer data from SQL Server 2000 to SQL Server
2005 by using SSIS package which you can design in SSBIDS (SQL Server
Business Intelligence Development Studio).
For simple data transfer, you can refer to the following steps:
1. Create a SQL Server Integration Service project in SSBIDS;
2. Switch to the Data Flow panel;
3. Drag a OLE DB Source to the panel;
4. Drag a OLE DB Destination to the panel;
5. Create two OLE DB connections for the OLE DB Source and OLE DB
Destination by the following:
Right click the panel of Connection Managers, click New OLE DB
Connection.
6. Assign the connections to the OLE DB Source and OLE DB Destination;
7. Select the table which need to be transferred in OLE DB Source Editor;
8. Drag the output line to the OLE DB Destination;
9. Double click the OLE DB Destination and set the mappings between the
source and the destination.
Then you can execute the task.
Of cource, this is just a very simple scenario and only used to transfer a
table or a view.

To let me better understand your issue, could you please tell me more
detailed on your real requirements of your scenario?
I will be more than happy to establish a similar environment for
reproducing your issue.
I believe that we can find a resolution on this issue.

I would like to let you know that satisfying customer is our top goal and
sincerely I also hope that I can satisfy you on your issue.

Also, there are other ways to let Microsoft hear your voice, such as
Microsoft Customer Support Service ( CSS ) and online submitting feedbacks.
I would like to provide their links for your reference:
1. CSS ( telephone )
To obtain the phone numbers for specific technology request please
take a look at the web site listed below.
http://support.microsoft.com/default...S;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
2. Online feedbacks
https://connect.microsoft.com/sql

Please feel free to let me know if you have any other questions or
concerns. It is always my pleasure to be of assistance.

Charles Wang
Microsoft Online Community Support

================================================== ====
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Charles,

I appreciate your prompt reply. The specific approach you described is of no use to me, as I am looking to transfer the entire
database (excluding the users, logins and roles) between the two servers. While the database is not large -- it has about 25 tables,
50 sprocs/udfs -- specifying the transfer on a table-by-table basis would be absurd. For one thing, the relatively complex set of
relations among the tables would make quite difficult (I know this because it's one of the things I tried in desperation, and gave
up in frustration). Not to mention that that wouldn't address the sprocs and udfs.

There has to be a better way. I want something similar to the old DTS wizard, which handled this kind of task simply and reliably
(as an aside, I made a rough estimate of the time it would take to transfer the database using the approach you outlined compared to
the old DTS wizard -- my estimate is that it would take about 10 times as long, not counting the learning curve -- this is
progress??). I see no reason why I should have to do a bunch of dataflow programming just to say "move this database from server A
to server B", particularly when that capability existed in an earlier version of the product.

As I am coming up on a deadline for testing a modification that I must put into production, I would like to move this discussion to
someplace where we can interact more than once a day (I have slightly less than a week). However, I am not interested in using a
support incident to cover the problem, because it looks like I'm dealing with a basic flaw in SqlServer2005. How do we go about
resolving this? Personal anger over the way Microsoft has mishandled this upgrade aside, I don't want to spend the next week on a
tirade about the company's failure -- I want to keep >>my<< customer happy, and let you guys spend your time fixing the shortcomings
in the product.

- Mark



On Fri, 01 Dec 2006 10:42:42 GMT, changliw (AT) online (DOT) microsoft.com (Charles Wang[MSFT]) wrote:

Hi Mark,
Because I did not totally understand your issue, I provide a simple sample
in my first reply which is indeed for a table or a view.
Now I understand that you would like to transfer a database between your
SQL Server 2000 and SQL Server 2005.
If you want to transfer your SQL Server 2000 database to your SQL Server
2005, you can use Copy Database Wizard in your SQL Server 2005 Management
Studio. It is easy and convenient:
1. Open SQL Server 2005 Management Studio and log on with a system
administrator;
2. Right click any of the databases, select Tasks, select Copy Database...;
3. Click Next, input your source server name, click Next, input your
destination server name, click Next;
5. Select the first option "Use the detach and attach method" and select
the option "If a failure occurs, reattach the source database", then click
Next;
6. In the Copy column, select the database that you want to transfer, click
Next, select either of the two options, then click Next;
7. Add the "Stored procedures from master database" and "User-defined error
messages" to the right list and click Next; (Please do not select the SQL
Server Agent Jobs due to a product issue not fixed in the current release).
8. Click Next, click next, and click Finish.

If you have SQL Server Jobs in your SQL Server 2000 database, I recommend
that you export the script of your jobs and then recreat them in your SQL
Server 2005 database. I must acknowledge that the utility of Copy Database
Wizard has many product issues now, and most of them have been submited to
Microsoft as feedbacks:
http://connect.microsoft.com/SQLServ...aspx?SearchQue
ry=%22copy+database%22
I believe that SQL Server 2005 SP2 will fix a great deal of product issues
that customers are very concerned with.

If you would like to transfer a SQL Server 2005 database to your SQL Server
2000, unfortunately by now SQL Server 2005 has not provide such tools to
directly copy the database into SQL Server 2000. I am afraid that a user
need to write an application to do this by himself now.

I believe that the SQL team must be aware of this deficiency and the next
release SP2 may include this function.

You may also try installing the SQL Server 2005 SP2 CTP version:
http://www.microsoft.com/downloads/d...579-D49C-4B25-
8F8A-79D14145500D&displaylang=en

If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.

Charles Wang
Microsoft Online Community Support

================================================== ====
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Charles,

Sorry, that won't work (I'd tried it before, but I tried it again just now following your instructions).

I can't use the attach and detach method because I don't have sufficient rights on the source databse (it's on a shared hosting
system, so they naturally don't want the risk of customers mucking around with each other's databases). The attach and detach option
is greyed out (an info message about the rights problem shows up in the bottom of the wizard page).

When I try to use the "Use the Sql Management Object method" instead, it fails with the error that the database owner user "is not a
valid user in database 4restore (error 916)". I'm not sure what this means, but it clearly keeps the copy from working.

So I'm still left with an inability to copy my database. What do I do next? Or do I give up on SqlServer2005 as a bad deal?

- Mark

On Mon, 04 Dec 2006 08:51:54 GMT, changliw (AT) online (DOT) microsoft.com (Charles Wang[MSFT]) wrote:

Hi Mark,
Yes, the second option "Use the Sql Management Object method" in Copy
Database Wizard indeed cannot work fine in the current version.

Since detach/attach methods were not appropriate for your situation, I am
afraid that you need to use the original method backup/restore to transfer
your SQL Server 2000 database to SQL Server 2005.
1. In your SQL Server 2000, run the statement like this:
BACKUP DATABASE DBTEST TO DISK='D:\TempData\dbtest.bak'
2. Copy the backup file to your SQL Server 2005 machine, then restore the
database like this:
RESTORE DATABASE DBTEST FROM DISK='D:\TempData\dbtest.bak'
WITH MOVE 'dbtest' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\dbtest.mdf',
MOVE 'dbtest_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\dbtest_log.ldf'

If you do not know the logical device name (such as 'dbtest' and
'dbtest_log') of your database files, you can run the statement like the
following to check:
RESTORE FILELISTONLY FROM DISK = 'D:\TempData\dbtest.bak'

Please feel free to let me know if you have any other questions.

Sincerely yours,
Charles Wang
Microsoft Online Community Support

Questions? Well, besides the one I raised before -- why Microsoft released this poorly-designed SqlServer2005 software in the first
place -- no, I don't have any.

Backup and restore won't work for me. I don't have rights to create a backup file on the host server.

Charles, thank you for your attempts to help, but I am really, really, really annoyed at your company for putting me through this.
It's inexcusable.

Please pass along my anger and disappointment to the SqlServer development and management teams. One or both of them should be
ashamed of themselves.

- Mark


On Tue, 05 Dec 2006 02:14:13 GMT, changliw (AT) online (DOT) microsoft.com (Charles Wang[MSFT]) wrote:

Hi Mark,
I have submited your concerns to SQL team and I do hope that they can
improve the product in the next release. Since your privilege on your
database is limited, I am afraid that you may consider to write an
applicatin to transfer the database or you may use the way transferring the
tables one by one though it seems a bit clumsy. For the stored procedures,
you can generate SQL Script in Enterprise Manager and then run it in SQL
Server 2005 Management Studio.

I am sorry that the first release of SQL Server 2005 product brings you
many inconvenience and that we could not provide better resolution on your
issue.

If you are very concerned with this issue, you can contact CSS and consult
if they can develop a seperate hotfix for you.

Sincerely yours,
Charles Wang
Microsoft Online Community Support

This is what you want to do to use the SQL 2005 Import/Export Data Wizard.

Open SQL Management Studio.
Right click on a user database.
Choose "Tasks -> Import Data"
This will bring you to a wizard very similar to the one on SQL 2000.

Hope this helps.

Dan Barkman
DBA
Redwood Trust, Inc.

"Mark Olbert" wrote: