Question

Locked

MySQL and SQL2005 Compatibility ?

By Phil ·
I am doing some work for a client who uses Sql2005 - and I need to be able to load his data and look at it.

I have loaded SQL Express but the database limit if too small for their data size.

1. Can I restore their MSSQL 2005 backup into Mysql and look at it with a 'front end' management tool

2. Could I then Link to the Database using Access / Excel Link Tables thru ODBC

Any answers greatfully received.

Phil

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

re: MySQL and SQL2005

by ThumbsUp2 In reply to MySQL and SQL2005 Compati ...

You can't "restore" a SQL2005 database backup to MySQL unless that backup is in CSV or SQL format. If it's simply a copy of the database, you'll need SQL2005 to open it. However, you can export out of SQL2005 and import to MySQL, then use your "front end" tools. Once you get it loaded into MySQL, you can export it as a CSV file and load that into Access / Excel, if that's your final destination.

The alternate, of course, is to do a live ODBC connection to the SQL2005 database directly and load up Access / Excel. But, that opens security concerns for your client. I wouldn't go that route if all you want to do is to "look" at the data.

Collapse -

Would this help you?.

MySQL import it into SQL Server 2005 Express.

This can be done pretty easily. one way is to use mysqldump and use the --compatible=mssql option. this will generate a single sql file with create table statements as well as insert statements for all data.

You will most likely have to edit the resulting sql file somewhat to fix any problems that you run into when running the script against sql server.

also, the mysqldump reference for version 5 of mysql is here http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html

Keep in mind the compatibility level of the sql server , as depending on the level , this has different requirements , for example using ";"

With complicated databases it may took a lot of time to convert mismatched data types one by one, editing the dump file, generated by mysqldump
May be its easier to use 3d party software like
http://www.sqlmanager.net/en/products/mssql

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90401

How to backup MSSQL 2005 Databases to Customers Domains

The first in the series is a script to backup users MSSQL 2005 databases to there ftp root folders. ie d:\domains\[customerDomain]\ in the folder \backups\mssql\ (by default anyway)

In order to use this script you will need to do the following

1. Copy the code below into a new file say c:\scripts\backupMSSQL2005.vbs

2. Now open the vbs script in notepad

3. Edit the settings in the top section

- The first set of settings are the details for where helm's Database is stored and the user credentials to access the database.
--- For ease of install use the WinAuthentication to access the databases.

- The second set of settings are for MSSQL 2005. You can get the MSSQL server name from 'Home > System Settings > Servers > [machine name]' where [machine name] is the server with the mssql 2005 service running on it. This must be used in the 'strMSSQLServername' value.

4. Set the value of domainsFolder to the location of the 'domains' folder where you have all users sites...

5. Save the changes

6. Now open up command prompt

7. Run CScript c:\scripts\backupMSSQL2005.vbs <enter> (change the location of the script to the location you used in step 1

8. If you get no errors go to step 9 if you do then ensure the settings you have entered are correct. I have at present included no error checking. PM me or reach me on MSN richard.myers@rjmsolutions.com if you have any errors and i will help you debug your error.

9. If you got no errors check in some users folders for the \backups\mssql\[databaseName].bak if you see some where you exect to you are done. If none show then some of your settings are incorrect and the result of the querry for databases for that domain therefore returned no results.

10. If this all worked ok then set up a scheduled task to run the script every night or however often you want to run it.

- To do this create a .bat file and enter the following

Code:

@echo off
CScript c:\scripts\backupMSSQL2005.vbs

Now save this as c:\scripts\RunMSSQLBackup.bat
- In Start > Control Panel > Scheduled Tasks create a scheduled task to run as often as you wish and ensure its set to run as a user with the nessecary user access to run the commands and you should be done
For Debugging issues contact:
richard.myers@rjmsolutions.com
Either PM or MSN.

Please post back if you have any more problems or questions.

Collapse -

Restore it no

by Tony Hopkinson In reply to MySQL and SQL2005 Compati ...

You'll have to use the transfer tool to export it to a format that MySQl can read.
You could do a full transfer but that would mean having them install MySQl to do it, and then back that up, which is perhaps unlikely.

You can get several front end management tools that work with MySql direct instead of messing about with excel or Access, but that's more comfort than anything else. If you can dodge using ODBC, you should.

As far as compatibility, you could have a bit of fun here and there. GUIDs and timestamps spring to mind.

You can download a trial SQL Server 2005.
You could also have a look at whether you need all the data and have them back up a slice of it.

HtHs

Back to Software Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums