Backup websites and MySQL databases on Windows Server

Question

I'm looking for a solution to backup our websites and MySQL databases on a Windows Server 2008. Our current host does a full weekly and daily differential backups, but I would like to maintain my own set of backups as an extra precaution.

We do have MySQL administrator to back up the databases. I also have 7zip installed to compress files on the server.

I'm thinking of doing a combination of backing up the databases using MySQL administrator, then zipping the files (database and websites) to a backup directory on the server, and then downloading all to our office server via FTP. I would also have a process to delete the backup zip files off the server as well.

I'm thinking of backing up the websites weekly and databases daily.

I was hoping there might be a program (free or paid) that already does this. Any suggestions?

Answer 1

One suggestion:

Since you are using a windows server, you might want to look into PowerShell.

I believe you have a solid list of all the various things you want to do, so a PowerShell script to automate/coordinate those things would be a good fit for your strategy.

My estimate is that if you've never used PowerShell, you could probably get a basis backup/download script working in about 1 day. And, since it's your own script, you could enhance as time permitted (to add the bells & whistles like deleting old backups, etc.)

Unfortunately, I don't have a specific starting point for you, but Google should probably do.

Answer 2

For backing up MySQL you should use mysqldump to create sql files of the databases, or even each separate table. Then include the dump files into the rest of what you compress with 7-Zip. Finally, use whatever means you have available to you to transfer the resulting archive file to its final destination. The whole lot is easily done with a batch file and Task Scheduler. Start by having a read of the mysqldump docs.

A much better solution would be to install rsync at each end and use that to transfer the files directly, rather than compressing them and transferring everything. Using rsync you will only transfer the changes. Alternatively, if you create a VPN between the machines you can use Robocopy to do likewise.