Occassional failures backing up a database from Azure SQL

1

I am occassionally getting the following error message when backing up database on Azure SQL.

10:39:37 PM : Backing up “dbname” with “Full” backup type.
10:39:37 PM : Creating snapshot of “dbname” database to “dbname_SQLBackupAndFTP_Snapshot”.
10:40:07 PM : Backing up “dbname_SQLBackupAndFTP_Snapshot” with “Full” backup type.
10:41:51 PM : Drop “dbname_SQLBackupAndFTP_Snapshot”.
10:42:01 PM : ERROR: Failed to backup “dbname” database with “Full” backup type: Object reference not set to an instance of an object.

It’s not always on the same database - I have about 20 in the job and it’ll randomly occur on different ones, and not every time the backup job runs. I’ve got the option ticked to create a snapshot of the database when backing up.

2

Hi Chris_Hart,

To investigate this case we need more details. Could you please send us your Advanced Log? Here are more details on how to do it How to send Log to developers | SQLBackupAndFTP's blog

Please let us know when the log will be sent, also please provide us with your Application ID (“Help” > “About”) to identify the log.

Sorry for the inconvenience.

3

I’ve enabled the log and will check it when the job runs through tonight. My application ID is:
c1fd84a0-5924-42e6-985a-ca633a176f40

4

The issue occurred last night and I have just sent the advanced log to you.

5

Any update on this issue?

6

Hi Chris_Hart,

Thank you for your waiting.

We have checked the log you sent and noticed the following error messages:

2021-11-16 00:34:35.6886|ERROR|87|Pranas.NET.SqlBak.Job.Sql.Business.SqlServerHelper|BackupDatabaseCloud|System.NullReferenceException: Object reference not set to an instance of an object.
   at Microsoft.Data.Tools.Schema.Sql.SchemaModel.ReverseEngineerPopulators.Sql140GenericDatabaseScopedConfigurationOptionPopulator.CreateElement(ReverseEngineerOption reverseEngineerOptions, EventArgs args)
   at Microsoft.Data.Tools.Schema.Sql.SchemaModel.ReverseEngineerPopulators.TopLevelElementPopulator`1.Populate(SqlReverseEngineerRequest request, OrdinalSqlDataReader reader, ReverseEngineerOption option)
   at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlReverseEngineerImpl.ExecutePopulators(Tuple`2 connectionTuple, IList`1 populators, Int32 totalPopulatorsCount, Int32 startIndex, Boolean progressAlreadyUpdated, ReverseEngineerOption option, SqlReverseEngineerRequest request)
   at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlReverseEngineerImpl.ExecutePopulatorsInPass(SqlReverseEngineerConnectionContext context, ReverseEngineerOption option, SqlReverseEngineerRequest request, Int32 totalCount, Tuple`2[] populatorsArray)
.............................
............................

   at Microsoft.SqlServer.Dac.ExtractOperation.<>c__DisplayClass13.<Microsoft.SqlServer.Dac.IOperation.Run>b__12()
   at Microsoft.Data.Tools.Schema.Sql.Dac.OperationLogger.Capture(Action action)
   at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.OperationExtension.Execute(IOperation operation, DacLoggingContext loggingContext, CancellationToken cancellationToken)
   at Microsoft.SqlServer.Dac.DacServices.ExportBacpac(Func`1 streamGetter, String databaseName, DacExportOptions exportOptions, IEnumerable`1 tables, CancellationToken cancellationToken)
   at Microsoft.SqlServer.Dac.DacServices.ExportBacpac(String packageFileName, String databaseName, DacExportOptions options, IEnumerable`1 tables, Nullable`1 cancellationToken)
   at Microsoft.SqlServer.Dac.DacServices.ExportBacpac(String packageFileName, String databaseName, DacSchemaModelStorageType modelStorageType, IEnumerable`1 tables, Nullable`1 cancellationToken)
   at Pranas.NET.SqlBak.Job.Sql.Business.SqlServerHelper.<>c__DisplayClass16_3.<BackupDatabaseCloud>b__7()
   at Net.Pranas.SqlBak.Tools.Task.TaskTools.TryWhileTimeSpan(Action action, TimeSpan retryLimit, Func`2 retryValidation)
   at Pranas.NET.SqlBak.Job.Sql.Business.SqlServerHelper.<>c__DisplayClass16_0.<BackupDatabaseCloud>g__PerformBackup|3(String dbname, Boolean logStartBackup)
   at Pranas.NET.SqlBak.Job.Sql.Business.SqlServerHelper.BackupDatabaseCloud(IJob job, String database, String folder, JobRunInfo runInfo, DbSqlBackupType backupType, DateTime backupTime)
   at Pranas.NET.SqlBak.Job.Sql.Impl.DatabaseHandler.BackupDatabase(String path, String databaseName, IJobRunInfo runInfo, IDbBackupType backupType, DateTime backupTime)
   at Net.Pranas.SqlBak.Job.Core.BackupJob.BackupJobHandler.BackupDatabases(IJob job, IDatabaseHandler dbHandler, IJobInfo jobInfo, IJobRunInfo runInfo, JobRunParameters runParameters, WorkingFolders folders, JobRunReport runReport, DestinationBackupHelper[] destinations, ApplicationType applicationType, Dictionary`2 archiveFileNames, Nullable`1 customMaxFileSize, IDestinationHandler destinationHandler)

This message indicates that the error happens in the Microsoft libraries. We try to use always up-to-date and latest official Microsoft libraries in our product. We will update these libraries in the next release to the latest version, but the experience of similar cases suggests that it rarely helps. Sorry, but there is nothing we can do about it.

Thank you and sorry for the inconvenience.

7

There must be something you can do about this - I’m getting this every single day and if I can’t reliably backup my databases then this software isn’t going to work for me. Could you not at least make it retry on receiving that error? At the moment it just fails that database, but I’m pretty sure if you attempted it again (say for a maximum of 3 tries) it would work successfully as the error seems to be transient considering it’s not on the same database or time each day.

8

Hi Chris_Hart,

Thank you for your suggestion, but that is how SQLBackupAndFTP works, perhaps later we can consider if we can do something here.

Thank you and sorry for the inconvenience.