A Server Trigger That Blocks Accidental Database Drops

A server trigger can block accidental database drops by stopping the DROP DATABASE statement before it finishes. It is a speed bump, not a vault. Backups and permissions still do the real protecting.

Why a drop needs a speed bump

Picture a Friday evening. Someone has two SSMS tabs open, one for test and one for production. They type DROP DATABASE, press F5, and notice the tab color a second too late. Nobody was careless on purpose. It is just a very easy mistake.

SQL Server lets you catch that moment. A server-level DDL trigger fires when a database is dropped. If the trigger rolls back, the drop never happens. Because it lives at the server level, it works for every database you name in it.

Before adding one, look at what already exists. A second trigger on the same event can surprise the next DBA. My test server shows one, tr_MScdc_db_ddl_event, which comes with change data capture. Yours may show none or several.

SELECT name, type_desc, is_disabled
FROM sys.server_triggers
ORDER BY name;

Build the guard for one database

This demo creates two server-level objects: a database named DropGuardDemo and a trigger named BlockDropDemo. It removes both at the end. The trigger reads the database name from EVENTDATA(). If the name matches, it raises an error and rolls back.

Keep the guard narrow. I compare against the exact name instead of blocking every drop. A trigger that blocks everything gets switched off in a week, and then you have no guard at all.

USE master;
GO
DROP TRIGGER IF EXISTS BlockDropDemo ON ALL SERVER;
DROP DATABASE IF EXISTS DropGuardDemo;
CREATE DATABASE DropGuardDemo;
GO
CREATE TRIGGER BlockDropDemo ON ALL SERVER
FOR DROP_DATABASE
AS
BEGIN
    DECLARE @Db sysname = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'sysname');

    IF @Db = N'DropGuardDemo'
    BEGIN
        RAISERROR('DropGuardDemo is protected. Disable BlockDropDemo first.', 16, 1);
        ROLLBACK;
    END;
END;
GO

Try to drop it

Now play the tired admin. The drop runs, the trigger fires, and the statement is cancelled. The first result shows error 50000, the number RAISERROR gives a custom message, plus the message itself. The second shows that DropGuardDemo is still there.

BEGIN TRY
    DROP DATABASE DropGuardDemo;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

SELECT name FROM sys.databases WHERE name = N'DropGuardDemo';

The error says what to do next, and that matters. A message like “Disable BlockDropDemo first” turns a block into a short, deliberate step.

Now check that other databases are not caught in the net. Create a second database and drop it. It goes quietly, because its name does not match.

CREATE DATABASE OtherDemo;
DROP DATABASE OtherDemo;

SELECT name FROM sys.databases WHERE name = N'OtherDemo';

The query returns no rows, so OtherDemo is gone. Only the protected name was stopped.

The escape route and the cleanup

Anyone with enough rights can disable or drop the trigger. That is by design. The guard stops mistakes, not people with a plan. For a real server, keep the trigger definition in source control and watch for changes to it.

This last block shows the deliberate path. Disable the trigger, drop the database, then remove the trigger. After it runs, the server is back to how it started.

DISABLE TRIGGER BlockDropDemo ON ALL SERVER;
DROP DATABASE DropGuardDemo;
DROP TRIGGER BlockDropDemo ON ALL SERVER;

SELECT
    (SELECT COUNT(*) FROM sys.databases WHERE name = N'DropGuardDemo') AS DatabasesLeft,
    (SELECT COUNT(*) FROM sys.server_triggers WHERE name = N'BlockDropDemo') AS TriggersLeft;

Test this on a non-production server first. And please keep your backups. A trigger will not save you from a drop that someone disables on purpose.

A small guard is cheap, so add it before the Friday evening you wish you had.

A drop guard is not a backup, it is one more chance to catch a mistake.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.