T-SQL query to find backup folder for specific maintenance plan

Question

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.

Answer 1

You can use the available views in msdb to find the information regarding backups :

1. dbo.backupset: provides information concerning the most-granular details of the backup process

2. dbo.backupmediafamily: provides metadata for the physical backup files as they relate to backup sets.

3. 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:

    SELECT  
    CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
    msdb.dbo.backupset.database_name,  
    msdb.dbo.backupset.backup_start_date,  
    msdb.dbo.backupset.backup_finish_date, 
    msdb.dbo.backupset.expiration_date, 
    CASE msdb..backupset.type  
    WHEN 'D' THEN 'Database'  
    WHEN 'L' THEN 'Log'  
    END AS backup_type,  
    msdb.dbo.backupset.backup_size,  
    msdb.dbo.backupmediafamily.logical_device_name,  
    msdb.dbo.backupmediafamily.physical_device_name,   
    msdb.dbo.backupset.name AS backupset_name, 
    msdb.dbo.backupset.description 
    FROM   msdb.dbo.backupmediafamily  
    INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id =    msdb.dbo.backupset.media_set_id 
    WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 1)  
    ORDER BY  
    msdb.dbo.backupset.database_name, 
    msdb.dbo.backupset.backup_finish_date