Keep your database in safe mode

Hi Seniors DBA HI junior DBA HI DBA Consultant , There is no one without errors or defects if you agree with me Complete the post if not don’t waste your time !, How you can keep your database in safe Mode from any mistake can by happen by any accident ? we should put our database in the safe mode Under any circumstances ,so what you can do if you deleted your database by wrong ! and in the same time you lost your last backup for this database !! really it is very bad situation and may be not happened put may be happen also, to be calm and to Keep your database in safe mode you should take preventive action one of this preventive action Forcing the Drop database Command or Rename database to rollback and you can do it easily without more effort and without more maintenance cost by using DDL trigger ,  to know more about triggers and DML , DDL and Login triggers go for this link (CREATE TRIGGER (Transact-SQL)) Trigger can be created on tables , Databases and on All Server what we will do now Create trigger on all Server

Create Trigger 

IF EXISTS (SELECT * FROM sys.server_triggers
WHERE name = ‘KeepYourDBinSafeMode’)
DROP TRIGGER KeepYourDBinSafeMode
ON ALL SERVER;
GO
Create TRIGGER KeepYourDBinSafeMode
ON ALL SERVER
FOR Drop_Database ,Alter_Database
AS
PRINT ‘You must disable Trigger “KeepYourDBinSafeMode” to drop or alter Database!’
ROLLBACK
GO

Check the trigger from Mangment Studio 

Now create new database and try to drop it or rename it

use master
go
Create database DEMO
GO

—Drop Database
Drop Database DEMO

You must disable Trigger “KeepYourDBinSaveMode” to drop or alter Database!
Msg 3609, Level 16, State 2, Line 6
The transaction ended in the trigger. The batch has been aborted.

Rename Database

ALTER DATABASE DEMO MODIFY NAME = DEMO_New

The database name ‘DEMO_New’ has been set.
You must disable Trigger “KeepYourDBinSaveMode” to drop or alter Database!
Msg 3609, Level 16, State 2, Line 14
The transaction ended in the trigger. The batch has been aborted.

by this preventative action you can keep your database in safe mode but still we have Some gaps if you executed sp_renamedb

EXEC sp_renamedb ‘DEMO’, ‘DEMO_New’

at this time the trigger will not work because trigger working only for dropping database or alter database command but Microsoft says it will be phased out at some time Take care Do not rely on the trigger only you should take all the precautions.

Disable trigger

IF EXISTS (SELECT * FROM sys.server_triggers
WHERE name = ‘KeepYourDBinSafeMode’)
disable TRIGGER KeepYourDBinSaveMode
ON ALL SERVER;

Enable Trigger 

IF EXISTS (SELECT * FROM sys.server_triggers
WHERE name = ‘KeepYourDBinSafeMode’)
Enable TRIGGER KeepYourDBinSaveMode
ON ALL SERVER;

Follow me in the next post i will explain how you can do auditing on all your databases by using trigger.

Follow the author:

View all my tips , LinkedIn , Website , Slideshare