In some cases, you may receive the following error when upgrading from DacPac:
Database has drifted from its registered data-tier application
When SQLWATCH is deployed, it registers itself as data-tier application, which adds a record in
This is userful to identify currently installed version of SQLWATCH.
The error may happen when database objects have been changed since the last deployment, for example, when you deploy SQLWATCH into your existing "dba_tools" database and add or remove some other tables, not related to SQLWATCH.
To work around this issue, we have to unregister data-tier application. This can be done in SQL Server Management Studio:
Right click database -> Tasks -> Delete Data-Tier Application
Alternatively, you can use T-SQL to achieve the same:
declare @instance_id uniqueidentifierdeclare @database_name sysname = 'SQLWATCH'select @instance_id = instance_idfrom msdb.dbo.sysdac_instances dpwhere dp.instance_name = @database_nameexec dbo.sp_sysdac_delete_instance@instance_id = @instance_idexec dbo.sp_sysdac_update_history_entry@action_id=21,@instance_id=@instance_id,@action_type=14,@dac_object_type=0,@action_status=2,@dac_object_name_pretran=@database_name,@dac_object_name_posttran=@database_name,@sqlscript=NULL,@error_string=N''