Here’s some quick SQL logic to create a DDL Trigger which is fired whenever a new database is created. This isn’t too terribly hard to setup, but doesn’t hurt to put a post out there for you guys. This is incredibly useful whenever you’re setting up a new SQL Server environment, and you want to make sure that all the databases are configured within a uniform standard.
In this case… I have a trigger executing a Job simply called ‘SET DATABASE CONFIGS’, and within this job there is some SQL logic that sets recovery models etc. To take it a step further; my job will also notify the database group whenever new databases are added to this server environment.
Here’s the Trigger logic:
use master; set nocount on if exists(select 1 from sys.server_triggers where name = 'ddl_trig_run_job_on_create_database') drop trigger ddl_trig_run_job_on_create_database on all server go create trigger ddl_trig_run_job_on_create_database on all server for create_database as declare @new_database varchar(255) set @new_database = cast(eventdata().query('/event_instance/databasename/text()') as NVarchar(128)) exec msdb..sp_start_job @job_name = 'SET DATABASE CONFIGS'; go
Categories: Agent Jobs, Alerts, AlwaysOn, Automations, Backup & Restore, Big Data, Cluster, Command Prompt, Configuration, Database Mail, Database Mirroring, ELI5, Error Solutions, Failover, Failover Cluster, Featured, Folder System, Forensics & Auditing, High Availability, How To, Installations, Long Scripts, Management Studio, Mirroring, Notification, Powershell, Quick Fix, Scripts, Security, Short Scripts, SQL Licensing, SQL Stuff, Top, TSQL, Work Arounds