################################################################################# Created On: 12/15/2015# Created By: Nate Swift nate.swift@live.com# This script is as is and not supported# Does not assume any risk of data loss# Use it at your own risk# Links: http://stackoverflow.com/questions/17950517/how-to-delete-old-files-in-azure-container# Links: http://blogs.msdn.com/b/sqlosteam/archive/2013/03/12/sql-server-backup-to-cloud-managing-interrupted-backups.aspx# Additional Script Used: https://gallery.technet.microsoft.com/scriptcenter/How-to-break-the-locked-c2cd6492################################################################################import-module Azure## Global Variables# SubscriptionName$subname = "Your Azure Subscription Name"# Storage Credentials$stracctname = "Your Storgae Account Name"$stracctkey = "Your Storage Account Key"$context = New-AzureStorageContext -StorageAccountName $stracctname -StorageAccountKey $stracctkey# Container variables$contsqllogs = "Your Container Name where backups are stored"$contsqldb = "Your Container Name where backups are stored"# Date Filter variable anything older from 7 days$isOldDate = [DateTime]::UtcNow.AddDays(-7)#Get the current date and time.$DateTime=Get-Date $filedate = Get-Date -format "MMdyyyyHMs"#Change the path of log file to a desired location below$LogFilePath = "C:\Scripts\Output\"#Build the log file name. Do not update this variable$LogFile = $LogFilePath + "CleanUpSQLAzureBKPs" + $filedate + ".log"Echo "Script Starting: $DateTime" | Out-File $LogFile -Append## Future part of script will also use a stored windows credential to log on instead of of calling a generic # Connecting to Azure Instance# $cred = Get-Credential# Add-AzureAccount -Credential $cred# Select-subscription -Name $subname## Future script function using another script to release Lease Locks on blobs# releasing the Leases on locked Blobs in sql-logs and sql-db# $blobs = Get-AzureStorageBlob -Container $contsqllogs -Context $context | Where-Object { $_.LastModified.UtcDateTime -lt $isOldDate -and $_.BlobType -eq "PageBlob" }#Echo "!!!!! Searching storage account name in container name to release Locks" | Out-File $LogFile -Append#foreach($blob in $blobs){## $blobdatemod = $blob.LastModified.UtcDateTime# $blobname = $blob.Name## C:\scripts\BreakBlobLease .\BreakBlobLease.ps1 -StorageAccountName $stracctname -ContainerName $contsqllogs -BlobName $blobname#}# Removing the SQL Logs that are older than 7 days$blobs = Get-AzureStorageBlob -Container $contsqllogs -Context $context | Where-Object { $_.LastModified.UtcDateTime -lt $isOldDate -and $_.BlobType -eq "PageBlob" }Echo "!!!!! Searching Your storage account name in container name" | Out-File $LogFile -Appendforeach($blob in $blobs){ $blobdatemod = $blob.LastModified.UtcDateTime $blobname = $blob.Name Remove-AzureStorageBlob -Blob $blobname -Container $contsqllogs -Context $context Echo "Removing Azure Blob Storage File $blobname created on $blobdatemod" | Out-File $LogFile -Append}$blobs = Get-AzureStorageBlob -Container $contsqldb -Context $context | Where-Object { $_.LastModified.UtcDateTime -lt $isOldDate -and $_.BlobType -eq "PageBlob" }Echo "!!!!! Searching Your storage account name in container name" | Out-File $LogFile -Appendforeach($blob in $blobs){ $blobdatemod = $blob.LastModified.UtcDateTime $blobname = $blob.Name Remove-AzureStorageBlob -Blob $blobname -Container $contsqldb -Context $context Echo "Removing Azure Blob Storage File $blobname created on $blobdatemod" | Out-File $LogFile -Append}#Get the current date and time.$DateTime=Get-Date Echo "Script Ending: $DateTime" | Out-File $LogFile -Append# Send a notification EmailSend-MailMessage -from "NAME " -to "NAME " -subject "Remove Older Azure SQL Backups" -body "Removing older than 7 days old SQL Log Backups in Azure Storage" -Attachments $LogFile -smtpServer SMTPSERVER