1

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
  • Can you please share the backup script or that's URL?? – Rajesh Ranjan Nov 14 '16 at 14:44
  • I have linked to the script and edited the question to show the SQL that I am running – Peuge Nov 14 '16 at 14:55
  • 1
    The @CleanupTime does not exist in your command...what did you try setting it to? – user507 Nov 14 '16 at 17:15
  • Oops, I had it set to 48. However in Ola's script I see the error is thrown in this if statement: IF CleanupTime < 0 OR (CleanupTime IS NOT NULL AND URL IS NOT NULL). Seems like it does not support setting a retention policy when using a URL – Peuge Nov 15 '16 at 5:03
2

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.

-1

I would recommend using XP_Delete_File as your solution.

Here is a link to the scripting and use of it. Link

Xp_delete_file takes five parameters:

1.File Type = 0 for backup files or 1 for report files.

2.Folder Path = The folder to delete files. The path must end with a backslash "\".

3.File Extension = This could be 'BAK' or 'TRN' or whatever you normally use.

4.Date = The cutoff date for what files need to be deleted.

5.Subfolder = 0 to ignore subfolders, 1 to delete files in subfolders.

Here is the sample script to loop through and delete backups.

DECLARE @name VARCHAR(50); -- Database name

DECLARE @path VARCHAR(256); -- Path for backup files 

DECLARE @fileName VARCHAR(256); -- Filename for backup 

DECLARE @fileDate VARCHAR(20); -- Used for file name

DECLARE @DeleteDate DATETIME = DATEADD(wk,-1,GETDATE()); -- Cutoff date




-- Path to backups.

SET @path = 'D:\Backup\';




-- Get date to include in file name.

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112);




-- Dynamically get each database on the server.

DECLARE db_cursor CURSOR FOR 

SELECT name 

FROM master.sys.databases

WHERE name NOT IN ('master','model','msdb','tempdb');




OPEN db_cursor;

FETCH NEXT FROM db_cursor INTO @name;




-- Loop through the list to backup each database.

WHILE @@FETCH_STATUS = 0

BEGIN  

      -- Build the path and file name.

      SET @fileName = @path + @name + '_' + @fileDate + '.BAK';

      -- Backup the database.

      BACKUP DATABASE @name TO DISK = @fileName WITH INIT;

      -- Loop to the next database.

      FETCH NEXT FROM db_cursor INTO @name;

END  




-- Purge old backup files from disk.

EXEC master.sys.xp_delete_file 0,@path,'BAK',@DeleteDate,0;




-- Clean up.

CLOSE db_cursor;

DEALLOCATE db_cursor;

GO
  • 2
    You might want to read that question one more time....xp_delete_file does not work against Azure blob storage. – user507 Nov 14 '16 at 17:11
  • Gotcha. I was treating it as regular maintenance. – bwilliamson Nov 14 '16 at 19:04

Your Answer

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

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