Creating a Windows database backup service I am creating a Windows service in C# that will run on our system to run a backup between 3am and 5am on our databases.

I have started by creating a method that will basically run the backup when SQL statements are passed into a backup method as below.

 private void BackupData()
        {
            try
            {
 RunBackupSQL("INSERT IGNORE INTO new table_backup SELECT * FROM current table", 
"Backup current table data");


However this is doing it 1 table at a time. I need to do this for 5 databases with over 56 different tables.

I was wondering if there is a way in which I can create a SQL command to take all tables in each database and do the backup so there would only be 5 SQL statements instead of 56?

The company I work for wants it done in this way based on the fact it was so difficult with their current backup system as it encrypted everything and it was difficult to extract the data again. I need to create a backup onto a different database completely so in effect snapshots of the current.

It needs to run once a day between 3am and 5am so in effect any changes made outside of these time frames will be picked up when the timer does start and it will overwrite the other database. I have 5 databases within the server I need to back up, so really there would be 5 databases I need to copy to. So if anything does go wrong with the current databases we have an exact replica that we can bring back across. It will create new dbs and then every time the current ones are changed it would then run the timer which will send across the changes to the new dbs and constantly do that.

It is MySQL. FYI I'm an apprentice at the moment, so it could be they want me to do it as a project form in C# using Visual Studio to get me into creating Windows services.
It seems you are trying to roll you own incremental backup script. The short answer is: 

Don't.



There are so many things to consider when doing backups and even more for incremental backups that it's very difficult to be done right. 
Using a simple INSERT IGNORE (which ignores many errors, not only duplicate key errors) is not going to work and give you incremental backups.  

Also note that a database has a few more things besides tables. There are view definitions and events that you may want to backup as well. And that tables are created and deleted and changed (columns added, dropped, modified, triggers added or dropped, etc).

Plus a backup has to be a consistent snapshot of the database, so whatever a script does has to be either a single transaction or while the database is not modified by any other session.

I suggest you start by reading the MySQL documentation on:

Backup and Recovery

It explains the various backup types and methods that can be used. There is no solution that covers all situations so the answer for your specific case depends on what exactly is your objective (normal backups, incremental backups) and many other details like the version, edition, the tables' engine (MyISAM, InnoDB, NBD, etc.), whether the database will be accessed during the backups or not, etc. 

You might find that a simple mysqldump of the 5 databases is all you need.