How to restore backups from AWS S3 to Azure SQL Managed Instance

Microsoft

Sep 04, 2023

Support for restoring database backups from AWS S3 to Azure SQL Managed Instance (MI) is now Generally Available (GA)! This feature offers users a flexible way of restoring backups and makes database migration to Azure SQL Managed Instance easier. Dive into this post to understand the scope and benefits of this new feature.

Background

In September last year SQL server 2022 introduced new feature – backup and restore to simple storage service (S3) – compatible object storage that grants the user the capability to back up or restore their databases using S3-compatible object storage, whether that be on-premises, or in the cloud.

To provide this integration Azure SQL MI is enriched with a new S3 connector, which uses the S3 REST API to connect to AWS S3 storage. It extends the existing RESTORE FROM URL syntax by adding support for the new S3 connector using the REST API.

Prerequisites for the AWS S3 endpoint

The S3 endpoint must be configured as follows:

A user (Access Key ID) has been configured and the secret (Secret Key ID) for that user is known to you. You need both to authenticate against the S3 endpoint.
At least one bucket with a .bak file has been configured.

Prerequisites for Azure SQL Managed Instance

The Azure SQL Managed Instance must be configured as follows:

User must have permissions to connect to Azure SQL Managed Instance and run T-SQL scripts to perform restore operations.
Network Security Group (NSG) must have outbound security rules set to allow TCP protocol on port 443 to Any destination.
Make sure other network security rules in tools such as Network Manager/Azure firewall, and similar, are not blocking outbound traffic.

How to restore from S3 bucket via T-SQL

In this example we will show how to restore .bak file(s) from AWS S3 bucket.

1. Make sure you have the right file path from AWS S3

The easiest way to get a proper S3 URL of a .bak file you want to restore to Azure SQL MI is to navigate to S3 bucket and specific folder where .bak files are located. Now select a .bak file and click “Copy URL“ to copy correct URL.

Copying S3 URL

Keep the copied URL handy. Pro tip: if you use Windows you can use Windows logo key + V to see clipboard history.

2. Create credential

First navigate to T-SQL query editor of your choice and connect to the Azure SQL Managed Instance. To restore from S3 bucket first you need to set up a credential to retrieve files from S3 bucket. To do so follow the next template and choose one of these two file path options:

-- Option 1 
CREATE CREDENTIAL   [s3://<bucketname>.<endpoint>/<path>] 
WITH 
        IDENTITY    = 'S3 Access Key', 
        SECRET      = '<AccessKeyID>:<SecretKeyID>'; 

-- Option 2 
CREATE CREDENTIAL   [s3://<endpoint>/<bucketname>/<path>] 
WITH 
        IDENTITY    = 'S3 Access Key', 
        SECRET      = '<AccessKeyID>:<SecretKeyID>';

Make sure you always use the path in your restore command as it is defined in your credential. This is the "real" credential we'll use in our example:

CREATE CREDENTIAL   [s3://realbucket.s3.us-east-2.amazonaws.com/TestFolder] 
WITH 
        IDENTITY    = 'S3 Access Key', 
        SECRET      = 'REAL_ACCESS_KEY';

3. Test credential

After having credentials set, now is the moment to perform test on the backup file stored on AWS S3 bucket. We can do this by performing `RESTORE HEADERONLY`.

RESTORE HEADERONLY  
FROM URL = 's3://realbucket.s3.us-east-2.amazonaws.com/TestFolder/TestBackup.bak';

After running this script you shall be able to see the results from reading a backup header as following.

Test results

4. Restore database from single .bak file on S3

If you have received results, that means now you have everything prepared for performing the native restore from S3 bucket. The script for performing restore operation from the S3 endpoint location looks like this:

RESTORE DATABASE <db_name> 
FROM URL = 's3://<endpoint>/<bucket>/<backupfile>.bak'

You can also use "Option 1" URL with bucket name in front. In our example below with "real" URL, we use option 1 since that one matches our credential.

RESTORE DATABASE [DB1]  
FROM URL = 's3://realbucket.s3.us-east-2.amazonaws.com/TestFolder/TestBackup.bak';

Note: You cannot have your database pre-created. When performing a native restore Azure SQL Managed Instance will create a database on your behalf. This is general limitation, it is not S3-specific.

5. (Optional) Restore from multiple .bak files on S3

You can also perform a native restore from multiple .bak files located in AWS S3 just by simply adding multiple URLs, like usual. Follow the next template to perform this:

RESTORE DATABASE <db_name> 
FROM    URL = 's3://<endpoint>/<bucket>/<database>_01.bak' 
,       URL = 's3://<endpoint>/<bucket>/<database>_02.bak' 
,       URL = 's3://<endpoint>/<bucket>/<database>_03.bak' 
-- ...
,       URL = 's3://<endpoint>/<bucket>/<database>_64.bak'

Note: Limit is 64 files, and this works for both filepath options.

If you receive any error, you can check best practices & troubleshooting page.

How to restore from S3 bucket via SSMS

If you use SSMS 19.1 or later, you can also utilize restore wizard. Once you are connected to Azure SQL Managed Instance, do a right click on databases and click on “Restore Database” item.

Opening SSMS' restore database wizard

This will lead you to restore wizard where you can add S3 URLs to your backups and make sure you also populate details about credentials.

SSMS Restore Database wizard to restore from S3 URL

When restoring a database via SSMS wizard, be aware that it will read DatabaseName field from .bak file and will prepopulate destination database name. Make sure you do not have already created a database with the same name or change the database destination name. After the completion of restore you will receive a popup that will let you know it has been successful. You can also restore from multiple .bak files in SSMS as well.

Conclusion

In this blog post we have outlined the steps to retrieve the file path from S3 and the methods for restoration via T-SQL and SSMS, along with key prerequisites. Backup to S3 is currently not supported, but feel free to nominate it on Azure SQL Ideas forum.