Backup Your On Premise SQL Server Directly to an AWS S3 Bucket

old_bucket

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’

bucket1

 

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 ;).

bucket2

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

bucket3

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.

bucket4

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.

bucket5

or in the AWS S3 console….

bucket6

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. 🙂

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: