A basic backup strategy

I'm running a very simple web server for Cultiv and some other projects. It's a virtual server to which I have full remote desktop access. This means that I am the administrator and I decide what goes on at the server, no interference by my hosting provider. But, as always, with great power comes great responsibility…

I believe my hosting provider makes a backup of the whole server every night, and at best, I can have them restore a 1-day old backup. One day of retention is not something that I feel comfortable to rely on, by far!

Recently Jeff Attwood (of Coding Horror and StackOverflow fame) had a big scare and the resulting discussion that followed, lead to a blog post by Joel Spolsky (of FogCreek and StackOverflow fame) pointing out that backing up is not the problem, restoring usually is.

Well, since I had NO backup strategy, whatsoever, I figured it was about time to change that.

My current server specs are: A Windows Server Web - (2008 Hyper-V VPS) with IIS7 and Microsoft SQL Express server. It's that simple!

What to backup?

The question of what to backup seems like a simple one to me:

• my websites (including file/folder permissions);
• the website's MS SQL databases;
• the IIS configuration for the websites;
• the IIS log files, so that I could maybe reconstruct what happened in case of a catastrophe.

How to backup?

Websites and meta content

The websites are all in one folder, so that's easy (I'll talk about the permissions later).

I had to look a little bit to find a way to backup the configuration files, but a post about IIS configuration history was a great resource.

To backup the current IIS configuration, I included the %windir%\system32\inetsrv\config\ folder and I love that the configuration changes are being written automatically to the %systemdrive%\inetpub\history directory. That way, I could recover and older configuration if needed, cool!

Finally, I included the %systemdrive%\inetpub\logs file for the sake of it.

SQL Express

At first, I was backing up SQL's ".mdf" and ".ldf" files, which is an utterly stupid idea, as I learned from StackOverflow postcast 78. So I needed a different strategy for that. SQL Express doesn't offer the ability to create automated back-ups, so I had to look for extra software. This was difficult, nothing really did what I wanted it to do: give me a bunch of .bak files. Everything I found was either too fancy or didn't work.

My colleague pointed me in the direction of an article on ServerFault and I was able to construct a decent enough solution out of this.

I took the SQL script in the ServerFault post and modified it a little, now the backup files are zipped using 7zip instead of zip and at maximum compression ratio (7zip can compress text really, really well, so why not). I put it in the folder C:\DatabaseBackup as per the default for the script.

Next, I set up a scheduled task in Windows, to run the script every day. The action is "Start a program", the program/script is "sqlcmd" and the arguments are:
-i C:\DatabaseBackup\backup.sql -o C:\DatabaseBackup\output.txt -S .\SQLEXPRESS
Where .\SQLEXPRESS is the name of my (you guessed it) SQL Express server. You might need to do add some login credentials to these arguments, have a look at the sqlcmd documentation for the details.

Finally, before running the script for the first time, I had to enable the xp_cmdshell option in SQL Express. Make sure to read up on the security implications when you do so.

Now, this will not give you any notifications when the SQL script is unable to backup the databases. On another server, I installed Vale Express Agent to get notifications when the SQL fails. To do this, make sure to paste the SQL directly into a new backup job (don't use sqlcmd to call the script!).

Where and how to backup?

I've been working with Amazon S3 lately and S3 was also mentioned in the podcast that I mentioned earlier. Basically, Amazon makes sure that your data is in at least two locations in the world at all times. Also, S3 is terribly inexpensive, so it seemed like a good idea to backup to their servers.

Enter JungleDisk. I'd heard about JungleDisk before, it can backup all of my data to S3 and it is dirt cheap. It's only $5 a month for a server edition (which runs the backup as a Windows service), this includes the first 10 GB of storage for free! Great deal, as I currently only have a few hunder megabytes to backup anyway.

JungleDisk also natively supports backing up NTFS file permissions, which is exactly what I need.

Setting up was quite easy, however I was suddenly stuck in a "looking for server" screen after entering my JungleDisk credentials. As it turns out, the first time you set up the software, you have to start "Set License Key for Jungle Disk Server Edition" first. After that, it was easy to add the folders I needed and exclude .mdf/.ldf files.

How to restore?

Apparently, this is the most important part of my backup strategy… And I'm being dumb about it, because I haven't tried restoring at all. I mean, yes, I've restored a JungleDisk backup to see if I can get my files back, but that's it.

I am prepared to spend a few hours rebuilding my server if something happens to it. Currently I don't have much data and a simple deployment strategy, so this should not be a big problem.

I'll revisit the restore part once I have to move to a much bigger server, but by then, I'll probably also have to revisit the backup strategy. As I said, it's basic.

10 PRINT "REALLY BASIC"

20 GOTO 10

RUN