Ryan Adams Blog

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

A couple of years ago I had a group that used a sharepoint web front end that created AD users on the fly in the backend. The problem is that people would either not use it again after the first login, or they would just signup again if they forgot their credentials. Although all of that COULD have been handled by the developers on the front end, it was not and my concern was AD. For the sake of a clean AD, additional replication overhead, and SOX compliancy, unused accounts needed to be removed. Of course let’s not forget the security implications.

It was the application owners’ responsibility to maintain the accounts, but without any AD knowledge they needed a dummied down way to clean up the accounts. At that time I was still learning VBScript and decided to kick it up a notch and write my script in an HTA. I swear my scripts are much cleaner these days. I’m not going to go over all the code, but please leave any comments if you have questions. After you copy the code and save it as an .HTA you will need to change the LDAP paths according to your AD, the maximum age for enabled accounts, and the maximum age for disabled accounts. The reason for querying whether the account is disabled or not is so you can do something like disable an account that is 30 days past and delete it if more than 60 days past. You should also know that I use the LastLogonTimeStamp attribute since it is replicated to all domain controllers. Conversely the LastLogon attribute is not replicated and will vary from DC to DC. For more on the workings of the LastLogonTimeStamp attribute and its replication frequency (14 days) see this TechNet article.

This HTA will not only show you your unused accounts, but will let you save it in a spreadsheet, selectively disable, enable, or delete the accounts, and let you save your changes to a spreadsheet.  Just for reference you can also find this script in the Microsoft Scripting Guys’ Script Repository HERE.  Don’t panic, I didn’t steal it; I submitted it to them to add to their community submitted scripts.

Awhile back I was tasked with designing and implementing a disaster recovery solution for one of our production application’s databases.  I decided to take a very simple approach for a couple of reasons.  I chose to simply create SQLAgent backup jobs on the production box that backup to a share on the DR box.  Why not use mirroring or log shipping? I took this route for the following reasons:

1. We have very large pipes between our data centers, so keeping the data in small chunks with mirroring or log shipping is not necessary.  Also the DBs are small.
2. This gives me point in time (full recovery model) backups in a remote location.  Another group handles server and application backups and the last time I needed a restore they “forgot” to setup the backups.
3. Security over the wire is not an issue since our security team is like a cast iron chastity belt.  Seriously….we got bought 5 years ago and they still block traffic from the company that bought us even though that company has opened its firewalls to us.  Hey guys we are all the same company, let’s play nice.
4. The required uptime for this application allows some leniency.  Besides, I can have the DBs restored long before the developer can change his code to point to the new location and DNS to replicate.

I’m a big believer in automation, because less is more.  I was also itching to write some VBScript, so I decided to script the restoration of the DBs.  This script makes some assumptions on how you design your backup job schedules so it will not work in every situation.  This script assumes that you take a full backup sometime in the AM and a differential sometime in the PM.  We are restoring these backups onto a different server than where the backups were taken.  This means we do not have local access to the SQL MSDB for backup information unless we remotely query the source server.  Since the backup files are local and SQL can’t tell us the difference between a full and differential backup (Please comment and correct me if you know a way) there is no need to query MSDB for any information anyway.  I bring this up because if we could query SQL to find the last full and differential backups, then it would not matter how we scheduled our backups and no assumptions would need to be made.   As far as I have ever known SQL only shows backups as one of two types, either a data file backup or a log file backup.  Since full and differentials are both data file backups there does not appear to be a way to differentiate between the two.  The last assumption is that you do not backup multiple databases in a single folder.  Things get messy if you do, so I suggest avoiding that as a general rule.   Here is where you can specify separate folders for each database while creating a maintenance plan within the “Backup Database Task”.

Backup Database Task

We start off declaring some variables and then initialize the ones that define the name of the DB we are restoring, the local drive letter where the backups are located, and the full path to the backup folder on that drive.  Now we create a connection to the local SQL server that will use the credentials under which the script is run.  We also create a connection to WMI and create a log file to record the name of each DB file that got restored.  Note that the log file will get overwritten every time the script is run.  You can change this to simply append, but make sure you also insert the date and time.  Alternately you could also build a log table and store it all in SQL.

Dim strComputer, strDrive, strPath, strDBName, colDBFilePath, colLogFilePath
Dim colDBName, strSQLRestore, colLogName, strSQLFixUser
strDBName = “MyRestoreDB”
‘This is the drive letter where your back files are located on the local machine
strDrive = “D:”
‘This is the path where your back files are located on the local machine
strPath = “\\MyBackupFolder\\” & strDBName & “\\” ‘WMI requires double backslashes in its path names
strComputer = “.”

‘=========================== SQL Connection =====================================================
Set objSQLConnection = CreateObject(“ADODB.Connection”)

objSQLConnection.Open _
    “Driver={SQL Server};Server=localhost;” & _
        “Trusted_Connection=No;database=master;”
‘================================================================================================

‘=========================== WMI Connection =====================================================
Set objWMIService = GetObject(“winmgmts:” _
    & “{impersonationLevel=impersonate}!\\” & strComputer & “\root\cimv2″)
‘================================================================================================   

‘=========================== Output Log Creation ================================================
Set objFSO = CreateObject(“Scripting.FileSystemObject”)
Set objTextFile = objFSO.CreateTextFile(“c:\Scripts\DBRestoreLog.txt”,True)
‘================================================================================================   

Now we need to get the file name and location where SQL tells us it is storing the DB, so we have that for the restore.  We’ll also need a list of all the backup “BAK” files in the backup directory to determine which full backup is the most recent and if there was a differential taken after it.  In order to do all that we query WMI for the list of files and store them in a recordset where we can sort them by their last modified date.  Since we know we do a full in the AM and a differential in the PM, we know if the last modified date of the most recent file is in the AM then all we have to restore is that full backup.  If the most recent backup file is in the PM then we know it was a differential and we have to restore the file before it since that will be the most recent full and then restore the differential.

‘=============================================================================================================================
‘FULL AND DIFFERENTIAL FILES
‘=============================================================================================================================

colDBFilePath = objSQLConnection.Execute(“Select filename from sys.sysaltfiles where dbid = db_id(‘” & strDBName & “‘) and fileid=’1′”)
colLogFilePath = objSQLConnection.Execute(“Select filename from sys.sysaltfiles where dbid = db_id(‘” & strDBName & “‘) and fileid=’2′”)
colDBName = objSQLConnection.Execute(“Select name from sys.sysaltfiles where dbid = db_id(‘” & strDBName & “‘) and fileid=’1′”)
colLogName = objSQLConnection.Execute(“Select name from sys.sysaltfiles where dbid = db_id(‘” & strDBName & “‘) and fileid=’2′”)

Set colFiles = objWMIService.execquery _
    (“Select * from CIM_DataFile where Path = ‘” & strPath & “‘” _
     & “And Drive = ‘” & strDrive & “‘ and Extension = ‘bak’”)

Set rsBackup = CreateObject(“ADODB.Recordset”)
rsBackup.Fields.Append “strFileName”, 200, 255
rsBackup.Fields.Append “strFileDate”, 7
rsBackup.Open

For Each objFile in colFiles
    rsBackup.AddNew
    rsBackup(“strFileName”) = objFile.Name
    rsBackup(“strFileDate”) = WMIDateStringToDate(objFile.lastmodified)
    rsBackup.Update
Next

rsBackup.Sort=”strfiledate”
rsBackup.MoveLast
If ucase(Right(rsBackup.Fields.Item(“strFileDate”),2)) = “AM” Then
 ’Restore Full
 objTextFile.WriteLine(rsBackup.Fields.Item(“strfilename”) & vbTab & rsBackup.Fields.Item(“strfiledate”) & VbCrLf)
 strStartDate = rsBackup.Fields.Item(“strfiledate”)
 strSQLRestore = “RESTORE DATABASE [" & strDBName & "] FROM  DISK = N’” & rsBackup.Fields.Item(“strfilename”) & _
 ”‘ WITH  FILE = 1,  MOVE N’” & colDBName(0) & “‘ TO N’” & colDBFilePath(0) & “‘,  MOVE N’” & colLogName(0) & “‘ TO N’” & colLogFilePath(0) & “‘,  NORECOVERY,  REPLACE”
 objSQLConnection.Execute(strSQLRestore)
Else
 ’Restore Full
 rsBackup.MovePrevious
 objTextFile.WriteLine(rsBackup.Fields.Item(“strfilename”) & vbTab & rsBackup.Fields.Item(“strfiledate”))
 strSQLRestore = “RESTORE DATABASE [" & strDBName & "] FROM  DISK = N’” & rsBackup.Fields.Item(“strfilename”) & _
 ”‘ WITH  FILE = 1,  MOVE N’” & colDBName(0) & “‘ TO N’” & colDBFilePath(0) & “‘,  MOVE N’” & colLogName(0) & “‘ TO N’” & colLogFilePath(0) & “‘,  NORECOVERY,  REPLACE”
 objSQLConnection.Execute(strSQLRestore)

 ’Restore Differential
 rsBackup.MoveNext
 objTextFile.Writeline(rsBackup.Fields.Item(“strfilename”) & vbTab & rsBackup.Fields.Item(“strfiledate”) & VbCrLf)
 strSQLRestore = “RESTORE DATABASE [" & strDBName & "] FROM  DISK = N’” & rsBackup.Fields.Item(“strfilename”) & _
 ”‘ WITH  FILE = 1,  MOVE N’” & colDBName(0) & “‘ TO N’” & colDBFilePath(0) & “‘,  MOVE N’” & colLogName(0) & “‘ TO N’” & colLogFilePath(0) & “‘,  NORECOVERY, REPLACE”
 objSQLConnection.Execute(strSQLRestore)
 strStartDate = rsBackup.Fields.Item(“strfiledate”)
End If

The last thing we need to do is find and restore all the transaction log files.  We use the same method of querying WMI for all the files in our backup folder that have an extension of “TRN”.  We then save them in a recordset so we can sort them by date.  In the previous section of code you’ll notice we stored the date of that last backup file we restored in a variable called “strStartDate”.  That allows us to compare it to the current transaction log date and skip restoring any log backups that were taken prior to the last full or differential backup we just restored.  As we iterate through the files in the recordset the other condition we have to check for is whether or not it is the last record in the set.  We do this because we have to specify “norecovery” in our TSQL restore command until we restore the last log file. At that point we remove ”norecovery” from our TSQL restore command (or you can change it to “recovery”, which is the default) so SQL can rollback any uncommitted transactions and make the DB available again.

‘=============================================================================================================================
‘TRANSACTION FILES
‘=============================================================================================================================

Set colFiles = objWMIService.ExecQuery _
 (“Select * from CIM_DataFile where Path = ‘” & strPath & “‘” _
  & “And Drive = ‘” & strDrive & “‘ and Extension = ‘trn’”)

Set rsTran = CreateObject(“ADODB.Recordset”)
rsTran.Fields.Append “strFileName”, 200, 255
rsTran.Fields.Append “strFileDate”, 7
rsTran.Open

For Each objFile in colFiles
    rsTran.AddNew
    rsTran(“strFileName”) = objFile.Name
    rsTran(“strFileDate”) = WMIDateStringToDate(objFile.lastmodified)
    rsTran.Update
Next

rsTran.Sort=”strfiledate”
rsTran.MoveFirst
Do Until rsTran.EOF
 If rsTran.Fields.Item(“strfiledate”) < strStartDate Then
  rsTran.MoveNext
 Else
  objTextFile.WriteLine(rsTran.Fields.Item(“strfilename”) & vbTab & rsTran.Fields.Item(“strfiledate”))
  If rsTran.AbsolutePosition <> rsTran.RecordCount Then
   strSQLRestore = “RESTORE LOG [" & strDBName & "] FROM  DISK = N’” & rsTran.Fields.Item(“strfilename”) & _
   ”‘ WITH  FILE = 1,  NORECOVERY”
   objSQLConnection.Execute(strSQLRestore)
  Else
   strSQLRestore = “RESTORE LOG [" & strDBName & "] FROM  DISK = N’” & rsTran.Fields.Item(“strfilename”) & _
   ”‘ WITH  FILE = 1″
   objSQLConnection.Execute(strSQLRestore)
  End If
  rsTran.MoveNext
 End If
Loop

This last piece of the script just closes the text file we opened to log our actions and includes a function that converts the WMI date format to something more familiar.  For more details on the WMI date conversion function, visit this article in the Microsoft Script Center that was written by the ScriptingGuys.  Now we have successfully restored our database on our DR server and hopefully the crisis is averted.  Of course now you have to go fix the production system…..Good luck!

objTextFile.Close

Function WMIDateStringToDate(dtmInstallDate)
    WMIDateStringToDate = CDate(Mid(dtmInstallDate, 5, 2) & “/” & _
        Mid(dtmInstallDate, 7, 2) & “/” & Left(dtmInstallDate, 4) _
            & ” ” & Mid (dtmInstallDate, 9, 2) & “:” & _
                Mid(dtmInstallDate, 11, 2) & “:” & Mid(dtmInstallDate, _
                    13, 2))
End Function