Super Simple SQL Server Express Backup to Amazon S3

There are lots of robust backup solutions out there, each with varying costs and sophistication.  They are great and I won’t claim that this post is better than any of them.  But in my case I simply wanted a cheap nightly backup of a database that could be stored in my Amazon S3 cloud.  With a little scripting I was able to do it for just the tiny price of the transfers to and storage in S3.

1. Created an Amazon S3 Account and create a bucket for your backups.  (I needed to look up my access keys in the Accounts section while I was there)

2. On the server, created a folder for my backup files and scripts (e.g. c:\backups)

3. Downloaded “S3copy Amazon S3 Command Line Copy” from http://download.cnet.com/S3copy-Amazon-S3-Command-Line-Copy/3000-2242_4-10915715.html?tag=mncol.  This is a simple, free EXE file (s3copy.exe) that allows you to upload a file to S3 from the command line.  I put it in my c:\backups folder.

4. Create a SQL script to backup the database, named backup.sql.  I wanted a fresh, full backup each time so my script was simply: 

BACKUP DATABASE [MyDatabase] TO  DISK = N’C:\Backups\MyDatabase.bak’ WITH FORMAT, INIT,  NAME = N’MyDatabase-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10

5. Create a batch file (BackupDB.bat) that will call SQLCMD to run this script and then use s3copy.exe to upload it:

REM — Perform database backup
sqlcmd -S .\SQLEXPRESS –U <user> –P <password> -i "c:\Backups\Backup.sql"

REM — Copy to S3
c:\backups\s3copy.exe c:\backups\ <bucketname> MyDatabase.bak <accesskey> <secretkey>

6.  I then used Task Scheduler to create a nightly job that runs BackupDB.bat.

Yes, readers, this has all sorts of flaws.  It has no error checking, it doesn’t store multiple generations, it’s storing keys in the open, etc.  I will be fixing all that.  But this gives you the basic framework for how a little creative scripting can get you a low cost, easy solution.

Advertisements