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:
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:
CODEEXEC sys.sp_addextendedproperty @name=N'Application', @value=N'Unified Comms' GO
As alternative, it's possible to run the following query to get them:
CODEUSE 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:
CODEUSE <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