Knowlege base

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

Article ID: 857
Last updated: 01 Jul, 2021

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

Article ID: 857
Last updated: 01 Jul, 2021
Revision: 9
Views: 19
Print Export to PDF Subscribe Share
This article was:  
Prev   Next
Restoring an old backup (< 2017.Winter.1) could fail if the...     How to Apply Transparent Data Encryption (TDE) in SQL Server