Database extended proprieties cause the backup tool to fail on Database Schema Check

Applies to

Imagicle Application Suite

Description

The backup fails on the Database Schema Check step, <StonevoiceAS>\Var\Log\BackupRestore\ApplicationSuite.log will report:

0814 14:50:09.852 ERROR { 1} [ApplicationSuite] [SvDataComUtils] An error occurred: Function {ExportDatabase}, { Exception Type {System.InvalidOperationException} Message {Database schema check failed} StackTrace { at ApplicationSuite.Data.Database.DatabaseBackupRestore.CheckSchema(FileSystemInfo dir) at ApplicationSuite.Data.Database.DatabaseBackupRestore.Backup(FileInfo backupFile, Boolean includeHistory) at ApplicationSuite.Data.Database.IasDatabase.Backup(FileInfo backupFile, Boolean includeHistory) at ApplicationSuite.Data.ComUtils.SvDataComUtils.<>c__DisplayClass3_0.<ExportDatabase>b__0(IIasDatabase database) at ApplicationSuite.Data.ComUtils.SvDataComUtils.Execute(String methodName, Action`1 action)} InnerException { Exception Type {Microsoft.SqlServer.Management.Common.ExecutionFailureException}

Message {An exception occurred while executing a Transact-SQL statement or batch.}

StackTrace { at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry) at ApplicationSuite.Data.Database.DatabaseBackupRestore.RestoreDbCreation(FileSystemInfo dir, String databaseName, String schemaVersion) at ApplicationSuite.Data.Database.DatabaseBackupRestore.CheckSchema(FileSystemInfo dir)} InnerException { Exception Type {System.Data.SqlClient.SqlException}

Message {Property cannot be added. Property 'Application' already exists for 'object specified'.}

StackTrace { at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException) at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)} } } }

Opening the file DbCreation in a subforder of the backup folder target, it is possible to find the usage of extended proprieties by an invocation of the sys.sp_addextendedproperty method

Cause

The schema of the DB is modified therefore not compliant to what the IAS expects, this causes the backup to fail

Solution

• Identify all the extended proprieties created by checking in the DbCreation file the usage of sys.sp_addextendedproperty, eg:

  EXEC sys.sp_addextendedproperty @name=N'Application', @value=N'Unified Comms' 
  GO

• As alternative, it's possible to run the following query to get them:

  USE model SELECT * FROM sys.extended_properties
  

• Prepare a query on the IAS database using an user with appropriate permissions to modify the IAS database schema and remove the extended proprieties, following the example above:
   

  USE <DB_NAME>
  EXEC sys.sp_dropextendedproperty @name=N'Application'
  GO
  

• Repeat the above query for every extended propriety found on the IAS database

• Run the backup again, this time it should succeed