SQL Server 2022: Backup and Restore database to AWS S3 bucket - Part 1

By:   |   Updated: 2022-06-30   |   Comments (1)   |   Related: 1 | 2 | 3 | More > SQL Server 2022


Problem

Historically, SQL Server database administrators create databases backup on disk and tape destinations. There was no direct support for backing up and restoring databases into the AWS S3 bucket. SQL Server 2012 introduced backup to Azure storage account, which improved in SQL Server 2016 with the added support for block blobs and page blobs.

Microsoft recently announced the public preview of SQL Server 2022, and it supports backup and restores of a SQL Server database using AWS S3 bucket storage. How do we configure and utilize the AWS S3 bucket for backup and restore into SQL Server 2022? Let's check it out in this tip.

Solution

SQL Server 2022 will be a significant upgrade to SQL Server, especially its cloud integration. It comes with the support of native backup to the AWS S3 bucket.

Many organizations prefer Amazon Web Services as their cloud infrastructure solution. However, SQL Server 2019 and below does not support storing backup to AWS S3 bucket directly using the BACKUP TO URL feature.

This is a multi-series article and Part 1 of the backup and restore series covers the following topics:

  • Create AWS S3 bucket
  • Create an IAM user-defined policy for the S3 bucket relevant permissions for creating a SQL database backup
  • Create an IAM user and apply a custom policy for database backups

Requirements

It would help if you satisfied the below requirements to proceed with this tip.

  1. SQL Server 2022 Preview: Download the SQL Server 2022 CTP 2.0 preview from https://aka.ms/getsqlserver2022 and install the database engine. This article uses the SQL Server 2022 CTP 2.0 – 16.0.600.9 version below.
  1. AWS subscription: You need an AWS subscription for storing backups into an S3 bucket. You can leverage AWS's free tier account for learning purposes.

Create and Configure the AWS S3 bucket

You need to create and configure an AWS S3 bucket for storing the database backups. Login to the AWS console and navigate to the S3 service, as shown below.

amazon s3 create bucket

Click on Create bucket. It launches the bucket configuration page that requires the following inputs.

  • Bucket name: Specify a unique bucket name without spaces or uppercase letters. For this tip, I specify the bucket name as sqlserver2022.
  • AWS region: Choose an AWS region from the drop-down regions list.
amazon s3 create bucket

For this tip, I unchecked the option - Block all public access for simplified configuration of S3 bucket and accept the acknowledgment as shown below. Note: You should configure access for the S3 bucket using access control lists (ACLs) to avoid unauthorized access to the bucket and its content.

block access for bucket

We can use the default values for the remaining configurations and click on Create bucket.

advanced bucket settings

It creates the s3 bucket named sqlserver2022 in the Asia Pacific (Mumbai) ap-south-1 region. Usually, it would help if you chose the AWS region close to your data center.

list of buckets

Open the S3 bucket and create a new folder. Click on create folder and specify the folder name.

bucket details
bucket folder

Click on create folder, and it shows the folder inside the s3 bucket.

bucket details

We see that a few options are disabled. Put a check on the folder name, and it enables an option to copy S3 URI and URL.

bucket details

It has the following values for the S3 bucket in my demo environment. We require the following information while performing the database backup and restore with the AWS S3 bucket.

  • Copy S3 URI: s3://sqlserver2022/fullbackups/
  • Copy URL: https://sqlserver2022.s3.ap-south-1.amazonaws.com/fullbackups/

AWS Policy for accessing the S3 bucket

IAM policies in the AWS IAM (Identity access management) define the user's permissions, or role can perform for the specific service. SQL Server 2022 requires ListBucket and PutObject permissions to take database backup directly into the S3 bucket.

  • ListBucket: The ListBucket access returns a list of all buckets owned by the authenticated sender of the request.
  • PutObject: The PutObject access allows you to write and adds objects into the bucket.

Navigate to IAM in AWS services and click on Policies. The policies tab shows you existing AWS policies and user-defined policies.

policies

To create a new IAM policy, click on create policy. In the visual editor, choose the followings:

  • Service: Choose the S3 service.
  • Actions: Choose the ListBucket and PutObject as shown below.
policy settings
  • Resources: In the resources section, we can choose a specific or all resource for providing access. It is recommended to restrict access to a particular resource or S3 bucket. To limit the access for our S3 bucket, specify the bucket name in the amazon resource name(ARN) format – arn:aws:s3:::bucketname.
s3 bucket actions

On the next page, review the policy and specify a policy name.

bucket policy

We need an AWS IAM user that has access to the S3 bucket for writing the backup copies into it. Navigate to IAM in AWS Services and click on Add users.

aws users

Specify an IAM user name and select AWS access type – Access key Programmatic access.

add user

Click on Next: Permissions. In the set permissions, click on Attach existing policies directly.

add user

We can choose an existing AWS or custom policy for the user. We already defined an IAM policy – sql2022backuppolicy. Therefore, filter the policy and decide to apply it to IAM users.

add user

In the review section, we can see the [sqlbackup] user has assigned [sql2022backuppolicy].

add user review

Click on create user, and it gives the access key id and secret access. You need to save these credentials since it is the last time they are available to the user.

add user
Next Steps



Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips


Article Last Updated: 2022-06-30

Comments For This Article




Tuesday, July 5, 2022 - 11:49:46 AM - Jason Taylor Back To Top (90232)
We use a tool called SQL Backup Master that makes it easy to send backups to S3, Google Drive, etc. Works with all versions of SQL Server too.


download














get free sql tips
agree to terms