;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*/
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