March 10, 2009

Using Automated Build Studio to automate offsite backups

Filed under: General — Tags: — Darrin Maidlow @ 10:33 pm

The importance of an offsite backup of your data is well known.  There are companies out there that do simply that – offsite data storage.  Take for example Iron Mountain’s service.  Depending on the amount of data you have to protect, LiveVault can be  great service.

I’ve been using (ABS) for some time now to ensure consistent, and fast builds of our products.  I’m incredibly pleased with it.   One of the key benefits to ABS is its versatility.  Yes, the primary goals of this product is to automate the software build process – and a lot of specific tools are provided in the product to accomplish this (e.g. working with source control, easy tools to automate compiling etc).  Build systems are really just a collection of tasks, usually very small tasks – but often there are dozens, hundreds or even thousands of these tasks.  Task as small as running a dos command line statement, copying files, making a zip file.  When I noticed that ABS gives you the ability to automate FTP operations, it got me thinking – this “build” tool could be used to automate so many of those little tasks I’ve always meant to ‘just write a small batch file’ for – but never got around to doing…

One of our most critical pieces of data is our database for our source code version control.   No source code, no software company – don’t think I need to say much more.   Vault uses SQL server to store its bits.  Well would you look at that.  ABS has a “backup database” component.  The workflow of what I want to accomplish is quite simple:

  1. 1. Backup and verify SQL server databases
  2. 2. Zip up said backup files
  3. 3. Upload the zip file to an FTP server that cannot burn down or blow up at the same time as our database server.


Pretty simple – and ABS will do everything we need – and then some.  So let’s get started.  First, I have to assume you have an SQL server running with databases in it.  I also assume you have an FTP server setup and running.  If you really wanna get fancy – get an FTP server that supports SSL running.  I recommend .

ABS Installed on same machine as SQL ServerAutomated Build Studio SQL Server Backup Operation

If ABS is installed on the same machine as SQL server we can make use of the built in macro operations to backup SQL server.  Start up ABS and create a new macro.  We’re going to start off simple – and maybe we’ll add some cool later.  First we’ll add a database backup operation.  From the left hand Operations “toolbox” click on SQL to expand the operations available there.  Double click on “Back Up Database”.  This will add a database backup action to our macro.  On the right hand side of the screen in the “macro” section, double click on the new macro operation and fill in the needed information to connect to your SQL server.  Be sure to specify the database, destination path and that the type of output is File.  If you have more than one database you want to backup – add another backup operation and repeat the process.  To backup Vault – we need to backup the sgvault and the sgmaster databases.

If you run your macro at this point you will be prompted with a message that “The executable file of the “SQLMaint” tool is not specified.   The SQLMaint executable is installed with your SQL server instance and can be found in the Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn folder.  Setup your tools to point to this file.  Note, MSSQL.1 will change depending on the you are running.

But, SQL Server is installed on another machine and that’s the way I like it!

Yup, me too.  The built in SQL Server backup operations depend on the SQLMaint executable to run backup process.  Again, this file is installed with SQL server and I could not get this file to run on machine that did not have SQL Server installed.

Instead what I did was created a batch file/SQL script combo on the machine running SQL server and scheduled a task to execute this batch file. The SQL script looks like this:

   1: BACKUP DATABASE [sgmaster] TO  DISK = N'C:\MSSQLData\backups\sgmaster.bak' WITH FORMAT, INIT,  NAME = N'Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
   2: GO
   3: BACKUP DATABASE [sgvault] TO  DISK = N'C:\MSSQLData\backups\sgvault.bak' WITH FORMAT, INIT,  NAME = N'Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
   4: GO


A batch file that executes this script is scheduled to run at a fixed interval and simply consists of this:

   1: "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S localhost -i "C:\AdminScripts\SQL Server\backup.sql"


This batch file on execution creates two files in c:\MSSQLData\backups.  These files will be processed by the next step in the macro.  Note that because no username or password information is being specified here, the user set to run this task must have the appropriate rights in SQL server.  This batch file is executed by adding a windows scheduler task.

Whichever path you chose for creating your backup file.  Go ahead and run the macro item for an SQL backup, or from the SQL server execute your batch file to get a backup made of the database(s).Automated Build Studio Create Zip operation

Building the zip file

The next operation we need can be found in the “Archivers” section on the left.  I’m using Zip for this example.   Add a “Pack Files With zip” operation to the macro and setup the properties.  Define the archive name locally.  Next we’ll need to set the path to the files to zip.  We’ll use UNC paths to access a share on our SQL server.  Depending on the rights of the user executing the macro, the administrative shares on the SQL server can be used or a new share can be created with the appropriate permissions assigned.

I set the zip to use the “Best” compression for the “Pack Level” and told it to “Move to archive” which results in the files being zipped getting deleted.  I also set a password on the zip – but make sure you write that down somewhere =).

Select the new archive item and click the “Run the selected operation” button.  This should create a new zip file for you in the appropriate location.

Uploading the zip

In the toolbox expand FTP and we’re going to add a “Connect to FTP” operation.  Double click this operation in the macro section and setup the properties.  Add your hostname, user name, password.  Set the port if necessary.  If your FTP server is using SSL setup the SSL information.  You can now click the “Run the selected operation” button to test the FTP connection.  If needed add some “Create FTP Directory” and “Set Current FTP Directory” items as needed.  Finally we’ll upload the newly created zip.

Add a new “Upload File(s) to FTP” operation to your macro.   Browse to and select the newly create zip file.

Click the big Green Go button – and your backup should be created (for a local SQL server), a zip files made, and that zip file should be uploaded to your SSL enabled FTP server.

Scheduling the macro runAutomated Build Studio Scheduled Execution

ABS comes with a nice little interface to add scheduled executions.  From the “Tasks” menu choose “Windows Scheduler”.  This will bring up the task scheduler window.  Click Add to add a new task.  Up will come a helpful wizard that will help you setup the task.  Set the various options like name, macro file, execution time and frequency and you’re done.   If your SQL server is on another machine, and you have scheduled that backup to run, you will need to ensure that this task is scheduled to execute after the backup is complete – otherwise your backup could be incomplete.


Building this macro let us cancel our LiveVault account, saving the company a decent amount of cash per month.  While making use of a tool we needed for our for and , and our dedicated server, we were able to ensure we critical offsite backups being automatically taken care of.

This basic macro should help you get started with ABS to implement your own offsite backup solution – possibly making use of resources you already have in place.  Obviously this macro can be enhance significantly – other resources can be zipped up and backed up.  The ABS scripting and variables can be implemented to append a unique number or a date to the file name which would allow for a range of backups to be stored. 


Technorati Tags:

March 2, 2009

One Click Builds Are A Beautiful Thing – Automated Build Studio

Filed under: Development — Tags: , — Darrin Maidlow @ 7:00 am

I’ve known for a long time that an automated build system is valuable, hey it’s number two on .  Recently the complexity of some of our products made an automated build system a requirement.  It was getting to the point where creating a build of was a four plus hour ordeal.  It had dozens of little steps, and missing even one would result in a defect.   This defect would be a vicious cycle.  We would have to debug to find the problem and fix it.  This would cause ANOTHER four hour rebuilt, and the “cycle of bug” would continue.

This complexity resulted in infrequent builds and as a result the code was not tested as well as it should have been.  Not to mention, I’d get really frustrated and even pissed off on build days.   It took me a while to find a system that really made me happy.  On a whim one night I downloaded (ABS) and gave it a shot.  After installing ABS on a new virtual machine that would become our dedicated build system I fired it up.  I was pretty impressed with the wide range of actions and  tools it supported.  In our environment we had several must have items:

  • 1. SourceGear Vault
  • 2. Visual Studio 2008
  • 3. Oracle/SQL Server
  • 4. Installshield 2009


ABS worked with all of them.  In addition to the key requirements I had – it supported a plethora of other operations.

First thing I did was sketch out a rough outline of what was involved in creating a build.  Get latest source code, compile, delete temporary files, etc etc etc.  I started selecting various options from the ABS and fleshing out my macro.  Within a couple of hours, I had a sweet macro in place.  My macro started to look like this:

  • Getting the latest code from Vault
  • Compile all assemblies
  • Delete the temporary files
  • Build the needed release folder structure
  • Move the new files into the needed structure
  • Execute on all assemblies
  • Increment the build version, and set the version on all assemblies
  • Create a label of the build in Vault
  • Build a zip of the release
  • Build the Installshield installer
  • Connect to the build FTP and upload the new build


The best part – all of this was occurring in a 100% reliable fashion, oh and it took minutes for a full build to occur.  Over time, this project was tweaked, and copied.  It evolved as I learned more about ABS – or thought up new requirements.  Take for example, it has evolved into three editions, sharing a similar yet different set of source files.  ABS lets me rebuild all three editions with the click of a button – in minutes.   Long long ago, in the before times something like this would have taken days, and years off my life.

I’ve only scratched the surface of what I’ve been able to do with ABS – some other cool features worth mentioning is that builds can be scheduled to run, there is a web application for viewing build history as well as kicking off builds.  It has a scripting language, and the support I’ve received is fantastic.  The price of the product makes the decision to buy ABS a non issue.

Man, now I’m starting to sound like a sales guy. =)  Seriously though, check out if you develop boxed, or even custom software.

Powered by WordPress

Switch to our mobile site