Backup and Restore SQL Server 2022 to and from AWS S3

One useful new feature for SQL Server 2022 users who run on EC2 instances is SQL Server 2022’s ability to perform database backup to and restore from what Microsoft calls “S3-compatible object storage.”

AWS has a good blog post about this that details many of the important considerations when backing up and restoring SQL Server 2022 to and from S3. You’ll need to pay special attention, as always, to the IAM policies and authentication as well as the networking you use to transport the traffic. (Any questions? Let me know in the comments.)

But there’s one thing that the AWS post doesn’t discuss: how to backup to a non-default S3 region (that is, not us-east-1). Quite a lot of S3 documentation lacks awareness of how to make calls to non-default S3 regions. It’s even worse when your client is using GovCloud like mine was. GovCloud users will be familiar with the “unloved child” nature of much of AWS’s doc about using services in GovCloud. Start with keeping in mind at all times the differences in Amazon Resource Names.

So, the examples in the AWS blog post for SQL Server 2022 backup to S3 won’t work for GovCloud users and, I presume, anyone using a non-default S3 region. Fortunately, Microsoft documents how to get a non-default S3 region to work. It’s a simple BACKUP_OPTIONS JSON formatted object.

Backup and restore to S3 from SQL Server delivers all the benefits of S3 — the most mature cloud storage system available — and you use it just like any backup or restore process in SQL Server. What’s not to love?

Here’s an example of a T-SQL statement you can model if you want to backup a database named Custom to a bucket in a non-default S3 region.

BACKUP DATABASE Custom
TO URL = 's3://your-s3-govcloud-bucket-name.s3-us-gov-west-1.amazonaws.com/backups/Custom.bak'
WITH FORMAT, STATS = 10, COMPRESSION, MAXTRANSFERSIZE = 20971520, BACKUP_OPTIONS = '{"s3": {"region":"us-gov-west-1"}}';

And, just for completeness, here’s a corresponding restore statement. Naturally, the RESTORE DATABASE command uses a RESTORE_OPTIONS JSON object to specify the non-default S3 region

RESTORE DATABASE Custom
FROM URL = 's3://your-s3-govcloud-bucket-name.s3-us-gov-west-1.amazonaws.com/backups/Custom.bak'
WITH REPLACE, RESTORE_OPTIONS = '{"s3": {"region":"us-gov-west-1"}}';