Taking a backup of an SSAS database

Backups?  No problem - just set up a maintenance plan in SQL Server and schedule the backup to run every night while the world is sleeping, or my bit of it at least.  Or in my current job, use Red Gate Backup to do the same thing.  Both do a good job, both are easy to set up.

So what's the problem?

I'm starting to get involved with Microsoft's Business Intelligence tools.  I've blogged before about SSRS, the Reporting Services tool which lets you create and deploy nice-looking reports.  In fact SSRS was one of the first things I got involved with when I started using SQL Server.  But until I started my current job I had little practical experience of SSIS - Integration Services or SSAS - Analysis Services.   I plan to write more about these in future.

The idea behind SSAS (practiced egg-suckers can skip forward past this bit) is that you extract data from your regular transactional database, and create a de-normalised database showing Facts, Dimensions, and Analysis Cubes.  Never mind the details, the point is that you can do very quick analyses of things like sales of widgets by type and colour and salesperson and area and month and whatever else might possibly matter.  Yes, I know you can do all that stuff by writing an SQL query, but SSAS does it a damn sight quicker than writing individual queries.  Honestly, once it's set up, it's just a click and drag thing.  Very impressive, and the software comes free with SQL Server, so a whole lot better than the million dollars that other vendors are likely to charge for their competing products.  It takes a bit of setting up, (well, a lot) but once you have your SSAS database in place, it's very whizzy indeed.

Here's the problem.  SSAS databases are different.  Check out the files it creates - none of that boring .mdf and .ldf stuff.  And you can't take a backup with Red Gate - I rang and asked them, and they may be putting it into a future version, but not yet.  You can't take a backup with your maintenance plan, which is geared up to the bog-standard OLTP databases I'm used to.  No.  Welcome to the wacky world of OLAP!

What you CAN do is back up your SSAS database by hand.  Just right click on it, and save it to the location of your choice.  All you have to do is come into the office every night at midnight and take a backup.  Er, no thanks.  Maybe I'm a bit spoilt by the automation tools I'm used to, but I really don't fancy going back to the days of 24 hour computer operators, even if spinning tape drives have a certain retro appeal to Thunderbirds fans...

Here's how to do it.  Find the SSAS database, right click on it, and up comes the following dialog box.  Notice the backup file is in .abf format?  Analysis Backup Format, I suppose - in any event, it ain't the familiar .bak format that native SQL backup gives you, or the .sqb format of Red Gate.  It's something else... <cue spooky Outer Limits music>.

You can compress the database and encrypt it, at least in version 2008 onwards.  If you click on OK, it takes the backup.

But instead of OK, click on Script.  Suddenly you have a little chunk of XML which will backup your database.

Unfortunately the top secret encryption password shows up in plain text, so frankly there isn't a great deal of point in encrypting something with a password that any hacker can read, in my opinion.  Ho hum, maybe the next version will address this.

Set up a new job under SQL Server Agent.  Tell the dialog box that you want to run a SQL Server Analysis Services Command (don't worry, it's on the pull-down menu, you don't have to type it in) and paste the XML stuff into the box.  Schedule it to run in the middle of the night when no-one's about, and Bob's your uncle.

Here's another little wrinkle:  You probably won't want to overwrite the backup every night, so set up a second step which changes the name to include the date.  Instead of MIS.abf, you save 20120817_MIS.abf.  Next time you run the backup, it'll be 20120818_MIS.abf.  And so on.  Here's the code for doing it:

DECLARE  @Command NVARCHAR(512)

DECLARE  @DatePrefix CHAR(8)

-- convert date to 20121105 format

SELECT @DatePrefix = convert(CHAR,GETDATE(),112)

 --print @DatePrefix

--format command.  Note " " in case filenames contain spaces

SELECT @Command = 'ren "G:\OLAPBackups_Test\IPO MI.abf" '

                              + '"'

                              + @DatePrefix

                              + '_IPO MI.abf"'

                 

--print @Command

EXEC master.dbo.xp_cmdshell @Command

Essentially you create a variable for the date, and put today's date into it.  Create a variable for the command, and construct a Rename command.  Wrap it all up in quotes just in case someone wants a database name with a space in it.  Then you run that command using the xp_cmdshell stored procedure.  There are issues with using xp_cmdshell - see this helpful article.  But it can be useful.

There's a problem with this, of course - sooner or later your backup drive is going to fill up with .abf files.  Probably sooner, as your production SSAS database could easily end up containing millions of de-normalised grains of data.  So set up a third step to clear out the older versions every time the job runs.  ForFiles is a good way of doing this - and I wrote about this in my first ever blog post.  Here's the code:

-- delete abf backup files older than 4 days

exec xp_cmdshell 'forfiles

                        -p G:\OLAPBackups_Test\

                        -m *.abf 

                        -d -4      

                        -c "cmd /C del @FILE" '

So three steps then - the XML to do the backup itself, a bit of code to add a date prefix, and finally a bit of code to sling the oldest copy.  Schedule this to run nightly, set up an alert to email you if something goes wrong, and you are sorted.