How to Backup SQL Server to URL S3 [Detailed Steps]

A new functionality in SQL Server 2022 allows you to run native backups directly to Amazon Simple Storage Service (Amazon S3). Let's explore it together.

By @CrystalLast Updated June 5, 2024

SQL Server Backup to Amazon S3 Using URL

If you're using Microsoft SQL Server, it's worth considering backing up your databases to a cloud platform like Amazon Web Services (AWS). Instead of traditional backup methods where backups are stored on local disks or network shares, SQL Server allows you to specify an Amazon S3 URL as the destination for the backup file. The Backup to URL feature was released with SQL Server 2014 (and SQL Server 2012 SP1 CU2), but until the SQL Server 2022 release, it only worked with Microsoft Azure Blob Storage. With SQL Server 2022, the Backup to URL feature now supports S3-compatible object storage. S3 compatibility means the storage solution uses the Amazon S3 API to provide an S3-compliant interface.

This URL typically follows the format https://s3.amazonaws.com/bucket-name/folder-name/backup-file-name.bak, where "bucket-name" represents the name of the S3 bucket, "folder-name" denotes the optional folder path within the bucket, and "backup-file-name.bak" is the name of the backup file.

By utilizing SQL Server's backup to Amazon S3 URL feature, you can leverage the durability, scalability, and security of Amazon S3 for your database backups. This approach eliminates the need for maintaining local backup storage infrastructure and provides an off-site backup location for disaster recovery purposes. Additionally, it offers the flexibility to integrate SQL Server backups with other AWS services and enables easy access to backups from anywhere with an internet connection.

This article offers a comprehensive overview of the main backup methods for SQL Server to Amazon S3 URL.

How to Backup SQL Server Databases to Amazon S3

Implementing this feature for SQL Server deployed on an Amazon Elastic Cloud Compute (Amazon EC2) instance requires addressing security requirements and understanding BACKUP command parameters and their relationship to Amazon S3 capabilities. In the following sections, I will explore the performance capabilities when using the Backup to URL feature.

πŸ“’Note: The Backup to URL feature to an S3-compatible object storage is only supported in SQL Server 2022, prior versions are not supported.

Create SQL database backup

1. Use SQL Server Management Studio (SSMS) or a T-SQL script to create a full backup of your database.

2. Specify a local backup file location accessible by the SQL Server instance.

Set up an Amazon S3 bucket

1. Log in to your AWS Management Console and create an S3 bucket.

2. Note down the bucket name and ensure that the appropriate permissions are set to allow the SQL Server instance to upload files to the bucket.

Generate AWS access keys

In the AWS Management Console, navigate to the IAM service and create or obtain an access key and secret access key for an IAM user with sufficient permissions to upload files to the S3 bucket.

Install and configure the AWS Command Line Interface (CLI)

1. Download and install the AWS CLI on the machine where the SQL Server instance is running.

2. Configure the CLI by running ‘aws configure’ and providing the access key, secret access key, and preferred AWS region.

Upload the backup file to S3

1. Open a command prompt or terminal and use the AWS CLI to upload the backup file to the S3 bucket. Run the following command:

aws s3 cp "local-path\backup-file.bak" s3://bucket-name/path/backup-file.bak

2. Replace "local-path\backup-file.bak" with the path to your local backup file, and "bucket-name/path/backup-file.bak" with the desired S3 URL for the backup file. The path within the S3 bucket is optional.

Verify the backup in S3

1. Log in to the AWS Management Console, navigate to the S3 service.

2. Confirm the backup file exists in the specified bucket and path.

By following these steps, you can successfully back up your SQL Server database to an Amazon S3 URL. Remember to periodically repeat the backup process to maintain up-to-date backups in your S3 bucket.

Comprehensive and Powerful SQL Server Backup to Amazon S3

AOMEI Cyber Backup simplifies the process of backing up SQL Server databases and ensures that critical data is safely stored in Amazon S3.

  • Powerful tools provide a comprehensive backup solution for a wide range of recovery needs.
  • An intuitive interface and powerful scheduling capabilities allow for centralized management of all SQL Server backups, reducing administrative overhead and minimizing the risk of data loss
  • Seamless integration with Amazon S3 ensures storage reliability and scalability.

Whether you manage a small business or a large enterprise, the software delivers the reliability and performance you need to protect your valuable data assets.

Download FreewareMicrosoft SQL Server 2005-2022
Centralized and Secure SQL Backup

Automatically SQL Server Backup Databases to URL Amazon S3

1. Download and install AOMEI Cyber Backup on a computer or server within your network.

2. Choose the "Create New Task" > "Microsoft SQL Backup" option from the left-hand menu. Click "Add Device" to establish a connection to your SQL Server databases. Provide the necessary login credentials and details to access your virtual machines. Then select the VMs that you want to back up.

3. Enter a special name for the SQL Server and select the device you added.

4. Choose the destination where you want to store your SQL backups. You can use an external drive, local folder or network share as the backup location.

5. Check Archiving backup versions to Amazon S3 and click Select to choose the added Amazon S3.

6. Schedule SQL Server backup, including: "Backup Method" & "Schedule Type". AOMEI Cyber Backup ensures your SQL Server databases are backed up regularly without manual intervention.

7. Click "Start Backup" to initiate the SQL backup process.

AOMEI Cyber Backup minimizes the risk of backup failures, ensuring that your SQL Server databases are always safe and recoverable. If you want to enjoy more advanced features, you can Upgrade to Premium.

Conclusion

This article offers complete guide to backup SQL Server to Amazon S3. By adopting SQL Server backup to Amazon S3 URL, organizations can enhance their data protection strategies, improve disaster recovery capabilities, and reduce the operational burden associated with managing local backup storage.