February 2011 - Posts

Ghost in the machine

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
Posted by GrumpyOldDBA with no comments
Filed under: ,