Getting notifications when new Databases are created

Last Post 09 Nov 2011 11:57 PM by Eralper. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
itsonlyme4
New Member
New Member

--
28 Oct 2011 07:13 AM
I have 2 Applications (Sharepoint and Websense) which create their own Databases.  In the case of Sharepoint, there is some intervention on the part of the Sharepoint Administrator and with the other Application, the Databases are created by the Application without any human intervention.

What I struggle with is getting these newly created Databases added to my backup schedule in a timely manor.

What can I put in place to have SQL Server alert me when a new Database is created?
rm
New Member
New Member

--
28 Oct 2011 07:22 AM
If you backup dbs with maintenance plan, don't need do anything by choosing backup all user dbs. Plan will pick up new dbs and backup them.
itsonlyme4
New Member
New Member

--
28 Oct 2011 07:25 AM
We recently gone to a TO TAPE backup scenerio (via Netbackup) where there is manual intervention required to adjust the backup scripts when Databases are added or deleted.    Not an ideal situation in my opinion, but that is what we have done.
gunneyk
New Member
New Member

--
28 Oct 2011 10:06 AM
The easiest way is to probably create a DDL trigger that notifies you (via db mail etc.) when a db is created or dropped. Look in BOL under DDL Triggers for more details but have a look at one of the examples here for db creation:

CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS
PRINT 'Database Created.'
SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
GO
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO
itsonlyme4
New Member
New Member

--
28 Oct 2011 10:37 AM
Thank you kindly!!!
Eralper
New Member
New Member

--
09 Nov 2011 11:57 PM
Hello,
Within the DDL trigger, you can call procedure sp_send_dbmail ( http://www.kodyaz.com/articles/sql-...bmail.aspx ) to send email which will inform you about the database creation.
Please also check http://www.kodyaz.com/articles/prev...igger.aspx for more event data within DDL trigger that will give more details about the Create Database process
You are not authorized to post a reply.

Acceptable Use Policy