Backing up to Amazon S3 bucket.

[alexsinkevich]

In SQL Server 2022 Microsoft finally created a way to backup directly to S3 bucket in AWS.

Below is the document describing the steps: https://aws.amazon.com/blogs/modernizing-with-aws/backup-sql-server-to-amazon-s3/

The backup command syntax is similar, but slightly different from backup to URL, like so: BACKUP DATABASE db1 TO URL = 's3://sql-backups-2022.s3.us-east-1.amazonaws.com/backups/db1.bak' WITH FORMAT, COMPRESSION, MAXTRANSFERSIZE = 20971520;

Is there a way to include this functionality into the solution? That would be a life changing modification, allowing AWS/SQL customers to save a ton of money by not keeping the volumes for the backups and not paying for them.

Please, @olahallengren ?

[mbrrg]

Found a PR for this! https://github.com/olahallengren/sql-server-maintenance-solution/pull/714

[dbaduck]

Do we know when this will be added? I have made modifications to my DatabaseBackup to allow S3 but would love to have it be natively there.

[MyKarpenko]

getting

Msg 50000, Level 16, State 1, Procedure dbo.DatabaseBackup, Line 2276 [Batch Start Line 36] The value for the parameter @URL is not supported.

Msg 50000, Level 16, State 3, Procedure dbo.DatabaseBackup, Line 2276 [Batch Start Line 36] The value for the parameter @Credential is not supported. if it is just about string format checking, the change should be pretty simple

[dbo].[DatabaseBackup]

---

IF EXISTS(SELECT * FROM @URLs WHERE Mirror = 0 AND DirectoryPath NOT LIKE 'https://%/%') BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @URL is not supported.', 16, 1 END

will become something like that IF EXISTS(SELECT * FROM @URLs WHERE Mirror = 0 AND (DirectoryPath NOT LIKE 'https://%/%') AND (DirectoryPath NOT LIKE 's3://%/%')

and now it works

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'test_S3_backup',
@URL = N's3://sql2022backuptest.s3.us-east-2.amazonaws.com/backups',
@Credential = 's3://sql2022backuptest.s3.us-east-2.amazonaws.com/backups',
@BackupType = 'FULL',
@Verify = 'Y',
@CheckSum = 'Y',
@LogToTable = 'Y'

[dbaduck]

If you look at the PR here, the instructions of the file changes are there. https://github.com/olahallengren/sql-server-maintenance-solution/pull/714

You don't need to use credential as the credential should be named like the s3:// url as per documentation.

[olahallengren]

I am finally working on this. I have created a pull request: https://github.com/olahallengren/sql-server-maintenance-solution/pull/822 Branch: https://github.com/olahallengren/sql-server-maintenance-solution/tree/feature-backup-aws-s3 Please do some testing.

[olahallengren]

This has been released to main now. https://ola.hallengren.com/versions.html