May 7, 2008 at 9:18 am
Hi,
Does anyone know how to execute a script on database startup, e.g. when a database is attatched, restored or bought online?
This is different from server startup, i know how to do that.
When database TestDB is restored, i want it to automatically run, say 'TestProc' within it.
thanks
r
May 7, 2008 at 9:31 am
You could try to use a DDL trigger. I have never tried it but it should be possible.
[font="Verdana"]Markus Bohse[/font]
May 7, 2008 at 10:10 am
I've tried a trigger for the all encompassing 'DDL_DATABASE_EVENTS' but that didnt fire 🙁
May 7, 2008 at 10:40 am
r5d4 (5/7/2008)
I've tried a trigger for the all encompassing 'DDL_DATABASE_EVENTS' but that didnt fire 🙁
The DDL_DATABASE_EVENTS have a database scope, so this won't work. Unfortunately for you only CREATE_DATABASE is available as a Server scope event. So it won't work for restore or attaching a database.
CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS
--add your script here
GO
[font="Verdana"]Markus Bohse[/font]
May 7, 2008 at 11:30 am
This is in fact interesting. I created a DDL trigger for database create/alter/drop events.
create trigger trigddl
on all server
for CREATE_DATABASE, DROP_DATABASE, ALTER_DATABASE
as
SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
It is triggered with no probs when I create or drop database.
create database testdbd
--sp_detach_db testdbd
--sp_attach_db testdbd, 'c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\testdbd.mdf'
drop database testdbd
But when I attach it the trigger is not fired. And this proc (sp_attach_db) does nothing more than create database .. for attach.
create database testdbd on (filename='c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\testdbd.mdf')
for attach
...and your only reply is slàinte mhath
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply