How to Backup SQL Server to Amazon S3

SQL Server Backup to Amazon S3

SQL Server 2022 is a database that has been enabled for Azure and offers enhanced performance, improved security innovations, advanced analytics capabilities, and robust data governance features. Prior to SQL Server 2019, the only option for backing up databases directly to cloud storage was Microsoft Azure storage.

However, with the introduction of SQL Server 2022 (16.x), object storage integration with S3-compatible object storage has been made possible, in addition to Azure Storage. This version includes a new S3 connection that utilizes the S3 REST API to establish a connection with any S3-compatible storage provider.

In SQL Server 2022 Community Update 2.0, the ability to perform backups of a SQL Server database directly to an AWS S3 bucket is supported. Today, our focus will be on creating a SQL Server database backup and storing it in an AWS S3 bucket.

How to Backup SQL Server Backup to Amazon S3

The syntax of the BACKUP DATABASE command provides the ability to perform backups by specifying a drive, a network folder, or Azure storage as the destination. While it's not directly possible to set an Amazon S3 bucket as the backup destination, you can transfer the backup file to S3 using a batch script or a third-party utility.

Please note that in order to transfer backups to AWS S3, you will need to create access keys with the necessary privileges to write files to the bucket. These access keys will enable the required permissions for interacting with the S3 bucket.

Use .bat Script to Backup a SQL Server Database to Amazon S3

Any task that can be executed through the user interface can also be accomplished using operating system batch scripts. To create a script, two utilities are required.

The first utility is sqlcmd, which enables the execution of SQL queries to a SQL Server from the command line. It is automatically installed along with the SQL Server.

The second utility is AWS, which facilitates the interaction with Amazon Web Services through the command line. After installing the utility, you will need to run the AWS configure command and provide the access keys to configure it:

Below is a simple script to back up a SQL Server database to Amazon S3:

Save this file with the .bat extension. For example, to c:\Scripts\sql-backup-to-s3.bat, then run it to check if it works. 

The script generates the backup file name based on the current date, then creates a backup file using the Sqlcmd utility, and, because of the COMPRESSION option, will compress it on the go. Then, using the AWS cp command, it will transfer the backup to the S3 storage and then delete the local copy of the backup file.

How to Automate SQL Server Backup by Scheduling Script on Windows

To schedule regular backup script runs on Windows, you can utilize the Windows Task Scheduler. The Windows Task Scheduler provides a convenient way to schedule and automate tasks.

To open the Windows Task Scheduler, follow these steps:

1. Press the Windows key + R on your keyboard to open the Run dialog box.

2. Type taskschd.msc into the Run dialog box and press OK.

3. In the Task Scheduler window, click on Create Task in the Actions pane on the right.

4. Follow the steps of the wizard: 

☞ Configure the General settings:

• Provide a name and optional description for the task.
• Ensure the Run whether user is logged on or not option is selected.
• Check the Run with highest privileges option to ensure the script runs with administrative privileges if required.

☞ Configure the Triggers:

• Click on the Triggers tab and click New to add a new trigger.
• Select the desired schedule (e.g., daily, weekly, etc.) and set the start time and date.
• Choose any additional settings like recurring intervals and duration.

☞ Configure the Actions:

• Switch to the Actions tab and click New to add a new action.
• Select Start a program as the action type.
• Specify the path to the script you want to run in the Program/script field.

☞ Configure additional settings:

• Optionally, you can set conditions and add specific settings in the Conditions and Settings tabs based on your requirements.

By following these steps, you can schedule a regular backup script run on Windows using the Task Scheduler. The task will execute based on the specified schedule, ensuring automated SQL Server backups according to your desired intervals.

Summary

To automate this process, users can create a SQL Server backup script that specifies an S3 bucket as the target of the backup file. The Windows Task Scheduler can also be utilized to schedule the backup script to run periodically on Windows. By configuring the task scheduler, users can automate the backup process to ensure timely and consistent backups of SQL Server databases to Amazon S3.

In summary, utilizing Amazon S3 as a backup target for SQL Server provides an efficient, scalable data protection solution that enables organizations to effectively store and retrieve backups in the cloud.