Remote SQL backup onto SAMBA Shares

Backup is a subject which comes up a lot on the rack as regular readers will already have noticed and I have already detailed several of the strategies we employ to make sure we have a myriad of copies of our essential data spread across the network, preferably as far apart as possible! Our latest investigation concerned how to get a usable copy of our most precious and bloatie database from our SQL Server 2000 installation across the VPN (and therefore Cheshire) on a regular basis.

Time for a topical and amusing dip into Google images for the word of the day - bloated, as in a very large database. This little marmot, who obviously has a small pie problem represents our database for this afternoon.

The data to be moved is 1.3 GB over a 2MB line from a Windows 2003 Server to a Linux partition on our CentOS virtual machine. Given all the other scheduled adminnie jobs we have going on overnight I cannot afford for the whole process to take more than about 30 minutes, I sometimes think the network is busier out of office hours!

The solution is of course to compress the backup before sending it and it turns out that after a little investigation there are a couple of products on the market which do this for you. After only a brief search I found SQL Backup by Red-Gate Software and Lite Speed for SQL Server by Quest which both offer on the fly compression and encryption to boot. One might have expected Microsoft to include a compression option in their rather expensive server system but one would be wrong as usual (thanks Bill). Bit less time writing the EULA and more on the software next time eh?

Moving swiftly on, the products mentioned above are relatively simple in their approach in that they add some system stored procedures to your SQL Server install which can be scheduled to run, adding compression and or encryption to a standard full or differential backup. The cost is quite manageable as well at $45 to $399, which is a lot less than the time would cost to build our own script! But incidentally if anyone is interested here is a start. Installing the programs on our test bench was easy and initially everything was very straight forward until we tried to send it to our Linux machine....

In order to share a folder on Linux one requires the cooperation of a service called SAMBA which is really quite powerful and therefore complicated, sharing a folder to any Tom Dick or Harry is very well documented and quite easy. Authenticating against our active directory and sharing with our windows machine requires the services of a good soothsayer however and is somewhat sparsely documented!

Having finally got a shared folder onto the windows network I thought the job was done but unfortunately I hadn't counted on a couple of less well documented features of SQL server.

1. SQL Server does not like backing up to network shares that are not in the same work group
2. SQL Server will not offer to re authenticate, the user account SQL Server logs in on must have explicit and full access to the network share.

I would love to give a blow by blow account of how I got the network share going but I have been chipping away at this problem sporadically and unfortunately I have sort of lost track of how I got where we are.

Having taken a while to sort this I have finally got some comparative data and I am very impressed indeed! Given that a 2MB line is really quite modest, SQL Backup managed to compress, encrypt and squeeze a 1.4 GB database down to 250 MB and ferry it across Cheshire in 20 minutes and 10 seconds! It took me a while longer to get Lite Speed up and running but at only $45 it ripped through the compression and transfer in a mere 10 minutes and shaved 30 MB of the storage requirements at 220 MB! Its my new best friend and I would recommend it to anyone looking to get their database backups as far away from them as possible. The only thing left to sort out is that I cannot afford to change the logon account for the main server as I did on the test machine so hopefully I can get SAMBA to cooperate with the existing setup this time :o)