Quick and dirty Windows MySQL + IIS Backups
If you’re looking for a quick and dirty way to back up your MySQL databases and your IIS website files, look no further. Below are two batch scripts for backing up your MySQL databases via a MySQL dump and a quick script to copy and compress your IIS website files into a .zip file.
I personally have the scripts save the backups in a OneDrive folder which then uploads them to OneDrive for backup. Others may want to use something like SyncBack to upload them to an FTP server elsewhere etc.
You’ll need to install 7Zip onto the server for the IIS script to work – I’d recommend using the 64 bit version if your server is 64bit.
7Zip Downloads: http://www.7-zip.org/download.html
Save the below scripts as .bat files – you can run them with Windows’ built-in Task Scheduler/Scheduled Tasks (Taskschd.msc).
IIS Website Files backup:
MKDIR "F:\OneDrive\Server Backups\WEB2\IIS\%date:~-4,4%%date:~-10,2%%date:~-7,2%\" "C:\Program Files\7-Zip\7z.exe" a -tzip "F:\OneDrive\Server Backups\WEB2\IIS\%date:~-4,4%%date:~-10,2%%date:~-7,2%\%date:~-4,4%%date:~-10,2%%date:~-7,2%_%computername%.zip" "F:\Hosting Spaces\*" -mx5
This script creates a folder in your backup directory named with the date the backup occurs and then names the backup .zip date_hostname.zip (e.g; 20160712_WEB2.zip).
Replace the bits in red bold with the your backup directory and bits in green bold with the folder you store website files in.
MySQL Dump backup:
MKDIR "F:\OneDrive\Server Backups\WEB2\MYSQL\%date:~-4,4%%date:~-10,2%%date:~-7,2%\" SET dbuser=backupuser SET dbpass=backuppassword PUSHD "C:\ProgramData\MySQL\MySQL Server 5.5\data" FOR /D %%F IN (*) DO ( "C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe" -u %dbuser% -p%dbpass% -P 3306 %%F > "F:\OneDrive\Server Backups\WEB2\MYSQL\%date:~-4,4%%date:~-10,2%%date:~-7,2%\%date:~-4,4%%date:~-10,2%%date:~-7,2%_%%F.sql" )-
The MySQL dump script creates a dump of all your databases with the name date_databasename.sql (e.g; 20160724_jonblog.sql). It saves the dumps in a folder with the date the backup occurs.
Replace the bits in red bold with your backup directory and replace the bits in orange bold with a MySQL database user with backup privileges.