March 6, 2017 at 12:25 am
When the recovery model is changed the trigger below is fired: an error is raised but the action will be executed. Is a rollback possible with eventdata triggers ? I understood, that it should work. Now I am not sure if I have a mistake in the code or not. Thanks for your advice.
CREATE TRIGGER [TR_PreventDBChanges1]
ON ALL Server
FOR ALTER_Database
AS
DECLARE
@EventTime datetime
, @ServerName varchar(200)
, @DatabaseName varchar(200)
, @TSQL varchar(2000)
, @event XML
DECLARE @audit TABLE (
[DatabaseName] [varchar](200) NULL,
[TSQL] [varchar](2000) NULL
)
SELECT @event = EVENTDATA()
SELECT @DatabaseName = @event.value('(/EVENT_INSTANCE/DatabaseName)[1]','sysname' )
SELECT @TSQL = @event.value('(/EVENT_INSTANCE/TSQLCommand)[1]','varchar(2000)' )
INSERT @audit (DatabaseName, TSQL) SELECT @DatabaseName, @TSQL
IF (SELECT TSQL FROM @audit WHERE TSQL Like '%SET RECOVERY%') IS NOT NULL
BEGIN
RAISERROR ('Recovery model changes are not allowed',1,1)
ROLLBACK;
END
GO
March 6, 2017 at 5:14 am
According to this blog http://itsalljustelectrons.blogspot.ch/2016/05/SQL-Server-Alter-Database-Oddities.html there is not way to roll it back. The final part of the article is not clear. A recovery model chenge for a database member of an availability group won't work anyway as it has to be full recovery.
My test are done with a normal database.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply