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?
Asked
Active
Viewed 1,118 times
2 Answers
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.
-
That does seem to be both the obvious and sensible approach. +1 – John Gardeniers Feb 11 '10 at 07:12
0
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.
Joel Mansford
- 985
- 1
- 5
- 13