Creating a Backup Batch File for Windows Servers (Simple Working Copy)

Overview

Backing up is a good thing. Disk space is cheap and your time is not. But, if backing up is too difficult or such a pain to do that it never actually gets done, it's not of much good.

In this article, we're going to go through developing a batch file that will allow you to very easily backup your entire Windows/MySQL based SugarCRM instance with just a mouse click or two. This is very useful when it comes to disaster recovery and should be mandatory when running administrative operations on your instance like upgrades, installing new packages, or changing the application code. After you are done with this exercise, backing up will be so easy there will be no excuse for not covering your assets. 

For steps on how to create a batch file to make a rolling archive backup (i.e. multiple backups with different date/time stamps) of your Sugar instance, please refer to the Creating a Backup Batch File for Windows Servers (Rolling Archive) article.

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'll start with a simple batch file first. This version will simply allow you to set an application path (AppPath) and a Backup Path (BUPath). We will then instruct Windows to copy all of the files in AppPath to BUPath. In all of these exercises, we'll assume the instance is stored in c:\inetpub\wwwroot\sugarcrm and that our backups will be stored in d:\backups\sugarcrm. Our MySQL programs directory is in c:\wamp\bin\mysql\mysql5.5.20\bin, our database name is sugarcrm, our user name is 'root', and our password is 'rootpassword'. We will be calling our batch file SugarBackup.bat. In all of these cases, make appropriate substitutions where you see fit.

1. Create a file called SugarBackup.bat and open it in the text editor.
2. Copy these nine lines of text into the file and then save it:
   

    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

Explanation of Script

If you have the above properly configured and spelled, when you run the batch file (you can do so by typing the file name or by double clicking it) the following will happen:

1. First, we set some variables. We can actually hard code these into the batch file but by doing this up front, we need set the proper value only once. This is really handy if you're having to manage many Sugar instances. You'll also notice that we use some of the values more than once. Using this method we can set them and forget them.
2. Next, we have ROBOCOPY make a copy of everything in your instance's file system and put it into a folder called "Files" within our backup directory. We used a couple of switches to make things run a little more smoothly:
3. /MIR - this will tell ROBOCOPY to mirror the source and destination files and folders. If you use the same location for your backup each time you run this file, this will copy only what has changed as well as delete files in your backup directory that no longer exist in your application directory.
4. /R:3 - this tells ROBOCOPY to retry copying a failing file only 3 times (the default is a million). This should not be necessary in Sugar because all files should not be locked; but, should there be a temporary issue, this will allow ROBOCOPY to retry but not so many times that the rest of the backup fails. You may want to experiment with the number after the /R:.
5. /W:3 - this tells ROBOCOPY how many seconds to wait before retrying. In this case it is 3 seconds.
6. In the next line we will erase a previous version of our database backup.
7. The last line runs a program called MySQLDump which will log into MySQL and then produce a backup of the entire database in the form of a script. By using the "> %BUPath%\%MySQLDB%.sql" at the end we not only tell MySQLDump to put the file in the backup directory but also to name the file the same name as our database and to give it an extension of "sql".

Additional Information

Here are a couple of final tips for working with this batch file:

1. If you need to change file locations, the database name, the user ID, etc, simply change the variables in the first 6 lines.
2. If a file path has spaces in it, surround the entire string with double quotes.
3. You can run the batch file (SugarBackup.bat) from the Windows Scheduler so that you can periodically make copies of your instance with no intervention.

With this batch file you can make a full backup of your entire instance with just a couple of mouse clicks or by typing a command.