Backup a Linked MySQL DB through SQL Server Management Studio

Question

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?

Answer 1

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.

Answer 2

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.