Some of us know SQL server maintenance plan is encrypted in xml file. I believe you can find a lot resources how to query SQL server maintenance plan. The hardest part of the query is I need to know how to unwrap xml and retrieve information.
I found very useful sites http://sqlchad.com/?p=339 and http://www.sqlballs.com/2013/01/how-do-you-query-maintenance-plan.html. They gave me what I am really looking for. I enhanced the script from the site and combine with powershell script to retrieve full and tlog backup maintenance plan from all SQL servers which includes maintenance clean up task. The script works in 2005/2008R2 and 2012.
Enjoy Scripting...
$($ServerList = 'E:\PowerShell\SQLServerList\SQLSvrList.txt'
$exportcsv ='E:\PowerShell\GetMaintenancePlan\BackupInfo.csv'
Clear-Content $exportcsv
$servername = Get-Content $ServerList
$errorlog = 'E:\PowerShell\GetMaintenancePlan\Error.txt'
$RunTime = $(get-date -f MM-dd-yyyy_HH_mm_ss)
if(!(test-path $ServerList))
{
Write-Error "SQLSvr.txt is not Found" -ea Stop
}
if(test-path $ServerList)
{
foreach($server in $servername){
Try{
$sqlconn = new-object System.Data.SqlClient.SqlConnection("server=$server;Trusted_Connection=true");
$query = "
IF SUBSTRING(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR),0,CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR),0)) = 9
BEGIN
WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTS
, 'www.microsoft.com/sqlserver/dts/tasks/sqltask' AS SQLTask)
,ssis AS (
SELECT name
, CAST(CAST(packagedata AS varbinary(MAX)) AS XML) AS package
FROM [msdb].[dbo].[sysdtspackages90]
WHERE packagetype = 6
)
SELECT '$server' as ServerName,s.name as MaintenancePlanName,
CASE c.value('(SQLTask:SqlTaskData/@SQLTask:BackupAction)[1]', 'INT')
WHEN 0 THEN
CASE c.value('(SQLTask:SqlTaskData/@SQLTask:BackupIsIncremental)[1]', 'bit')
WHEN 1 THEN 'DIFFERENTIAL'
WHEN 0 THEN 'FULL'
ELSE 'Maintenance clean Up'
END
WHEN 1 THEN 'FILES'
WHEN 2 THEN 'LOG'
ELSE 'UNKNOWN'
END as BackupType,
CASE c.value('(SQLTask:SqlTaskData/@SQLTask:BackupCompressionAction)[1]', 'int')
WHEN 0 THEN 'SERVER DEFAULT CONFIG'
WHEN 1 THEN 'YES'
WHEN 2 THEN 'NO'
END as Compressed,
c.value('(SQLTask:SqlTaskData/@SQLTask:BackupDestinationAutoFolderPath)[1]', 'VARCHAR(MAX)') as BackupLocation
FROM ssis s
CROSS APPLY package.nodes('//DTS:ObjectData') t(c)
WHERE c.exist('SQLTask:SqlTaskData/@SQLTask:BackupDestinationAutoFolderPath') = 1
END
ELSE IF SUBSTRING(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR),0,CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR),0)) >= 10
BEGIN
WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTS
, 'www.microsoft.com/sqlserver/dts/tasks/sqltask' AS SQLTask)
,ssis AS (
SELECT name
, CAST(CAST(packagedata AS varbinary(MAX)) AS XML) AS package
FROM [msdb].[dbo].[sysssispackages]
WHERE packagetype = 6
)
SELECT '$server' as ServerName,s.name as MaintenancePlanName,
CASE c.value('(SQLTask:SqlTaskData/@SQLTask:BackupAction)[1]', 'INT')
WHEN 0 THEN
CASE c.value('(SQLTask:SqlTaskData/@SQLTask:BackupIsIncremental)[1]', 'bit')
WHEN 1 THEN 'DIFFERENTIAL'
WHEN 0 THEN 'FULL'
ELSE 'Maintenance clean Up'
END
WHEN 1 THEN 'FILES'
WHEN 2 THEN 'LOG'
ELSE 'Maintenance clean Up'
END as BackupType,
COALESCE(c.value('(SQLTask:SqlTaskData/@SQLTask:BackupFileExtension)[1]','varchar(1000)'),
c.value('(SQLTask:SqlTaskData/@SQLTask:FileExtension)[1]','varchar(1000)')) as Extension,
COALESCE(c.value('(SQLTask:SqlTaskData/@SQLTask:BackupFileExtension)[1]','varchar(1000)'),
c.value('(SQLTask:SqlTaskData/@SQLTask:FileExtension)[1]','varchar(1000)'))+'$server' as PK,
'DELETE backups older than ' + c.value('(SQLTask:SqlTaskData/@SQLTask:RemoveOlderThan)[1]','VARCHAR(3)') +
CASE c.value('(SQLTask:SqlTaskData/@SQLTask:TimeUnitsType)[1]','VARCHAR(10)') --0=Daily,1=Weekly,2=Monthly,3=Yearly,5=Hourly
WHEN 0 THEN ' Day(s)'
WHEN 1 THEN ' Week(s)'
WHEN 2 THEN ' Month(s)'
WHEN 3 THEN ' Year(s)'
WHEN 4 THEN ' Minute(s)'
WHEN 5 THEN ' Hour(s)'
END AS Del_Freqency,
CASE c.value('(SQLTask:SqlTaskData/@SQLTask:BackupCompressionAction)[1]', 'int')
WHEN 0 THEN 'SERVER DEFAULT CONFIG'
WHEN 1 THEN 'YES'
WHEN 2 THEN 'NO'
END as Compressed,
c.value('(SQLTask:SqlTaskData/@SQLTask:BackupVerifyIntegrity)[1]', 'BIT') as BackupVerified,
c.value('(SQLTask:SqlTaskData/@SQLTask:BackupDestinationAutoFolderPath)[1]', 'VARCHAR(MAX)') as BackupLocation
FROM ssis s
CROSS APPLY package.nodes('//DTS:ObjectData') t(c)
where s.name in('Full BackUp','Transaction Logs')
END
"
$sqlconn.Open()
$sqlcmd = new-object System.Data.SqlClient.SqlCommand ($query, $sqlconn);
$sqlcmd.CommandTimeout = 0;
#$dr = $sqlcmd.ExecuteReader();
$dr = New-Object System.Data.SqlClient.SqlDataAdapter
$dr.SelectCommand = $sqlcmd
$dt = New-Object System.Data.DataTable
$dr.fill($dt) | out-null
$dt
}
Catch
{ Clear-Content $errorlog
"Fail to get information $server +' ' + $RunTime :$_" |Out-File $errorlog -Append
}
}
})|Export-csv $exportcsv -noType
Get-Job | Wait-Job | Out-Null
Remove-Job -State Completed
Thank you.
