Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a MySQL database that I'm able to query as a Linked Server through SQL Server Mgt Studio. I'm wondering if it's possible to create a backup task in Studio that will backup the MySQL DB, so that I could schedule automated backups. Is this possible?

share|improve this question

2 Answers 2

Personally, I would just write a batch file that simply runs the myslqdump utility and schedule that for automation rather than trying to do it through SQL server. Don't try and turn a screw with a hammer. :)

mysqldump --opt -u root -ppassword -h host > fulldb.sql

or

mysqldump --opt -u user -ppassword -h host database > database.sql

...or any of the other options you need in your particular circumstance (--opt just used as an example). All this is assuming you're not already doing replication or want hot copy backups or anything else that's more intensive, just the basic nightly dump of the bits.

share|improve this answer
    
That does seem to be both the obvious and sensible approach. +1 –  John Gardeniers Feb 11 '10 at 7:12

I very much doubt it, I don't have my SQL Internals book to hand but I would expect that the API used for backup isn't supported over the OLE DB/ODBC API used to connect to MySQL.

My best advice would be to use a SQL Agent job to copy (backup!) the linked table to a local SQL table and then utilise a SQL backup of that.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.