I implemented a policy on my production server to check if all databases are in full recovery model.
the 3 steps are:
- create condition , choose Database as facet, filter Recovery Model = Full
- Create policy, against all users database by database_id>4 and use the above condition, schedule a job runs weekly.
- enable it and evaluate. I noticed a few databases were highlighted not being Full recovery model, but it is required by the application as simple. I left it and will update the condition later, then forgot it.
The next week, I open this server is SSMS, I found a Script icon beside the server, panic, what is wrong with my SQL instance? google it, didn't find it, soon later, notice the script icon is the policy checker. Cool stuff.