Sample policy and conditions

PBM-Test-Policy-Condition.sql

/* All Conditions */
DECLARE @condition_id INT
EXEC msdb.dbo.sp_syspolicy_add_condition
	@name=N'AutoShrink', 
	@description=N'Verify AutoShrink is disabled', 
	@facet=N'Database', 
	@expression=N'<Operator>
  <TypeClass>Bool</TypeClass>
  <OpType>EQ</OpType>
  <Count>2</Count>
  <Attribute>
    <TypeClass>Bool</TypeClass>
    <Name>AutoShrink</Name>
  </Attribute>
  <Function>
    <TypeClass>Bool</TypeClass>
    <FunctionType>False</FunctionType>
    <ReturnType>Bool</ReturnType>
    <Count>0</Count>
  </Function>
</Operator>', 
	@is_name_condition=0, 
	@obj_name=N'', 
	@condition_id=@condition_id OUTPUT
SELECT @condition_id
GO

DECLARE @condition_id INT
EXEC msdb.dbo.sp_syspolicy_add_condition
	@name=N'AutoClose', 
	@description=N'Check if AutoClose is disabled.',
	@facet=N'Database', 
	@expression=N'<Operator>
  <TypeClass>Bool</TypeClass>
  <OpType>EQ</OpType>
  <Count>2</Count>
  <Attribute>
    <TypeClass>Bool</TypeClass>
    <Name>AutoClose</Name>
  </Attribute>
  <Function>
    <TypeClass>Bool</TypeClass>
    <FunctionType>False</FunctionType>
    <ReturnType>Bool</ReturnType>
    <Count>0</Count>
  </Function>
</Operator>',
	@is_name_condition=0,
	@obj_name=N'',
	@condition_id=@condition_id OUTPUT
SELECT @condition_id
GO

/* Policies */
---------------------------
------AutoShrink-Scheduled
---------------------------

-- Create schedule
DECLARE @scheduleUID UNIQUEIDENTIFIER
EXEC msdb.dbo.sp_add_schedule
	@schedule_name = 'PBM-AutoShrink-Scheduled',
	@enabled = 1,
	@freq_type = 4,
	@freq_interval = 1,
	@schedule_uid = @scheduleUID OUTPUT;
SELECT @scheduleUID;

DECLARE @object_set_id INT
EXEC msdb.dbo.sp_syspolicy_add_object_set
	@object_set_name=N'AutoShrink-Scheduled_ObjectSet',
	@facet=N'Database',
	@object_set_id=@object_set_id OUTPUT
DECLARE @target_set_id INT
EXEC msdb.dbo.sp_syspolicy_add_target_set
	@object_set_name=N'AutoShrink-Scheduled_ObjectSet',
	@type_skeleton=N'Server/Database',
	@type=N'DATABASE',
	@enabled=True,
	@target_set_id=@target_set_id OUTPUT
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'',
	@target_set_level_id=0;

DECLARE @policy_id INT
EXEC msdb.dbo.sp_syspolicy_add_policy
	@name=N'AutoShrink-Scheduled',
	@condition_name=N'AutoShrink',
	@schedule_uid=@scheduleUID,
	@execution_mode=4,
	@is_enabled=True,
	@policy_id=@policy_id OUTPUT,
	@object_set=N'AutoShrink-Scheduled_ObjectSet'
GO

---------------------------
------AutoShrink-NonScheduled
---------------------------
DECLARE @object_set_id INT
EXEC msdb.dbo.sp_syspolicy_add_object_set
	@object_set_name=N'AutoShrink-NonScheduled_ObjectSet',
	@facet=N'Database',
	@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'AutoShrink-NonScheduled_ObjectSet',
	@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'',
	@target_set_level_id=0
GO
DECLARE @policy_id INT
EXEC msdb.dbo.sp_syspolicy_add_policy
	@name=N'AutoShrink-OnDemand',
	@condition_name=N'AutoShrink',
	@policy_category=N'',
	@execution_mode=0,
	@policy_id=@policy_id OUTPUT,
	@root_condition_name=N'',
	@object_set=N'AutoShrink-NonScheduled_ObjectSet'
--SELECT @policy_id


GO