4

I am looking to take and SQL Server Database and convert it into a format that would be easily importable into MySQL.

The plan is I run this process once a month, backing up the SQLServer database to a given location and then another process picks it up and imports it into MySQL.

I can do the transfer bit etc. I just have no idea how I would go about the backing up process.

Thanks for the help.

IMSoP
  • 89,526
  • 13
  • 117
  • 169
user1708468
  • 195
  • 10
  • 3
    You don't need a backup, you need to [export the database](http://stackoverflow.com/questions/9086880/t-sql-export-to-new-excel-file). – Mike Perrenoud Jul 23 '13 at 16:24
  • Since you have worded this as a "backup", it implies that you are planning to use this as a "fallback" of sorts. Is there a reason why the fallback cannot also use SQL Server? (I'm guessing licensing cost?) – IMSoP Jul 23 '13 at 18:28
  • There are a number of tools available for porting data between different database applications. You should Google those and find one that works for you. – Mike Brant Jul 23 '13 at 18:35

1 Answers1

2

One way to get good performance would be to write a SQL Server Integration Services (SSIS) package that connects to MySQL, truncates the relevant tables, and uses dataflows to copy the data from SQL Server tables into MySQL tables.

There isn't really a common backup format. You could have SQL Server write out a text file with a bunch of INSERT statements. Or you could write out plain text csv files for each table. Text conversions may become an issue. Excel is notorious for messing with your data based on what it thinks the data type should be.

Using SSIS is probably going to give you the cleanest inserts. It insists on clear data typing, code pages, etc, so you may find yourself writing derived column transformations in order to clean up small differences between SQL Server and MySQL. This may involve significant work, but the point is that it can be done in a consistent and predictable manner. And it should provide decent performance.

criticalfix
  • 2,870
  • 1
  • 19
  • 32