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

Question

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

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

SSingh-MSFT 16,371 Reputation points Moderator

2023-02-13T05:54:26.8033333+00:00

Hi @Nikhil Verma ,

Following up to see if the below answer and comments were helpful. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.

Accepted answer

2 additional answers

Your answer

SSingh-MSFT 16,371 Reputation points Moderator

2023-02-13T05:54:26.8033333+00:00

Hi @Nikhil Verma ,

Following up to see if the below answer and comments were helpful. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.

Answer 1

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

Answer 2

Nikhil Verma 40

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.

Dan Guzman 9,406

The ALTER DATABASE trigger below works for me. Can you try it?

CREATE OR ALTER TRIGGER srv_ddl_trigger_alter_database   
ON ALL SERVER   
FOR ALTER_DATABASE   
AS
	--check the recovery model of 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, 'Databases must use FULL recovery model',1;
	END;
GO

Nikhil Verma 40 Reputation points

2023-02-09T15:04:17.4733333+00:00

Hi , It does throw error one the ALTER DATBASE command gets fired internally but still the database is getting created with SIMPLE recovery model unfortunately. Can you please elaborate how did you tried this ?
Dan Guzman 9,406 Reputation points

2023-02-09T18:38:47.9866667+00:00

I assumed the ALTER DATABASE was reverted after the error was raised but, like you, see the recovery model is still changed even after the THROW. This behavior looks to be specific to the recovery model change to SIMPLE (maybe because the log has been truncated at that point). My testing shows other changes are rolled back as expected.
Nikhil Verma 40 Reputation points

2023-02-09T23:04:27.1+00:00

Dan , Any specific steps you want me to follow ? I am basically enabling this policy and then trying to create the database from the SSMS ( by selecting SIMPLE recovery model) . In my head the database should be created but the recovery model should be set to FULL . :(
Nikhil Verma 40 Reputation points

2023-02-10T09:20:29.9766667+00:00

Below is happening in my case ,
Dan Guzman 9,406 Reputation points

2023-02-10T16:49:26.3733333+00:00

The CREATE DATABASE trigger will prevent creation of new databases with SIMPLE recovery (because the model database is SIMPLE recovery) but the ALTER DATABASE trigger cannot prevent a change to SIMPLE recovery after creation. SQL Server MI does enforce the FULL recovery model rule you want automatically and there is a system configuration option to suppress the error message of desired.

I don't believe there is a way to prevent the recovery model change but you could change it back to FULL afterwards perhaps using a SQL Agent job.
Erland Sommarskog 122.7K Reputation points MVP Volunteer Moderator

2023-02-10T22:01:13.5666667+00:00

I guess the DDL trigger can put the database back into full recovery. But since the log chain has been broken, the damage has already occurred.

This is a good argument for why there should be INSTEAD OF DDL triggers!
Dan Guzman 9,406 Reputation points

2023-02-12T11:10:37.3433333+00:00

Unfortunately, attempting to change the recovery model to FULL in the DDL trigger results in error "ALTER DATABASE statement not allowed within multi-statement transaction."
Erland Sommarskog 122.7K Reputation points MVP Volunteer Moderator

2023-02-12T11:20:17.8266667+00:00

You're right. That's what I get for not testing. And not thinking. Hmm... You could set up an event notification on ALTER DATABASE, and the activation on procedure on the other end would revert the recovery model. Not that very many people use event notifications...

(I think it is still possible to do it in the trigger by committing the transaction, alter the database, and start a new transaction. The latter to avoid an error message when the trigger exits. But this absolutely beyond what we can call "supported".)
Dan Guzman 9,406 Reputation points

2023-02-12T14:25:01.81+00:00

I think it is still possible to do it in the trigger by committing the transaction, alter the database, and start a new transaction.

That's thinking outside the box ;-)

You are right it is possible but should probably be avoided. I'd personally schedule a SQL Agent job for this need rather than event notifications due to the Service Broker dependency and additional complexity.

Dan Guzman 9,406

This is the script I suggest scheduling in a SQL Server Agent job.

DECLARE @SQL nvarchar(MAX) = (
	SELECT
		STRING_AGG(N'ALTER DATABASE ' + QUOTENAME(name) + N' SET RECOVERY FULL', N';') 
		FROM sys.databases
		WHERE name NOT IN('master', 'model', 'msdb', N'tempdb') --excluded other databases as desired
		AND recovery_model_desc <> 'FULL'
	);
EXEC sp_executesql @SQL;

Erland Sommarskog 122.7K Reputation points MVP Volunteer Moderator

2023-02-12T16:04:53.57+00:00

Nah, I think you need to proof-read your script a little bit.

And, yeah, unless you are already using event notifications, Agent is to prefer. Then again, why have an Agent job that most of the time will do nothing at all?
Dan Guzman 9,406 Reputation points

2023-02-12T17:20:39.0533333+00:00

I think you need to proof-read your script a little bit.

Thanks for the catch. I copied a similar script I use to changing the recovery model to SIMPLE and forgot to change it to FULL for this use case. I corrected the script in my comment.
Erland Sommarskog 122.7K Reputation points MVP Volunteer Moderator

2023-02-12T18:41:27.85+00:00

Looks better now. :-)

Answer 3

Nikhil Verma 40

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

Dan Guzman 9,406 Reputation points

2023-02-13T14:22:11.16+00:00
I would be wary of a DDL trigger approach because it's delving into unsupported/undocumented territory. I could not find any MS documentation that explains why the DDL trigger ROLLBACK doesn't actually rollback the ALTER DATABASE change as expected. That being said, you can proceed at your own risk with the trigger workaround if you choose but be mindful of what Erland mentioned: this absolutely beyond what we can call "supported". Below are code changes I suggest: a single IF block, adding wildcards to the LIKE criteria, and adding the missing END as the last statement in the trigger.

IF @DDLTEXT LIKE ('%SET%RECOVERY%SIMPLE%') BEGIN ROLLBACK; SET @TEXT = ' ALTER DATABASE ' + QUOTENAME(@DBNAME) + ' SET RECOVERY FULL;'; EXEC sp_executesql @TEXT; THROW 50000, 'xxx', 1; END; END;
Nikhil Verma 40 Reputation points

2023-02-13T15:15:36.4733333+00:00

Thanks Dan . I believe there is no "formal" or I would say a secured \ documented approach to perform this operation unfortunately . My main take from this discussion is to get a most realistic solution which can be automated on SQL instance and require least manual intervention .

SQL job in this case would run on a provided schedule , lets say the continuous data load ( ELT system ) is runinng on DB and then if this job runs in the background , what repercussions would it have ? Will be job time out or roll back the existing sessions on the database ? Any danger with inconsistent state of database ?

Would like to hear from you guys !
Dan Guzman 9,406 Reputation points

2023-02-13T17:17:06.5166667+00:00

I can't think of a risk to existing sessions when changing the recovery model. Be aware that the FULL recovery model will not be effective a database backup is performed. Of course, you'll need to also perform regular transaction log backups afterwards and size the log large enough to accommodate activity between log backups.
Erland Sommarskog 122.7K Reputation points MVP Volunteer Moderator

2023-02-13T22:00:01.1+00:00

. I could not find any MS documentation that explains why the DDL trigger ROLLBACK doesn't actually rollback the ALTER DATABASE change as expected.

Isn't the fact that ALTER DATABASE may not be in a user-defined transaction reason enough?

I guess that part of the picture is that we are changing the setting for the transaction log itself. And apparently it takes effect immediately, not when any transaction commits.

Good point about the need to take a full backup. The trigger would have to do that.

When I think of it, I think the best course of action is to have the trigger to send a mail with the instructions to revert. There can be good reasons for setting the recovery to simple. I think it should be a manual decision to change it.

Share via

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

2 additional answers

Your answer