I’ve been having a little play around with AWS recently and was looking at S3 (AWS’ cloud storage) when I thought to myself, I wonder if it’s possible to backup up an on premise SQL Server database directly to S3?

When we want to backup directly to Azure, we can use the ‘TO URL’ clause in our backup statement.  Although S3 buckets can also be accessed via a URL, I couldn’t find a way to backup directly to that URL.  Most of the solutions on the web have you backing up your databases locally and then a second step of the job uses Power Shell to copy those backups up to your S3 buckets.  I didn’t really want to do it that way, I want to backup directly to S3 with no middle steps.  We like to keep things as simple as possible here at SQL Undercover, the more moving parts you’ve got, the more chance for things to go wrong.

So I needed a way for SQL Server to be able to directly access my buckets.  I started to wonder if it’s possible to map a bucket as a network drive.  A little hunting around and I came across this lovely tool, TNTDrive.  TNTDrive will let us do exactly that and with the bucket mapped as a local drive, it was simply a case of running the backup to that local drive.

Let have a look at how to set that up…

I’m not going to go into how to setup an S3 bucket, that’s a subject in itself and something that you’ll find plenty of tutorials on if you do a spot of Googling.

TNTDrive

The first that that you’re going to need to do is grab yourself a copy of TNTDrive from tntdrive.com.

When you’ve got that installed, fire it up and hit ‘Add New Mapped Drive’

You’ll now be presented with a bunch of options.  Leave Storage Type as the default ‘Amazon S3 Storage’ and pop in your user Access Key and Secret Access Key.  You can get the access key by going to the Users section of the IAM console in AWS, choose your user and hit the Security Credentials tab.  The secret access key would have been given to you when you first created the users and there’s no way to get that back so I hope you kept it safe.  If not, don’t panic, you can always create a new access key but this time make sure that you remember that secret access key ;).

When you’ve done that, you’ll be able to choose a bucket to map to.

Click ‘Add New Drive’ and Bob’s your uncle.

You can pop over to file explorer and you should now see your S3 bucket mapped as a drive.

Backup To S3

All you need to do now is just backup your database to the mapped bucket.

BACKUP DATABASE SQLUndercover TO DISK = 'Z:\SQLUndercover.bak'

If we have a look in file explorer now, we should see that backup.

or in the AWS S3 console….

So there you have it, that’s how you can backup your on premise SQL Server databases directly to an AWS S3 bucket.  I hope you found that useful and thanks for reading.