Support for restoring database backups from Amazon 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 Amazon 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 Amazon S3 endpoint
The S3 endpoint must be configured as follows:
The Azure SQL Managed Instance must be configured as follows:
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 Amazon 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.
If you find this guide useful, please share it with others who might benefit.
Happy restoring!
Related articles
Continue reading...
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 Amazon 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 Amazon 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.
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.
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 Amazon 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 URLKeep 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 results4. 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 wizardThis 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 URLWhen 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.
If you find this guide useful, please share it with others who might benefit.
Happy restoring!
Related articles
- RESTORE (Transact-SQL) - SQL Server | Microsoft Learn
- Automated backups in Azure SLQ Managed Instance | Microsoft Learn
- Backup & restore with S3-compatible object storage - SQL Server | Microsoft Learn
- SQL Server backup to URL for S3-compatible object storage - SQL Server | Microsoft Learn
- Release notes for SQL Server Management Studio (SSMS) | Microsoft Learn
- Back up to URL best practices & troubleshooting for S3-compatible object storage - SQL Server | Microsoft Learn
Continue reading...
