4

I did set up SQL Server managed backup for our databases. I did define retention days to 4, but i just checked the azure storage account where the managed backups are and found that there is still all the files from the day i did set up managed backup (2015/12/1).

SQL i used on every user database:

    Use msdb;
    GO
    EXEC smart_admin.sp_set_db_backup 
            @database_name='TestDB' 
            ,@retention_days=4 
            ,@credential_name='MyCredential'
            ,@encryption_algorithm ='AES_128'
            ,@encryptor_type= 'Certificate'
            ,@encryptor_name='MyBackupCert'
            ,@enable_backup=1; 

We are using SQL Server 2014 Enterprise with Azure VM.

Set up tutorials and information about the managed backups: MSDN

Is SQL server managed backup to Azure supposed to automatically delete backups after given retention days have passed or do i need to implement custom job for that?

2

I ended up making a Sql server agent job that runs this with powershell mode:

# Ps1-script to keep azure storageaccount for managed backups clean

Import-Module Azure

$storageAccount = "STORAGEACCOUNTNAME"
$storageKey = "STORAGEKEY"
$blobContainer = "CONTAINERNAME"

$CleanupTime = [DateTime]::UtcNow.AddDays(-4)
$context = New-AzureStorageContext -StorageAccountName $storageAccount -StorageAccountKey $storageKey
Get-AzureStorageBlob -Container $blobContainer -Context $context | 
Where-Object { $_.LastModified.UtcDateTime -lt $CleanupTime -and $_.BlobType -eq "PageBlob" -and $_.Name -like "*.bak"} |
Remove-AzureStorageBlob
$CleanupTime = [DateTime]::UtcNow.AddDays(-4)
Get-AzureStorageBlob -Container $blobContainer -Context $context | 
Where-Object { $_.LastModified.UtcDateTime -lt $CleanupTime -and $_.BlobType -eq "PageBlob" -and $_.Name -like "*.log"} |
Remove-AzureStorageBlob

This does delete all .bak and .log files older than 4 days. It does not however work on blobs that does have active lease on them.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy