Well it does relate to ghosts, in this case dbGhost, http://dbghost.com/ which is what this post is about.
Ghost creates databases on the fly, something I personally don’t like too much, which it then compares to a “base” database to produce release scripts. ( The brief description ).
As with all things sometimes all is not well and the server is left with a number of ghost created databases so I have to have a job to delete these every night before backups, it’s not difficult to code but for completeness here’s the script I use.
declare @cmd nvarchar(1256)='',@count tinyint=1;
declare@NameTable table(dbName sysname not null,NumKey tinyint not nullidentity(1,1));
IF(selectCOUNT(*)from master.sys.databaseswherename like'%[_]201%')>0
BEGIN
insert into@NameTable(dbName)
selectname fromsys.databaseswherename like'%[_]201%';
--
while@count<=(selectMAX(NumKey)from@NameTable)
BEGIN
select@cmd =N'alter database '+dbName+' set single_user with rollback immediate;drop database '+dbName+';'from@NameTable whereNumKey =@count;
exec(@cmd);
print@cmd;
set@count+=1;
END
END
However now and again when the ghost process dies it also manages to delete the the “base” database, it’s not a big deal but it’s just a little annoying, so I decided to implement a server trigger to stop the database being deleted – in fact it’ll stop any database being deleted which isn’t one of the ghost databases – or to be precise it will only drop databases with ‘201’ in the name.
dbGhost dbs are created with the name of the base database with an isodate appended e.g. myghostdatabase20110118
It’s a simple piece of code but if you’re in a similar situation hopefully you’ll find it helpful.
CREATE TRIGGER ddl_trig_databasedrop
ON ALL SERVER
FOR DROP_DATABASE
AS
Declare @message nvarchar(250);
Declare @data XML = EVENTDATA();
Declare @dbname sysname = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','sysname');
IF @dbname not like '%201%'
BEGIN
ROLLBACK
set @message = 'Database Drop Call for database '+@dbname+' not permitted';
PRINT @message;
END
GO