2

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.

  • Maintenance plans are stored as SSIS packages in newer versions of SQL Server, and you may be able to get this information from the .dtsx file. However, in many cases, the location of backup directories is not even a constant within the SSIS package, but rather it is constructed from metadata at runtime. – dartonw Feb 5 '15 at 19:12
  • Thank you for posting this. I performed a p2v conversion of a SQL server and forgot to disable/update the maintenance plans. Both servers were backing up to the same location. I needed to isolate which server was creating each file to clean up my backup repository. – Brandon Dec 21 '16 at 15:20
3

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
  • Thanks, only problem is that it requires backup to have been run and I don't always have time for that. I will look into SSIS. – Stiegler Feb 10 '15 at 14:23
  • This has proved so useful that I marked it as a solution. Thank you :) It will not work on new servers where backup hasn't run yet, but I'm still looking into SSIS and powershell options to do that. – Stiegler Apr 18 '15 at 8:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.