Way back in the mists of time I wrote a post on how to backup SQL server to an S3 bucket using TNTDrive, https://sqlundercover.com/2018/06/18/backup-your-on-premise-sql-server-directly-to-an-aws-s3-bucket/.
Back then, if we wanted to backup SQL to S3 we needed to use a third party tool. Since SQL 2022 things have changed and we’ve now got the option to backup directly to S3 in a similar way that we can backup to Azure BLOB store.
Create an S3 Bucket
The first thing that you’re going to need to do is to create an S3 bucket. If you haven’t already, create an account with AWS and head over to the portal. The AWS free tier will give you 5GB of S3 storage free for the first year.
From the portal, open the services menu, find storage and select S3.
Next you’re going to want to click on ‘create bucket’
Give your bucket a name, I’m going for sqlundercoverbackups since that’s what I’m going to be using it for.
Big Disclaimer
The next section is going to look at setting up the security for the bucket. I’m far from an expert in AWS security. Please follow your own policies with regards to security.
Security
We’re going to need to make our bucket accessible so go ahead and uncheck ‘Block All Public Access’.
Once that’s all done hit ‘create bucket’ at the bottom of the screen, and Bob’s your uncle, you’ll have a fresh new AWS S3 bucket to play with.
Add Some Folders
I like to keep my different backups separate, so I’m going to create a folder for full, diff and log backups.
Click on the name of the bucket and hit ‘Create Folder’
Give your folder a name and hit ‘Create Folder’
Do that for each of the folders that you want to create and you’ll see them show up in the bucket screen.
Security Policies
Right, just let me refer you to my previous disclaimer before we go any further with security stuff.
Now that we’ve got a bucket and some folders, we’re going to need to give access to our SQL Server. We’ll look at setting up some policies to do this.
Find you way back to the services menu, Security, Identity and Compliance and click on IAM.
Find ‘Policies’ in the menu on the left and then click on ‘Create Policy’
Switch to JSON view by clicking JSON in the top right corner.
Copy and paste the following to grant ListBucket, PutObject and GetObject permissions to the policy. Make sure that you change the Resource ARN to whatever applies to your bucket. Note that there is a /* after the ARN for PutObject and GetObject declarations but not under ListBucket. /* will apply the permissions to all folders in the bucket, which is what I want to do. If you want to tie things down further then you could do so by specifying specific folders here. For example, if I wanted this to only apply to my full backup folder, I could use, “Resource”: “arn:aws:s3:::sqlundercoverbackups/full”
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": "s3:ListBucket",
"Resource": "arn:aws:s3:::sqlundercoverbackups"
},
{
"Effect": "Allow",
"Action": [
"s3:PutObject",
"s3:GetObject"
],
"Resource": "arn:aws:s3:::sqlundercoverbackups/*"
}
]
}
Once that’s all done, hit ‘next’.
Here we’re going to have to give our new policy a name. While you’re here, check that the permissions are correct. You should have Limited: List, Read and Write for S3. I have spent a good while banging my head against the wall. I was trying to figure out why my backups were failing. Only then did I look at the policy and realise that it was missing Write permissions.
Hit ‘Create Policy’ and we’re done with that bit. We should now be able to find our policy in the policy list.
Create a User
With a policy done, the next thing that we need to do is create a user.
Select ‘Users’ from the menu on the left of the IAM screen and hit up ‘Create User’
Give you user a name and click next.
We’re going to assign the policy that we created earlier to this user. Select ‘Attach Policies Directly’, find the policy that we created and click on next.
On the last screen, click ‘Create User’ and we should see him appear on the IAM users screen.
There’s one last thing that we’re going to need to do here. In order for SQL to authenticate using this user, we’ll need to create an access key. Click on the user and scroll down to Access Key. Click ‘Create Access Key’.
Select a use case, it doesn’t really matter what you choose here but I went with ‘other’. Click next and give it a tag, I went with sqlundercoverbackups here. Click, ‘Create Access Key’.
You should now be given the key details. Copy these off and put them somewhere safe, without these SQL won’t be able to authenticate.
Now we’ve got the bucket and security sorted, let’s get into the proper stuff and fire up SQL.
Backup a Database to S3
Backing up a database to an AWS bucket is very similar to backing up to Azure BLOB storage. The first thing that you’re going to want to do is create a credential in SQL.
For that you’re going to need a couple of things, the URL of the folder and the user access key that you created in the previous section.
To get the URL, just go back to your AWS portal, click on the bucket you created earlier. Check the box beside the folder that you want to get the URL of and click ‘Copy URL’
Now, let’s look at creating the credential. The credential name should match the folder URL, although you’ll need to replace https with s3. In my case, the URL is https://sqlundercoverbackups.s3.eu-west-2.amazonaws.com/full so the credential name would have to be s3://sqlundercoverbackups.s3.eu-west-2.amazonaws.com/full.
The SECRET value should be in the format of <access key>:<secret access key>
So for me that’s going to be,
CREATE CREDENTIAL [S3://sqlundercoverbackups.s3.eu-west-2.amazonaws.com/full]
WITH IDENTITY = 'S3 Access Key',
SECRET = 'AKIAXYKJQLHU7ZR67APF:zGuWe2LYjK25EaDTW22ARzUokUoda2xMPE4fSi1M'
You’ll need to create a credential for all the folders that you want to backup to, I’ll run the same script again, altered for both the log and diff folders.
To backup the database, it should now just be a case of running a backup to URL, pointing the URL at the folder that you want to backup to.
BACKUP DATABASE SQLUndercover TO URL = 'S3://sqlundercoverbackups.s3.eu-west-2.amazonaws.com/full/SQLUndercover.bak'
Let’s give that a go and see what happens…
and if we now check our bucket we can see our backup file there…
So there we have it, how to backup a SQL2022 server directly to S3. There’s no need to third party software anymore, we can do it all from within SQL.
Thanks for reading and I hope you find it useful.