In most SQL Server environments with fast moving data we want to ensure that we will suffer the least amount of data loss in an outage or disaster. This means using the FULL recovery model. There are some circumstances in which someone might wish to change the database to a SIMPLE mode in order to, for example, shrink a runaway transaction log.
We want to ensure that any time a database changes state away from the FULL recovery mode we have a policy in place which will log and highlight this so that we can correct it and fix the backup chain.
Ideally we would want an email alert to go out in this scenario and that is perfectly possible and something which I cover here
. However, this article will just cover the creation of the Policy itself.
Let’s get started. We will create a new Policy by expanding “Management” -> “Policy Management” and then right clicking “Policy” and choosing “New Policy…”
In the resulting window we will include a meaningful Name then select the Condition and choose “New Condition…”
This brings up the Condition window. As before, provide a meaningful name and then select the correct Facet. In this case both the Database and Database Option Facets contain a field of @RecoveryModel, however, only the Database Option Facet allows the option to report On Change: Log Only rather than having to rely on manual checks. Therefore we will choose this and fill the rest of the Condition in accordingly…
Note: This can be an “odd” interface and sometimes you need to click into different fields before it will allow the OK button to be checked.
You now have the basis for your Policy…
However, this isn’t quite right because there is a large flaw on display… SQL Server defaults the condition to “Every” database. Generally we wouldn’t want this condition applying to system databases and therefore we’ll exclude them. To do this, click the arrow next to “Every” and select “New Condition…”
We will now make a Condition called “User Databases” which uses the Database Facet (NOT Database Option) and @IsSystemObject field set to “false”. If you have replication and the distribution database on your server then you would be better off listing each database in turn, but in my example this isn’t the case, however I have shown the two options below:
Using a list of values
After creating the condition your Policy should look like this:
Change the evaluation mode to suit your needs (on demand or scheduled) and, if necessary, enable the Policy.
And you’re done. Now, if any user databases are changed from the FULL recovery model then you’ll be able to quickly tell and rectify it.