How to Backup SQL Server Database to S3 Bucket Step-by-Step?

Why keep every SQL Server backup close to the database when you can store a copy in the cloud? If you’re looking for how to backup SQL Server database to an S3 bucket, this article is for you.

Amazon S3 can provide a separate cloud storage location for your backup files. A backup sitting on the same server as your database won’t help much when that server is the problem. Therefore, cloud storage gives you a separate location even if the server crashes for any reason.

Moving data from SQL Server to S3 Bucket
by inaws

However, having an S3 bucket is not the only thing you need to backup your SQL Server database to. There is more to it; let’s find out what.

Why Backup SQL Server Database to an S3 Bucket? Importance

There are various reasons to do so; however, the situation might vary from user to user:

1. Separate backup location: If your SQL Server and its local backup files are affected by the same hardware failure, you may lose both. Therefore, S3 gives you a separate storage environment to keep your data protected.
2. Scalable cloud storage: You don’t have to keep expanding the local disk attached to your SQL Server just to retain more backup copies. This is the reason why backing up your SQL Server to an S3 bucket can save you from that.
3. Off-site protection: An Amazon S3 bucket can keep a backup copy away from the primary SQL Server environment.
4. Long-term retention: Moreover, an S3 bucket can be useful when you need to retain older SQL Server backup files. AWS also supports lifecycle configurations for managing older objects.
5. Disaster recovery: A backup stored separately from the primary SQL Server environment can become useful when recovering from infrastructure failures or other major incidents.

Few Necessary Permissions and Prerequisites for SQL Server Backup to S3

There are two sets of permissions that you will need to establish, one on SQL Server and also on the storage layer.

First, check the SQL Server version while performing the backup job manually. Native backup and restore to S3-compatible object storage is supported in SQL Server 2022 and later. SQL Server 2019 and earlier versions do not support this feature.

On SQL Server, the user account that you used to issue BACKUP or RESTORE commands should belong to the db_backupoperator database role and must have Alter any credential permissions.

On Amazon S3 storage:

1. You need to create credentials on your S3 or S3-compatible storage before connecting it to SQL Server, for that, you must have a proper Access Key and Secret Key. These credentials must have read and write permissions for backup files.
2. For other S3-compatible storage providers, the Access Key ID should have both ListBucket and WriteOnly.
3. An S3 bucket must already exist before you initiate the SQL Server backup to Amazon S3 process.
4. Once your S3 bucket is ready, configure it with the correct S3 endpoint in SQL Server.
5. After these requirements are checked carefully, you can configure SQL Server to back up and restore databases using the S3-compatible storage.

Important note: At least one bucket must already exist before you start the backup. Buckets can’t be created or configured from SQL Server 2022. Also, SQL Server 2022 and later support native backup and restore to S3-compatible object storage. SQL Server 2019 and earlier don’t support this native capability.

How to Backup SQL Server Database to S3 Bucket Using a Manual Method?

1. To begin this manual backup job, first create an S3 bucket.
2. After this, you will need to create the bucket where you want to store your SQL Server backup files.
3. Then, configure SQL Server credentials with the S3 access key and secret key.
4. From there, define the S3 backup path. Your destination will use an S3 URL. Make sure to format the URL correctly.
5. Once done, just run the backup command and monitor its progress.
6. After completion of the backup job, verify that the backup completed successfully and confirm that the .bak object appears in the S3 bucket.

Important point: the S3 bucket needs to exist before the native BACKUP TO URL operation. The backup command does not create the bucket.

How to Backup SQL Server Database to S3 Automatically?

The above-mentioned SQL Server backup method can work well for environments that meet the required version and configuration requirements. But if you manage large SQL Server databases and are looking for scheduling, encryption, monitoring, and incremental backup management in one place, a dedicated backup application can simplify the process.

To ease your pain, the Prapl SQL Server Backup Tool is a convenient way to back up SQL Server to an S3 bucket. Its easy-to-use interface and different built-in functions can create backup jobs easily as needed. It supports all SQL Server versions, including 2022 and 2019, for creating backup jobs.

In fact, if needed, with this utility you can securely backup SQL Server database to a local machine for more control.

Quick Guide to Automatically Backup SQL Database to S3 Bucket

Launch the Prapl SQL Server Database Backup Tool on Windows, connect to your SQL Server, and then test the connection.

From the list of databases, choose the SQL Server database you want to protect, you can even select multiple SQL Server databases as well.

In the next step, select Amazon S3 as the destination. Configure the S3 bucket and required access details.

Choose a recurring schedule such as hourly, daily, weekly, or monthly as needed for this process. Tap the Save and Run Now button to initiate the procedure.

Top 9 Benefits of Using Prapl for SQL Server to S3 Backups

1. Scheduled S3 backups: It allows businesses to configure recurring backup schedules instead of starting each backup manually.
2. Centralized management: This software makes the entire task easy by managing database connections, backup settings, destination, and retention from just one application.
3. Multiple storage options: It allows you to backup SQL Server database to an S3 bucket alongside local, NAS, OneDrive, or other supported destinations.
4. Encryption: By using just one Windows-based application, you can protect backup files with AES-256-GCM. No other person will be able to open them without the correct keys.
5. Compression: Managing large SQL Server databases can already eat up your storage space, but that doesn’t mean its backup file should too. Hence, this tool allows you to reduce backup file size where appropriate.
6. Retention management: Want to remove the backup from last week? This tool makes it possible. Now, with its retention settings, you can control how long backup copies are kept.
7. Backup history: After backing up the database to an S3 bucket, you can simply review previous backup activity and look for any failed jobs.
8. Restore support: Backup is only beneficial when it can be restored in the event of data loss incidents. So, you can keep backup and recovery within the same workflow.
9. Multiple database engines: Moreover, this software is useful if your environment includes databases beyond SQL Server. It supports multiple database engines and unlimited databases to back up.

Conclusion

By moving your SQL Server backups to an Amazon S3 bucket, you can simply reduce the dependency on the storage connected to the database server and have a different place to store your backup files.

It won’t be a smart choice to keep the primary database and the backup file on the same server.

Prapl SQL Backup Tool offers you a centralized method to configure the database and upload your .bak file to an S3 bucket for teams that want to backup SQL Server databases to S3 buckets without having to handle every step of the procedure manually.