How to Backup Your WordPress Database

The WP-DB-Backup plugin works great to automatically email you database backups, however the email part does not seem to work for many people.  From what I have gathered this stems from a file size upload setting in PHP.  This can be remedied in several ways, the most common of which is editing your php.ini file.  I’m not a PHP guy so I won’t go into all those methods, but feel free to research on your own.  If you find a fix please post it in the comments for others.

You don’t want to store your backups on your site because if the site data gets destroyed so do your backups.  You need to keep them somewhere else, but how can you automate this?  I have two alternatives.

SMTP Plugin

WP-DB-Backup works if you install an SMTP plugin to handle your blogs email instead of the native PHP mailer.  This plugin is great because you can do backups on the fly as well as scheduling them to your desired frequency.  However, once you cross that PHP default threshold (4mb I believe) you will magically stop receiving your database backup emails.  I love this plugin and I wanted to make it work, so I contacted the author.  If you’re thinking of contacting him then good luck, as I have never received a response.  Here is a workaround to get this plugin working for you.

Download, install, and configure WP-Mail-SMTP.  Once you have that setup the WP-DB-Backup plugin will start working again.  Well technically it never stops working, it’s just that the PHP mailer never sends the email.  Not only does WP-Mail-SMTP allow you to custom configure your SMTP server to something other than your ISP, it intercepts and handles anything coming to the PHP mailer method from WordPress.

Script a DB Download

Use DBC Backup to backup to your site.  This plugin allows you to define an export directory on your site where it will store the backups.  You can define the backup interval as well as how many backups to retain.  I suggest setting the plugin to remove all old backups so that only the newest one is available.  You will be pulling and storing them offsite anyway.  I have written a VBScript that you can schedule using windows task scheduler that will download all of the backup files in your export directory to your local machine or server.  I know it’s not PowerShell and shame on me, but I never seem to find the time to learn it as well as I know VBScript.  Make sure to schedule the task in task scheduler to run AFTER the time you have set the plugin to run and backup to your site.  I am a firm believer in code documentation through comments so I am not going to explain the code here.  You should be able to follow it easily with my comments.  If you have questions please email me or put a comment on this post as others may have the same question.

'==========================================================================
'
' NAME: DownloadFromWeb.vbs
'
' AUTHOR: Ryan J. Adams
' DATE  : 7/21/2010
'
' COMMENT: This script downloads a WordPress DB Backup file created by the
'			"DBC Backup" WP Plugin.
'			Remember that you are hard coding your username and password so
'			either change the script to take them as input parameters or encode the script
'			I personally prefer encoding the script and encrypting it is even better!
'
'==========================================================================

Dim strUser, strPass, strURL
Dim objFSO, objShell, objFile
Dim strCommand, strLocalDir, strRemoteDir

strUser = "MyUser" 'Replace MyUser with your username
strPass = "MyPass" 'Replace MyPass with your password
strURL = "MyURL" 'Replace MyURL with your FTP URL or IP Address
strLocalDir = "c:temp" 'Fully qualified path on your local system where you want the backup copied to
strRemoteDir = "httpdocs/wp-content/backup" 'directory on your FTP where the backup is located

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objShell = CreateObject("Wscript.Shell")

'Here we delete the text file input for the FTP command if it exists
'The script re-creates the file each time it is run
If objFSO.FileExists(strLocalDir & "FTPCommand.txt") Then
	objFSO.DeleteFile strLocalDir & "FTPCommand.txt"
End If

'Here we create the FTP input command text file and populate it with our options
Set objFile = objFSO.CreateTextFile(strLocalDir & "FTPCommand.txt")
objFile.WriteLine "open " & strURL
objFile.WriteLine strUser
objFile.WriteLine strPass
objFile.WriteLine "cd " & strRemoteDir
objFile.WriteLine "mget Backup*.*"
objFile.WriteLine "quit"
objFile.Close

strCommand = "%comspec% /c cd" & Mid(strLocalDir,4) & " && FTP.exe -i -s:" & strLocalDir & "FTPCommand.txt"
objShell.Run strCommand, 7, True

'Here we delete our input file so our passwords are not hanging out there in plain text
objFSO.DeleteFile strLocalDir & "FTPCommand.txt"

'Cleanup objects from memory
Set objFSO = Nothing
Set objShell = Nothing