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
selectname fromsys.databaseswherename like'%[_]201%';
select@cmd =N'alter database '+dbName+' set single_user with rollback immediate;drop database '+dbName+';'from@NameTable whereNumKey =@count;
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
Declare @message nvarchar(250);
Declare @data XML = EVENTDATA();
Declare @dbname sysname = @data.value('(/EVENT_INSTANCE/DatabaseName)','sysname');
IF @dbname not like '%201%'
set @message = 'Database Drop Call for database '+@dbname+' not permitted';