Possible to enforce recovery model (to FULL ) at the time of DB creation in SQL 2019 ( SQL VM in Azure) . Choosing another recovery model should throw error

Nikhil Verma 40 Reputation points
2023-02-09T09:37:04.49+00:00

Possible to enforce recovery model (to FULL ) at the time of DB creation . Choosing another recovery model should throw error Possible to enforce recovery model (to FULL ) at the time of DB creation in SQL 2019 ( SQL VM in Azure) . Choosing another recovery model should throw error

Azure SQL Database
{count} votes

Accepted answer
  1. Dan Guzman 9,406 Reputation points
    2023-02-09T11:21:36.2466667+00:00

    A server level DDL trigger can check the recovery model of a newly-created database and rollback when not FULL. Below is an example trigger that uses THROW to rollback the statement with an error message.

    CREATE TRIGGER srv_ddl_trigger_create_database   
    ON ALL SERVER   
    FOR CREATE_DATABASE   
    AS
    	--check the recovery model of new database and rollback if not FULL
    	IF EXISTS(
    		SELECT 1
    		FROM sys.databases 
    		WHERE name = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','sysname')
    		AND recovery_model_desc <> 'FULL')
    	BEGIN
    		THROW 50000, 'New databases must be created with FULL recovery model',1;
    	END;
    GO
    
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Nikhil Verma 40 Reputation points
    2023-02-09T13:59:23.96+00:00

    Thanks for the reply but the said command doesnt work .When the database gets created , initially the create database gets fired and it gets created . Post then ALTER_DATABASE command runs which tries to alter the recovery model .

    I even used your code on ALTER_DATABASE and tried to rollback the command . But that doesnt work either. The database gets created with the error which is mentioned in the trigger and post then it gets SIMPLE recovery model as well.


  2. Nikhil Verma 40 Reputation points
    2023-02-13T08:01:43.09+00:00

    You guys see any challenge with the below code ? Please do let me know . This code atleast worked and helps reverting the recovery model . What difference would that make it put Alter database in a DDL trigger compared to scheduling the same in SQL agent . I assume that DDL trigger would be an instant reversal of the recovery model rather than depending on a schedule to scan and run .

    In any case , the ALTER DATABASE will do its job and then the recovery model needs to be reset again ( back to FULL from simple or Bulk logged .. ) .. At least this was my observation in this whole episode..

    Thanks a lot in advance guys..

    create trigger srv_trg_db_creation on ALL SERVER for ALTER_DATABASE

    as

    BEGIN

    DECLARE @DDLText VARCHAR(MAX)

    DECLARE @DBNAME VARCHAR(MAX)

    DECLARE @TEXT NVARCHAR(MAX)

    SET @DDLText = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','NVARCHAR(MAX)'))

    SET @DBNAME = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','sysname'))

    IF @DDLTEXT LIKE ('%SET RECOVERY SIMPLE%')

    rollback;

    IF @DDLTEXT LIKE ('%SET RECOVERY SIMPLE%') begin

    SET @TEXT = ' ALTER DATABASE ' + @DBNAME + ' SET RECOVERY FULL '

    EXEC SP_EXECUTESQL @TEXT ;

    THROW 50000, 'xxx',1;

    end


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.