Creating a Backup Batch File for Windows Servers (Rolling Archive)

Overview

We discuss how to use a batch file to make a simple backup of your Sugar® instance in a predetermined location in the Creating a Backup Batch File for Windows Servers (Simple Working Copy) article. If you are not familiar with it already, please take a moment to review it since this article will be building on this.

In contrast to that article, you can also use a batch file to keep a rolling archive of your Sugar instance, as opposed to one copy. In this article, we will use the technology from the simple backup to make a backup batch file that will automatically save a copy of the files and the database in a folder with a date- and time-based name. This way, each time you run the backup, a new copy of the backup is made without affecting the previous backup. With this method, should something go wrong, you have many different backups from which you can choose -- different snapshots of your instance in time.

Prerequisites

You must already have access to the file system in order write this code directly into Sugar.

Also, for this project you will need:

• ROBOCOPY – a utility for Windows machines that acts like XCOPY but has fewer limitations. ROBOCOPY is called this because it is more ROBust than COPY or XCOPY. ROBOCOPY is one of the components of the Resource Kit Tools provided by Microsoft. You can download it from Microsoft.
• A text editor. Notepad will do just fine.
• A rudimentary knowledge of what a batch file is. If you don't know, this article will provide more than enough information.

Steps to Complete

We will start with the Simple Working Copy solution, then modify it to accomplish the rolling archives:

   SET AppPath=c:\\inetpub\\wwwroot\\sugarcrm
 SET BUPath=d:\\backups\\sugarcrm\\
 MySQLPath=c:\\wamp\\bin\\mysql\\mysql5.5.20\\bin
 MySQLDB=sugarcrm
 MySQLUser=root
 MySQLPW=rootpassword

 ROBOCOPY %AppPath% %BUPath%\\Files /MIR /R:3 /W:3

 ERASE %BUPath%\\%MySQLDB%.sql

 %MySQLPath%MYSQLDump -u %MySQLUser% -p%MySQLPW% %MySQLDB% > %BUPath%\\%MySQLDB%.sql

Next, we will remove the ERASE line since there shouldn't be anything to ERASE. We still want the /MIR switch on the ROBOCOPY command because that not only checks the target for no-longer-existing files, but it also ensures the entire folder tree is copied. Your file should appear as follows:

   SET AppPath=c:\\inetpub\\wwwroot\\sugarcrm
 SET BUPath=d:\\backups\\sugarcrm\\
 MySQLPath=c:\\wamp\\bin\\mysql\\mysql5.5.20\\bin
 MySQLDB=sugarcrm
 MySQLUser=root
 MySQLPW=rootpassword

 ROBOCOPY %AppPath% %BUPath%\\Files /MIR /R:3 /W:3

 %MySQLPath%MYSQLDump -u %MySQLUser% -p%MySQLPW% %MySQLDB% > %BUPath%\\%MySQLDB%.sql

Now we need to figure out how to make a sub-path that will make a separate, time-based named folder inside the %BUPath% that will house each backup. We want the path to look something like this:
d:\backups\sugarcrm\20120806_201453
In this case, the folder was created at 8:14:53PM on August 6, 2012.

The following steps will accomplish this:

1. Before the ROBOCOPY line, insert a line that looks like this:
   SET DTS=%DATE%%TIME: =0%
   This will make a date-time stamp that is captured in the variable DTS. This is kind of an ugly, unusable value but it has all of the raw information we need to make a date-time stamp file name. Here is what DTS will look like:
   SUN 08/05/201222:54:52.51
   From this we want to make something that looks like this:
   20120805_225452
   This way, one can tell at a glace when this was made and the folder names are sortable in Windows Explorer. If you are wondering why the time variable is %time: =0% instead of %time%, this compensates for the fact that in the earlier hours of the morning, Windows neglects to put a leading zero on the hour. The " =0" replaces all spaces with the character "0". This give us a string SubPath with no spaces in it no matter how early in the morning it is.
2. To transform DTS to our desired string, make a command on the next line that looks like this:
   SET SubPath=%DTS:~10,4%%DTS:~4,2%%DTS:~7,2%_%DTS:~14,2%%DTS:~17,2%%DTS:~20,2%
   Each segment of the string like %DTS:~10,4% is simply taking out a chunk of the DTS string and placing it into the string. In the case of %DTS:~10,4%, it goes to the 10^th charater of DTS and grabs the next four characters, making up the "2012" part of the string.
3. Now we need to insert our SubPath into the destination strings right after BUPath. This will make a folder inside the BUPath that has a date/time stamp name.
   After this addition, your file should look like this:
   

    SET AppPath=c:\\inetpub\\wwwroot\\sugarcrm
    SET BUPath=d:\\backups\\sugarcrm\\
    MySQLPath=c:\\wamp\\bin\\mysql\\mysql5.5.20\\bin
    MySQLDB=sugarcrm
    MySQLUser=root
    MySQLPW=rootpassword
   
    SET DTS=%DATE%%TIME: =0%
    SET SubPath=%DTS:~10,4%%DTS:~4,2%%DTS:~7,2%_%DTS:~14,2%%DTS:~17,2%%DTS:~20,2%
   
    ROBOCOPY %AppPath% %BUPath%\\%SubPath%\\Files /MIR /R:3 /W:3
   
    %MySQLPath%MYSQLDump -u %MySQLUser% -p%MySQLPW% %MySQLDB% > %BUPath%\\%SubPath%\\%MySQLDB%.sql

4. Save and run your batch file.

With this simple, 10-line batch file you now are one double-click away from making a complete backup of your instance. This is useful for when you upgrade, install new packages, develop, or just want to keep things safe. Furthermore, you can set this batch file in the Windows Scheduler so that it runs periodically (perhaps every night) so that you are never without multiple restoration points of your instance.