Backups are not deleting when using encryption

kenneyh

I've recently noticed that my backups have not been deleting at all, which has caused multiple space issues (which is how I noticed the problem). At first I thought maybe it was because I had changed the backups to be deleted before the new backup instead of the default of after, but that wasn't the problem it didn't work either way. I keep trying things but nothing seemed to be the problem. I finally went into the log file and grabbed the command that performed the deletion and noticed something interesting. Here is the line of code stripped of the error handling and with my domain info replaced with "xx".

EXECUTE [master].dbo.sqbutility 1032, N'ReportServer$CERNERNEW', N'\xx.comxxDBBUCERNER$CERNERNEWReportServer$CERNERNEWLOG', 'L', '331h', 'FILE:\xx.comxxDBBUSQLBUPassword.txt'

What I noticed is that it is being passed the password file for the encryption and the ability to use a password file instead of the password is a very new feature. So I replaced that part of the code with the actual password and it deleted the old backups.

EXECUTE [master].dbo.sqbutility 1032, N'ReportServer$CERNERNEW', N'\xx.comxxDBBUCERNER$CERNERNEWReportServer$CERNERNEWLOG', 'L', '331h', 'MySecureEncryptionPassword'

So I can deduce from this that the sqbutility was not updated to except password files and it also doesn't return an error if it fails to decrypt the backup. This is two oversights that I hope RedGate fixes soon.

I am running version 7.7.0.7.

petey

Thank you for reporting the bug. The standalone function did not support file-based passwords.

This has now been fixed in build 7.7.1.17, which you can download here. Please note that this is a patch release, and has not been subjected to exhaustive regression tests.

With regards to your second point on SQL Backup not returning an error when it fails to decrypt a backup file, it is something we cannot support. It is a common situation where backups from multiple databases are stored in the same folder, some encrypted, some not. Not all backups files may have been encrypted using the same password. Also, over time, users may change the backup passwords. Thus, the deletion process only processes files it can decrypt. For every other file that it cannot decrypt, it cannot tell which database the backup file belongs to. It would be pointless to raise an error for every file that it cannot read.

What you could do is modify the SQL Agent job to raise an error if no backup files are deleted. There are 2 output parameters supported by sqbutility 1032, an integer value indicating the number of backup files deleted and a error message e.g.

DECLARE @count int
DECLARE @error varchar(1024)
EXEC master..sqbutility 1032, 'db01', 'e:	empackups', 'D',  '5b', 'file:e:	empackupspassword.txt', @count OUT, @error OUT

You could raise an error if the @count value is 0, if you expect at least 1 file to be deleted every time the job runs.

kenneyh

Peter,

Thank you for the quick turn-around on a fix. I finally got a chance to try it out and it appears to be working properly. I'll reply back if it displays any unwanted behaviors.

I see what you mean about reporting errors. I like your workaround for the issue. I'm using Ola Hallengren's maintenance scripts and I'll have to weigh out the benefits of getting an error back when no files are deleted compared to the overhead of modifying his code every time there is an update.