Maintenance Plan Listing for DB's

;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTSJob

                   ,'www.microsoft.com/sqlserver/dts/tasks/sqltask' AS SQLTask)

,SSISJob AS

   (SELECT name, description, CAST(CAST(packagedata AS varbinary(MAX)) AS XML) AS Plans

    FROM   msdb.dbo.sysssispackages)

SELECT SSISJob.name AS Plan_Name

      ,SSISJob.Description AS Plan_Description

      ,Jobs.value  ('(SQLTask:SqlTaskData/@SQLTask:TaskName)[1]', 'NVARCHAR(MAX)') AS JOB_name

         ,DBname.value('(@SQLTask:DatabaseName)[1]','nvarchar(max)') AS DBname

         INTO #tmp_names

FROM  SSISJob

CROSS APPLY Plans.nodes('//DTSJob:ObjectData') Obj(Jobs)

CROSS APPLY Obj.Jobs.nodes('SQLTask:SqlTaskData/SQLTask:SelectedDatabases') DBS(DBname)

WHERE Jobs.exist('SQLTask:SqlTaskData') = 1

  /*Databases that are not in any Maint Plan */

select name as 'Name No backup at all' from sys.databases

WHERE name NOT IN ('tempdb')

EXCEPT

SELECT DBname from #tmp_names

select Name AS 'FULL DB without Maint Plan' from sys.databases

  where databasepropertyex([Name],'Recovery') = 'FULL'

  --where Plan_Name='Maint - Full Recovery Nightly'

EXCEPT

select DBName from #tmp_names

  where Plan_Name = 'Maint - Transaction Log - 2 Hours'

EXCEPT

select DBName from #tmp_names

  where Plan_Name = 'Maint - Full Recovery Nightly'

drop table #tmp_names

/*https://www.experts-exchange.com/questions/28732924/SQL-2012-get-databases-associated-with-a-maintenance-plan.html*/

Good casting of Date (swiped from HillRom)

declare @Name nvarchar(100)

declare @Disk nvarchar(max)

declare @Statement nvarchar(max)

select @Name = N'NavicareEnterprise_full_' + cast(year(GETDATE()) as nvarchar) + '_' + cast(month(GETDATE()) as nvarchar) + '_' + cast(day(GETDATE()) as nvarchar) + '_' + cast(DATEPART(hh,getdate()) as nvarchar) + cast(DATEPART(mi,getdate()) as nvarchar) + cast(DATEPART(ss,getdate()) as nvarchar)

select @Disk = N'X:\Backups\NavicareEnterprise\'' + @Name  + '.bak'

select @Statement = N'backup database [NavicareEnterprise] to  disk = N''' + @Disk  + ''' with noformat, noinit,  name = N''' + @Name + ''', skip, rewind, nounload,  stats = 10'


execute sp_executesql @Statement