I was running the WP-DB-Backup plugin for this blog up until recently when it suddenly stopped working. I tried troubleshooting a little bit before I said “screw it, I’ll backup myself”. My solution was to write a script that will create a dump of my MySQL database, add it and my WordPress directory to a password-protected 7-Zip file, then email it to my GMail account and automatically archive it for safe keeping. This was all created in one batch file. The steps I took to perform these actions are as follows:
1. For the first few lines of the script, we’re going to set variables to set a date and time stamp on our archive, that way we’ll know exactly where it came from (and won’t overwrite anything if we choose to store the backup locally).
For the first lines of the script, write:
set T=%time:~0,5%
set UniqueDir=%date:/=-% %T::=-%
For the next line, we’re going to set the location of the backup file. Type out:
set backupfile="c:\path to backup\%UniqueDir%.7z"
2. Create the MySQL dump. To do this, add the line
"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqldump.exe" --user=MySQL-user-here --password=pw-here blog-db-name-here > c:\path to backup\blog_name.sql
3. Create the 7-Zip archive by archiving the SQL file we created along with the WordPress directory of your site. Start by downloading and installing 7-Zip. Then, add the line:
"C:\Program Files\7-Zip\7z.exe" a -t7z -pPASSWORD-HERE %backupfile% C:\inetpub\wwwroot\website_name\wordpress c:\path to backup\blog_name.sql
This creates the password protected archive named Date + Time.7z at the location you specify in the “backupfile” variable. The archive is protected with PASSWORD-HERE (and please note the lack of a space between the “-p” switch and the password. The archive includes the wordpress directory specified and the blog_name.sql file specified. You can add whatever other files you’d like after that, if desired.
4. Now it’s time to email the archive. To do this, we need to download the program “SendEmail”. This is a command line utility that will allow us to send email messages via the command line (or batch file, in this case). Once you’ve downloaded it, extract the contents of the zip file to c:\SendEmail. Now we’re going to add a line to our batch file that says:
"c:\sendemail\sendEmail.exe" -f [email protected] -t [email protected] -u Blog Backup for %UniqueDir% -m "See attached for backup" -s SMTP Server Here -xu SMTP User, if needed -xp SMTP PW, if needed -a %backupfile%
You can exclude the -xu and -xp switches if you don’t need to authenticate with your mail server to send messages.
5. This part is optional. To remove the backup files from the local machine, we add two lines to delete the SQL dump file and the 7-Zip archive we’ve created:
del c:\path to backup\blog_name.sql
del %backupfile%
That’s it! Now all that’s left to do is create a scheduled task using the Windows Task Scheduler to run this every evening (or however often you feel like). This will create a SQL dump, 7-Zip it with the WordPress directory, email it to a specified address, then delete the local copy of the backup. A full version of the script is below:
set T=%time:~0,5%
set UniqueDir=%date:/=-% %T::=-%
set backupfile="c:\path to backup\%UniqueDir%.7z"
"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqldump.exe" --user=MySQL-user-here --password=pw-here blog-db-name-here > c:\path to backup\blog_name.sql
"C:\Program Files\7-Zip\7z.exe" a -t7z -pPASSWORD-HERE %backupfile% C:\inetpub\wwwroot\website_name\wordpress c:\path to backup\blog_name.sql
"C:\sendemail\sendEmail.exe" -f [email protected] -t [email protected] -u Blog Backup for %UniqueDir% -m "See attached for backup" -s SMTP Server Here -xu SMTP User, if needed -xp SMTP PW, if needed -a %backupfile%
del c:\path to backup\blog_name.sql
del %backupfile%