Backup IIS 7.5 Site and MySQL 5.5 Database using Batch File on Windows 2008

BackupIIS75SiteMySQLDatabaseBatchFileWindows2008

Unfortunately as Windows based operating systems become easier and easier to pickup and use with their shiny graphics and user friendly design it seems the art of scripting or batch coding is becoming rarer.  I thought I’d take the time to share with you a simple script I’ve previously written to be able to backup an IIS 7.5 website, it’s MySQL database and then compress it ready for download.

This script is based on the following environment:

– Windows 2008
– IIS 7.5
– MySQL 5.5
– RoboCopy
– 7Zip

The script runs in two parts.  The first is edited with the necessary variables to state the Site Name, it’s Database Name and finally the File Name used for the .zip archive.

This assumes that all sites are installed in x:\webrootfolder\sitename.com and the scripts themselves are stored in c:\batchfiles

This is the first script (GoBackups.bat make sure to edit the paths / database credentials etc) (double click to select all for copy/paste):

@echo off

REM Put Your Websites in Here
set BACKUPSITENAME=website1.com
set BACKUPDBNAME=website1_db
set BACKUPZIPNAME=website1
call c:\batchfiles\backuptasks.bat

set BACKUPSITENAME=website2.com
set BACKUPDBNAME=website2_db
set BACKUPZIPNAME=website2
call c:\batchfiles\backuptasks.bat

set BACKUPSITENAME=website3.com
set BACKUPDBNAME=website3_db
set BACKUPZIPNAME=website3
call c:\batchfiles\backuptasks.bat

REM Do a MYSQL Dump of the Main MYSQL DB
"C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump" mysql -uroot -p!Adm1nsql! > c:\inetpub\backups\%Date:~-10,2%-%Date:~-7,2%-%Date:~-4,4%\MYSQL.sql

REM ZIP the Main MYSQL Dump
"C:\Program Files (x86)\7-Zip\7z" a c:\inetpub\backups\%Date:~-10,2%-%Date:~-7,2%-%Date:~-4,4%\%Date:~-10,2%-%Date:~-7,2%-%Date:~-4,4%-mysql.zip c:\inetpub\backups\%Date:~-10,2%-%Date:~-7,2%-%Date:~-4,4%\mysql.sql

REM Tidy Up Afterwards
DEL c:\inetpub\backups\%Date:~-10,2%-%Date:~-7,2%-%Date:~-4,4%\MYSQL.sql /Q
robocopy c:\inetpub\backups c:\temp\delete /e /MOVE /MINAGE:30
rmdir c:\temp\delete /s /q

REM Clear Variable Strings
set BACKUPSITENAME=
set BACKUPDBNAME=
set BACKUPZIPNAME=

exit

The script basically sets three variables BACKUPSITENAME, BACKUPDBNAME and BACKUPZIPNAME.  It then calls the main batch file.

This is the main batch file (backuptasks.bat) (double click to select all for copy/paste):

REM This file runs once for each site listed in the GoBackups.bat file

REM Robocopy the Files
robocopy x:\webrootfolder\%BACKUPSITENAME% x:\webrootfolder\backups\%Date:~-10,2%-%Date:~-7,2%-%Date:~-4,4%\%BACKUPSITENAME% /MIR

REM SQL Dump
"C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe" %BACKUPDBNAME% -uYOURSQLACCOUNT -pYOURSQLPASSWORD > x:\webrootfolder\backups\%Date:~-10,2%-%Date:~-7,2%-%Date:~-4,4%\%BACKUPSITENAME%\%BACKUPDBNAME%.sql

REM Zip Everything Up
"C:\Program Files (x86)\7-Zip\7z" a x:\webrootfolder\backups\%Date:~-10,2%-%Date:~-7,2%-%Date:~-4,4%\%Date:~-10,2%-%Date:~-7,2%-%Date:~-4,4%-%BACKUPZIPNAME%.zip x:\webrootfolder\backups\%Date:~-10,2%-%Date:~-7,2%-%Date:~-4,4%\%BACKUPSITENAME%

REM Tidy Up
rd x:\webrootfolder\backups\%Date:~-10,2%-%Date:~-7,2%-%Date:~-4,4%\%BACKUPSITENAME% /s /q

The process covers several key steps:

  1. The site is copied using Robocopy to x:\webrootfolder\backups\DD-MM-YYYY\websitename.com
  2. The MySQL DB is then backed up using mysqldump and creates a .sql file inside the websitename.com folder
  3. 7Zip is then used to create a compressed .zip version of the websitename.com folder
  4. The original folder is then deleted leaving only the .zip file

When used with several sites all installed on the same web server, you will end up with the following file structure:

x:\webrootfolder\backups\dd-mm-yyyy\websitename1.zip
x:\webrootfolder\backups\dd-mm-yyyy\websitename2.zip

You can then use the built in Windows Task Scheduler to run the script weekly and produce well structured compressed backups ready to be FTP’d onto your workstation for safe keeping.

I hope you find this article useful.  If you need any assistance or have any questions please use the comments.

Author: BigGeek

Wayne is a self-proclaimed geek, IT Professional, father of two and husband who's still much closer to 40 than 50. When he's not busy in an otherwise hectic life he takes time to blog on all things that warrant an online opinion. Founded in 2012, ePINIONATED has been a guilty pleasure for Wayne, giving him an online voice in a very noisy Interweb – some of which might even be useful, maybe.

We would love to hear from you...