Set Retention for SQL Backup to URL

ola-hallengrensql server

I am using Ola Hallengrens DatabaseBackup script. I am storing my backups in Azure Blob storage using the @url parameter. Everything is working fine, however I am looking for a way to set a retention policy on those backups – currently I assume they live there forever until I manually delete them. I tried using the @CleanupTime parameter but got the following error

The value for the parameter @CleanupTime is not supported

Is there any way I am able to set this?

Edit: SQL being run for the backup

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d DBATools -Q \ 
"EXECUTE [dbo].[DatabaseBackup] \ 
@Databases = 'USER_DATABASES', \ 
@BackupType = 'LOG', \ 
@Verify = 'Y',  \
@Compress = 'Y', \ 
@CheckSum = 'Y', \ 
@LogToTable = 'Y', \ 
@ChangeBackupType='Y', \
@url='https://example.blob.core.windows.net/backups', \ 
Credential='AzureBlobBackups'" -b

Best Answer

In order to clean up files in Azure blob storage requires PowerShell or going through some app like Azure Explorer. So it is likely that Ola's backup script does not support it since there is no native T-SQL command or procedure to access blob storage in that manner.

When you are deleting files you will need to account for any that may still have an active lease. BOL has code to handle dropping the active leases.

Related Solutions

Sql-server – Sql Server Backup to URL (Azure) ERROR

You might have an issue with your credentials. I recently used the following command successfully to back up to SQL Azure:

BACKUP DATABASE [MyDatabaseName] TO URL = N'https://mystorageaccount.blob.core.windows.net/mycontainer/MyDatabaseName_backup_2014_12_19_124300_8473463.bak' 
WITH CREDENTIAL = N'MyCredentialName', 
    NOFORMAT, NOINIT, 
    NAME = N'MyDatabaseName_backup_2014_12_19_124300_8473463',
    SKIP, REWIND, NOUNLOAD, 
    COMPRESSION, STATS = 10;

The credential was set up with MyCredentialName as the 'Name', the storage account name as the 'Identity', and primary or secondary access key as the 'Password'.

I ran into problems with the credential originally because I was trying to use the credential format supported for using Azure storage as the file location for the database (i.e. url prefix for 'Name', "SHARED ACCESS SIGNATURE" for Identity, and everything after the "?" from shared access signature url for the password), and that apparently does not work with the BACKUP command.

SQL Server – Implement Ola Hallengren Backup Plan for Different Databases

No you don't need to install the Ola's solution again since the initially installed commandexecute.sql and databasebackup.sql would have been saved on master DB as SP.

You just need to create a new job , as one created early but this time for 5 left over DB;s and schedule it weekly per you're timings.

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',-- Name of those 5 DB's would come here
@Directory = 'C:\Backup',-- You're backup path
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y'

enter image description here You can use above script while creating the T-SQL job and schedule it accordingly.

More details on how to do above refer to schedule job

Also, just to make sure both, initial backup and this newly created backup job not to run on same time you can put a condition or per the analysis when you're daily backup job completes, you can schedule this new weekly/nightly backup job for remaining 5 DB's

Best Answer

Related Solutions

Sql-server – Sql Server Backup to URL (Azure) ERROR

SQL Server – Implement Ola Hallengren Backup Plan for Different Databases

Related Question