Introduction
One of the most frequent questions on forums is how to back up MySql databases directly from a .NET application. Unfortunately, unlike Access, SqlLite etc., it is not really a case of just copying a file and renaming it, you really have to get down and dirty and basically recreate the SQL that creates all tables and inserts all data back into tables from scratch. There are applications that do this for you, obviously, such as SqlDump, mysqlhotcopy etc., but these require some knowledge of MySQLl and relational databases in general. Unfortunately, in my experience, 99% of my clients do not even know what a relational database is; they just want a computer program that can do their invoices and manage their stock with pretty graphs to look at. When it comes to backing up, they just want to press a button that says Back-up and everything is taken care of. The obvious answer to this would be to use Process.Start("mysqldump", filename), and hey presto, you have a full backup. Sadly, I have never had much success with this. Let me tell you. I tried, but it would always stuff up, especially when it is the client trying to do a back up with no one else around to recover the process for them. Then you have an irate client on your hands, telling you that you have the programming skills of a twit, who couldn't even make a backup system that worked. So I set out to make a bulletproof, fully managed, backup class that I could use in my .NET apps.
I just thought, having completed most of what I set out to accomplish, that I would share the results of that effort with everyone else out there who needs something like this. First off though, I need to stress that I am in no way a database expert, rather I am just a humble application developer who has built up a business building custom business applications that just happen to have a database for the back-end. While I have fully tested this successfully on my own databases, those databases tend to be small and located on the machine that is doing the backup. For the database experts out there, I would really appreciate your comments and criticisms to optimise this and correct things that I have done incorrectly. I also need to apologise in advance if the code lines are too long to read without scrolling. I have widescreen monitors, and I hate line continuation characters in my code (except maybe to stress SQL - maybe), so there.
Prerequisites
Other than the fact that you need access to a MySQL server with a database to play around with, and the .NET connector for MySQL which can be found here: MySql Connector for .NET, you will only need Visual Studio (I have used VS 2008) to run the project and test the backups.
Project design
Obviously, the first thing to do once you have established what the project is setting out to do, is to figure out what is necessary to accomplish those goals. When it comes to backing up a database schema, the only things to make a decision on are:
1. What is the optimal way to recreate a database from scratch?
Having done the research, mainly studying the way other people and organisations have accomplished this, I came to the conclusion that recreating the SQL for the CREATEs and INSERTs for each table would be the most obvious way to go, especially as this was in our original conclusion. We also needed to make a decision as to how far in recreating the database we needed to go. Would we need to backup Stored Procedures, Functions, and Views? What about recreating Indexes? And Foreign Keys?
I decided that it would be better to start off with the aim of being able to recreate any database, complete with Stored Procedures, Functions, Views, and Foreign Keys. As far as Indexes go, I would rely on MySQL itself to create Indexes on Unique, Primary, and Foreign Keys automatically when the tables are recreated on restoration. Any other Indexes would have to be created manually once the restoration is complete, by the SysAdmin.
Read more: Codeproject
One of the most frequent questions on forums is how to back up MySql databases directly from a .NET application. Unfortunately, unlike Access, SqlLite etc., it is not really a case of just copying a file and renaming it, you really have to get down and dirty and basically recreate the SQL that creates all tables and inserts all data back into tables from scratch. There are applications that do this for you, obviously, such as SqlDump, mysqlhotcopy etc., but these require some knowledge of MySQLl and relational databases in general. Unfortunately, in my experience, 99% of my clients do not even know what a relational database is; they just want a computer program that can do their invoices and manage their stock with pretty graphs to look at. When it comes to backing up, they just want to press a button that says Back-up and everything is taken care of. The obvious answer to this would be to use Process.Start("mysqldump", filename), and hey presto, you have a full backup. Sadly, I have never had much success with this. Let me tell you. I tried, but it would always stuff up, especially when it is the client trying to do a back up with no one else around to recover the process for them. Then you have an irate client on your hands, telling you that you have the programming skills of a twit, who couldn't even make a backup system that worked. So I set out to make a bulletproof, fully managed, backup class that I could use in my .NET apps.
I just thought, having completed most of what I set out to accomplish, that I would share the results of that effort with everyone else out there who needs something like this. First off though, I need to stress that I am in no way a database expert, rather I am just a humble application developer who has built up a business building custom business applications that just happen to have a database for the back-end. While I have fully tested this successfully on my own databases, those databases tend to be small and located on the machine that is doing the backup. For the database experts out there, I would really appreciate your comments and criticisms to optimise this and correct things that I have done incorrectly. I also need to apologise in advance if the code lines are too long to read without scrolling. I have widescreen monitors, and I hate line continuation characters in my code (except maybe to stress SQL - maybe), so there.
Prerequisites
Other than the fact that you need access to a MySQL server with a database to play around with, and the .NET connector for MySQL which can be found here: MySql Connector for .NET, you will only need Visual Studio (I have used VS 2008) to run the project and test the backups.
Project design
Obviously, the first thing to do once you have established what the project is setting out to do, is to figure out what is necessary to accomplish those goals. When it comes to backing up a database schema, the only things to make a decision on are:
1. What is the optimal way to recreate a database from scratch?
Having done the research, mainly studying the way other people and organisations have accomplished this, I came to the conclusion that recreating the SQL for the CREATEs and INSERTs for each table would be the most obvious way to go, especially as this was in our original conclusion. We also needed to make a decision as to how far in recreating the database we needed to go. Would we need to backup Stored Procedures, Functions, and Views? What about recreating Indexes? And Foreign Keys?
I decided that it would be better to start off with the aim of being able to recreate any database, complete with Stored Procedures, Functions, Views, and Foreign Keys. As far as Indexes go, I would rely on MySQL itself to create Indexes on Unique, Primary, and Foreign Keys automatically when the tables are recreated on restoration. Any other Indexes would have to be created manually once the restoration is complete, by the SysAdmin.
Read more: Codeproject