0

I'm trying to find a way to get a dump of a database in SQL 2000 to move to MySQL 5.5 on an Ubuntu Server 12.04. The Linux box runs some ruby scripts to convert the SQL data into JSON for upload to a website on AWS for a client portal.

We're running SQL 2000 on Windows Server 2008 Standard (x86), which is a VM on a XenServer 6.0 host. The destination Ubuntu server is x64, also a VM on the same XenServer host. I know SQL2K is antiquated tech, but it is the newest version of SQL the proprietary software we run on can use. And yes - we're looking into an upgrade there; but that is not today.

My thought was to just run a DUMP command through the Query Analyzer, but I keep getting errors.

    USE <database_name>
    EXEC sp_addumpdevice 'disk', 'tempdumpdisk', 'c:\dump\dump.bak'
    GO
    DUMP DATABASE <database_name>
      TO DISK = 'tempdumpdisk'
    GO
    EXEC sp_dropdevice 'tempdumpdisk'

The errors I get:

Server: Msg 15247, Level 16, State 1, Procedure sp_addumpdevice, Line 27 User does not have permission to perform this action. Processed 98264 pages for database '', file '' on file 4. Processed 1 pages for database '', file '' on file 4. BACKUP DATABASE successfully processed 98265 pages in 22.056 seconds (36.497 MB/sec). Server: Msg 15247, Level 16, State 1, Procedure sp_dropdevice, Line 38 User does not have permission to perform this action.

I'm logged into the server as a domain admin, and connecting to the database as sysdba. I don't know where the permissions issue would be coming from.

I apologize if this is obvious to you all. I'm quite new to all this and have been tossed into the deep end as it were. I don't post to these forums (I search the heck out of them usually), but I'm desperate.

Thank you!!

jszobody
  • 28,495
  • 6
  • 61
  • 72
user3015494
  • 1
  • you won't be able to import the mssql x.bak into mysql take a look to these qlosed questions: http://stackoverflow.com/questions/2176728/sql-server-to-mysql-conversion-converting-the-sql-server-query-dump-to-mysql-d and http://stackoverflow.com/questions/129/how-to-export-data-from-sql-server-2005-to-mysql – bummi Nov 21 '13 at 16:47
  • OK. Thank you bummi! Giving ms2my a try right now; we'll see how this goes. – user3015494 Nov 21 '13 at 19:18

1 Answers1

0

This sounds like a two-step process: first build the empty database structure, and then import the data.

You should be able to script out the database structure through the management GUI (now called SQL Server Management Studio, formerly Enterprise Manager). The CREATE TABLE etc statements should be capable of being modified to work with MySQL.

Then you can manually export the tables to flat files using the Export feature. Obviously this will be more practical if you have 20 tables than if you have 200 tables. For automation, SQL Server 2000 used DTS packages (now replaced with SSIS packages). You might want to script a flat file export that puts each record into an INSERT statement, to ease the import into MySQL.

The order of the INSERTS will need to depend on relationships between the tables. E.g., the child records pointed to by an FK will have to be inserted before the parent records.

(Beware: if you are thinking of moving to any more recent version of SQL Server, don't sink a lot of effort into DTS package development. You will have to recreate everything from scratch for SSIS, which does not resemble DTS at all.)

criticalfix
  • 2,870
  • 1
  • 19
  • 32
  • Wow. That may be out of my league to do. I'm just a junior sysadmin. I think I found a way to work this through MySQL Workbench the way I need it to. Don't know if I will be able to automate that, but I'm trying to chunk this elephant one dependency at a time. Thank you! – user3015494 Nov 21 '13 at 23:57