Friday, April 15, 2011

Do you want to get the Backup Devices list of rour DB using a single Query?

Easy to do:
SELECT 'Server[@Name=' + quotename(CAST(serverproperty(N'Servername') AS sysname),'''') + ']' + '/BackupDevice[@Name=' + quotename(o.name,'''') + ']' AS [Urn],o.name AS [Name], case when 1=msdb.dbo.fn_syspolicy_is_automation_enabled() and exists (select * from msdb.dbo.syspolicy_system_health_state where target_query_expression_with_id like 'Server/BackupDevice\[@Name=' + QUOTENAME(o.name, '''') + '\]%' ESCAPE '\') then 1 else 0 end AS [PolicyHealthState] FROM sys.backup_devices o ORDER BY [Name] ASC
The result of this query will give us the list of backup devices on URN format

No comments:

Post a Comment