By employing SQL Server's backup functionality and integrating it with Amazon S3, organizations can achieve robust data protection, enhanced disaster recovery capabilities, and seamless scalability in managing their SQL Server backups.
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.
This part will introduce 3 ways to backup SQL databases to Amazon S3. Whether you're a novice or an expert, you can find answers here.
✨Tips: 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.
If you are tired of complicated and error-prone scripts and looking for a user-friendly and professional tool to perform SQL Server backup to S3, AOMEI Cyber Backup is the ideal choice.
AOMEI Cyber Backup, with its robust features and seamless integration with Amazon S3, provides a secure, reliable, and cost-effective solution for archiving SQL Server backups. By leveraging the strengths of AWS's cloud infrastructure, AOMEI Cyber Backup ensures your critical SQL Server data is protected, easily retrievable, and cost-efficiently stored, making it an ideal choice for businesses of all sizes.
🌟Benefits of Using AOMEI Cyber Backup with Amazon S3 for SQL Server: Scalability: Amazon S3 provides virtually unlimited storage capacity, accommodating the growth of your SQL Server databases over time. Durability and Availability: AWS offers high durability and availability, ensuring your data is safe and accessible when needed. Efficient Cost Management: By selecting the appropriate S3 storage class, you can manage storage costs effectively, paying only for what you use.
📢Steps to backup SQL Server to 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.
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.
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:
☞ Configure the Triggers:
☞ Configure the Actions:
☞ Configure additional settings:
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.
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.