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

Description

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

CODE

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:
CODE
```
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:
CODE
```
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:
CODE
```
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