I want to check check if backup plans are set up correctly and need to query different servers, mostly SQL Server 2008, but also 2012.
Is there any way to query the maintenance plans for databases, backupfolder, starttime and other information?
So far I have only have this:
select name, subplan_name from msdb.dbo.sysmaintplan_subplans sp join
msdb.dbo.sysmaintplan_plans p on p.id = sp.plan_id
Mostly this is regarding new servers so backup have not had a chance to run yet, rendering logs useless.
Best Answer
You can use the available views in msdb to find the information regarding backups :
dbo.backupmediafamily: provides metadata for the physical backup files as they relate to backup sets.
dbo.backupfile: this system view provides the most-granular information for the physical backup files
You can use below T-SQL code to list down all required info: