I want to create a mysqldump file from a SQL Server database, I know its possible using MYSQL workBench, what i want is to make it completely automate. So the script runs on a daily or weekly basis and create a copy of MSSQL database and update mysql database with the copy. Is there any tool or script which can do that task.

  • Do you really need to export, then import/merge? I think you can do this directly using a linked server. – Jon Seigel Jul 10 '13 at 16:35

There is no built in solution that allows to create the equivalent of a MySQL dump file from a SQL Server database. (Instead SQL Server employs a backup solution that actually works and provides transactionally consistent backups, but that is only a side-note...)

You can use power-shell to write you own solution, but that is not going to be straight forward either. As a starting point you can look here: http://blogs.technet.com/b/heyscriptingguy/archive/2010/11/04/use-powershell-to-script-sql-database-objects.aspx

After you scripted the objects, you can use scripter.Options.ScriptData to generate the data.

However, instead of reinventing the wheel you might want to look at third party replication solutions. While I have not used it myself, http://www.symmetricds.org/ looks very promising for that purpose. It supports 12 database platforms including MySQL and SQL Server and offers automatic synchronization of databases with one or more subscribers. It comes in two versions, an open source version with GNU license and a fully supported "pro" version.

| improve this answer | |

Microsoft released a new tool last week called mssql-scripter that's akin to mysqldump. It's a Python-based, open source command line tool and you can find the official announcement here. Essentially, the scripter allows you to generate a T-SQL script for your database/database object as a .sql file. You can generate the file and then execute it. This might be a nice solution for you to automate your process. Here's a quick usage example to get you started:

$ pip install mssql-scripter
# script the database schema and data piped to a file.
$ mssql-scripter -S localhost -d AdventureWorks -U sa --schema-and-data  > ./adventureworks.sql

More usage examples are on our GitHub page here:https://github.com/Microsoft/sql-xplat-cli/blob/dev/doc/usage_guide.md

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy