October 5, 2011 at 4:04 am
Using Policy Based Management, I am creating a policy to fine databases which have not had a FULL or Incremental Backup in the last 24hrs. This works OK but I need to ad a condition to the targets so that some databases (temporary copies etc) can be excluded.
I can do this for a single database setting a condition with the expression: @name != '<DatabaseName>', however I want to add a series of databases to this. I thought the way to do this would be to use the 'NOT IN' operator, However I can not format the Value field to accept multiple values
(Screenshots attached)
Please can anyone provide an example of how to format this
October 5, 2011 at 4:30 am
Hi,
You can utilize the following steps:
1. Create your conditions which you want to evaluate.
2. Create a second condition that makes use of the "Database" facet. In this condition, you should use @Name and the databases you want to exclude the databases.
3. Create your policy, you will see a drop down for databases which says "every". Drop this down and select your condition to do the exclusion of the names.
Execute the below script to see an example.
Declare @object_set_id int
EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N'test_ObjectSet_1', @facet=N'IDatabaseMaintenanceFacet', @object_set_id=@object_set_id OUTPUT
Select @object_set_id
Declare @target_set_id int
EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'test_ObjectSet_1', @type_skeleton=N'Server/Database', @type=N'DATABASE', @enabled=True, @target_set_id=@target_set_id OUTPUT
Select @target_set_id
EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'db_name', @target_set_level_id=0
GO
Declare @policy_id int
EXEC msdb.dbo.sp_syspolicy_add_policy @name=N'test', @condition_name=N'backup date', @policy_category=N'', @description=N'', @help_text=N'', @help_link=N'', @schedule_uid=N'00000000-0000-0000-0000-000000000000', @execution_mode=0, @is_enabled=False, @policy_id=@policy_id OUTPUT, @root_condition_name=N'', @object_set=N'test_ObjectSet_1'
Select @policy_id
GO
Unfortunately you have to specify multiple databases, as the check target does not seem to support the Array function which the NOT IN operator supports.
Don't hesitate to ask any other questions.
If you are interested in Policy Based Management, you can get a book called Pro: SQL Server 2008 Policy Based Management written by Ken Simmons.
Regards...
October 5, 2011 at 6:35 am
Thanks for your response, unfortunately I have only worked through the GUI so don't understand much of what is in your scripts as they don't appear to match what is created by the 'script' button within the GUI.
I do have two 'conditions', one for the test I wish to make and one for the databases I wish to exclude. These are referenced in a Policy as you describe. The problem I have is the 'Condition' for the databases I wish to exclude. Currently I have this set to use a 'not like' clause for a single string. When I script this it shows
Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'Exclude databases', @description=N'', @facet=N'Database', @expression=N'<Operator>
<TypeClass>Bool</TypeClass>
<OpType>NOT_LIKE</OpType>
<Count>2</Count>
<Attribute>
<TypeClass>String</TypeClass>
<Name>Name</Name>
</Attribute>
<Constant>
<TypeClass>String</TypeClass>
<ObjType>System.String</ObjType>
<Value>%AuditLog2%</Value>
</Constant>
</Operator>', @is_name_condition=4, @obj_name=N'%AuditLog2%', @condition_id=@condition_id OUTPUT
Select @condition_id
GO
I can not make out how to change this for a 'NOT IN' condition with an array of database names, which is what I am trying to achieve.
January 10, 2013 at 5:25 am
Use Array function in the Value filed of the condition.
Eg:
Array("master", "model", "msdb")
That works for me.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply