| Groups | Blog | Home |
sql server dts : SqlServer 2005 Transfer Flame
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.
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.aspx?scid=fh;EN-US;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 [quoted text, click to view] On Fri, 01 Dec 2006 10:42:42 GMT, changliw@online.microsoft.com (Charles Wang[MSFT]) wrote:
>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.aspx?scid=fh;EN-US;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,
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 [quoted text, click to view] On Mon, 04 Dec 2006 08:51:54 GMT, changliw@online.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/SQLServer/feedback/SearchResults.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/details.aspx?FamilyId=D2DA6579-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.
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/SQLServer/feedback/SearchResults.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/details.aspx?FamilyId=D2DA6579-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. ======================================================
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 [quoted text, click to view] On Tue, 05 Dec 2006 02:14:13 GMT, changliw@online.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
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
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. [quoted text, click to view] "Mark Olbert" wrote:
> 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
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |