Unable to create table. Invalid object name 'master.dbo.DDLevents' when creating a new object

Question

I am using SQL Server 2008. I restored a database backup. Select query is working fine but when I try to create a new Table, Stored Procedure or View it displaying the following error screens.

Other databases work good. Only this new database has these issues.

Is this might be defective backup?

Any help would be appreciated.

Answer 1

look for any triggers under YourDBname -> Programmability -> Database Triggers. Delete them if not required and try creating the table. This happens for enabling audit trails in your previous installation of MSSQL from where you migrated the data.

Answer 2

It might as well be that you are missing the mentioned table in the master.dboi schema. Check the following link for details: https://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/

Answer 3

The main reason is that master is not calibrated for additional load: it is not installed on IO system with proper capacity planning, is hard to move around to new IO location, it's maintenance plan takes backups and log backups are as frequent as needed for a very low volume of activity, its initial size and growth rate are planned as if no changes are expected. Another reason against it is that many troubleshooting scenarios you would want a copy of the database to inspect, but you'd have to attach a new master to your instance. These are the main reasons why adding objects to master is discouraged. Also many admins understandably prefer an application to use it's own database so it can be properly accounted for, and ultimately easily uninstalled.

Similar problems exist for msdb, but if push comes to shove it would be better to store app data in msdb rather than master since the former is an ordinary database (despite widespread believe that is system, is actually not).