How to debug SQL Server 2014 managed backup on Azure?

Question

I hope to configure and use the SQL Server 2014 Managed Backup to Azure feature. I tested the manual backup of the T-SQL URL query and it worked. But backups created using Managed Backup for Azure feature do not complete.

The managed backup process leaves 0 bytes of backup files in the backup storage, and all of them are locked (active lease) due to an unsuccessful backup process. I am trying to understand what the problem is. My release of SQL Server 2014 is a web edition that does not seem to support compression. However, the SQL Server Management Studio dialog for configuring managed backup to Azure does not provide any compression options. So maybe he is trying to compress the backup file and fails? I don’t know how to see what SQL Server is actually trying to use, although it does an automatic backup on Azure. Does anyone have any idea?

I see the following errors in the event log ...

Date 10/1/2014 3:10:39 PM Log SQL Server (Current - 10/1/2014 2:42:00 PM) Source Backup Message BACKUP failed to complete the command BACKUP LOG CaseLaw. Check the backup application log for detailed messages.

Date 10/1/2014 3:12:01 PM Log SQL Server (Current - 10/1/2014 2:42:00 PM) Source spid71 Message Error: 18210, Severity: 16, State: 1.

Date 10/1/2014 3:12:01 PM Log SQL Server (Current - 10/1/2014 2:42:00 PM) Source spid71 Message BackupVirtualDeviceFile::DetermineFileSize: SetPosition(0,EOF) failure on backup device 'https://caselaw.blob.core.windows.net/sqldb01-mssqlserver/CaseLaw_d68e6c91feba4f7f891a66cb86fcce8d_20141001151038-05.log'. Operating system error Backup to URL received an exception from the remote endpoint. Exception Message: The remote server returned an error: (409) Conflict..

I can’t read the full 409 error message, so I don’t know what the actual 409 error is. According to MSDN, there are several possible 409 answers .

I struggled with this problem for several days. I tried to execute a manual scheduled task that runs my T-SQL query, which works, but I cannot automatically clear it after X days using this method. Any ideas? Thanks!

Answer 1

This is really related to installing COMPRESSION in the backup command that is invoked by the Managed Backup process.

The inclusion of debugging messages indicated a call to the stored procedure: [msdb]. [smart_admin]. [sp_do_backup]. If you look inside this stored procedure, you will see "COMPRESSION" hard-coded into the various backup commands that it issues.

Find and replace for "COMPRESSION" with "NO_COMPRESSION" should fix your problem.

Note that this only applies to SQL 2014 Web Edition, as other versions support compression, and this should not even occur.