2

Can I do a SQL backup to AWS S3 URL from an EC2 machine I know that Azure side is possible but I do not know if it is possible AWS side thank you for your advice

1

3 Answers 3

1

it is possible with SQL Server 2022. here is the Amazon article on the subject: https://aws.amazon.com/blogs/modernizing-with-aws/backup-sql-server-to-amazon-s3/

2
  • 1
    Perhaps you could add the salient points from the article to the actual answer, lest it becomes useless once the link goes stale.
    – mustaccio
    Feb 6, 2023 at 21:02
  • Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    Feb 6, 2023 at 22:07
1

As Alexei mentioned, this is possible from SQL 2022. If you are on an older version of SQL Server I would recommend using File Gateway. Using a file gateway will simplify your backup and restore operations vs writing to local storage and using scripts to push the files to S3. Using command line tools to push large files to S3 can also be quite CPU intensive and will increase your overall backup time.

If you do need backups from S3 it can also be painful to have to pull them down to local storage to restore them.

File Gateway works as a file share that is backed by S3 storage. So you just write your backups to a UNC path and works with Ola's scripts. Restores also work directly from the File Gateway.

It does have a cost associated with it though. In our case, this was completely offset by reducing the amount of EBS storage we needed. We didn't need to duplicate our backup storage on the passive node. Also, you might not need quite as big a volume for the cache on the File Gateway.

0

No, at the moment there is no way to transfer backups directly to S3.

It seems to me that that option will not be added, since Microsoft and Amazon are competing in the cloud computing market. They are not interested in the mutual integration of products and services.

But in general, this is not a problem. Both SQL Server and AWS S3 support command-line interfaces, so you can write a simple backup script, or use a third-party utility.

To write a script, we need two utilities.

sqlcmd is a utility for interacting with SQL Server from the command line. It is installed with SQL Server.

aws is a command-line utility for interacting with Amazon Web Services. After downloading it, you need to configure it by running AWS configure. You will need to enter the access keys that you need to create in advance, the keys must have write access to the S3 bucket.

Here is a very simple script for backing up a SQL Server database to S3.

set day=%date:~7,2%
set month=%date:~4,2%
set year=%date:~-4%
set backup_name=%day%_%month%_%year%.bak

sqlcmd -Q "BACKUP DATABASE [AdventureWorks] TO  DISK = N'W:\temp\%backup_name%.bak'"

aws s3 cp W:\temp\%backup_name%.bak s3://my-backet-name/%backup_name%.bak

Note that the first three lines that receive parts of today's date to form the file name are guided by the position of the numbers in the %date% system variable, which depends on the regional settings.

To prevent your S3 storage from overflowing, configure in the backup bucket to automatically delete old files (or move them to AWS S3 Glacier)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.