SQL Server Performance

list databases in backup maintenance plan

Discussion in 'SQL Server 2005 General DBA Questions' started by Trev256b, Jun 5, 2011.

  1. Trev256b Member

    How do i easily list all the databases that are backed up in a maintenance plan other than reading from the screen, as the job is encrypted in maintenance plan?
    Trev256b, Jun 5, 2011
    #1

  2. Luis Martin Moderator

    SELECT sd.name AS [Database],
    CASE WHEN bs.type = 'D' THEN 'Full backup'
    WHEN bs.type = 'I' THEN 'Differential'
    WHEN bs.type = 'L' THEN 'Log'
    WHEN bs.type = 'F' THEN 'File/Filegroup'
    WHEN bs.type = 'G' THEN 'Differential file'
    WHEN bs.type = 'P' THEN 'Partial'
    WHEN bs.type = 'Q' THEN 'Differential partial'
    WHEN bs.type IS NULL THEN 'No backups'
    ELSE 'Unknown (' + bs.type + ')'
    END AS [Backup Type],
    max(bs.backup_start_date) AS [Last Backup of Type]
    FROM master..sysdatabases sd
    LEFT OUTER JOIN msdb..backupset bs ON rtrim(bs.database_name) = rtrim(sd.name)
    LEFT OUTER JOIN msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
    WHERE sd.name <> 'tempdb'
    GROUP BY sd.name,
    bs.type,
    bs.database_name
    ORDER BY sd.name, [Last Backup of Type]
    Luis Martin, Jun 5, 2011
    #2

  3. Trev256b Member

    what I'm really looking for is just to list the databases in a maintenance plan. I don't need any other information. How can I list the databases in a maintenance plan easily?
    Trev256b, Jun 5, 2011
    #3

  4. satya Moderator

    Your Q is little bit unclear that what you accomplish, when you are using DB mainteance plan the list of databases are presented in the screen in addiiton to that if you need to backup specific databases you can use the TSQL too.
    satya, Jun 6, 2011
    #4

  5. Trev256b Member

    hi satya - ok here goes - i can see the list of databases selected in a maintenance plan (when selecting specific databases) on the screen (i.e. tick boxes are ticked). I want to list these databases without using a screenshot (or several screenshots) or writing/typing the names out. how can i list these databases easily (possibly by TSQL script)?
    Trev256b, Jun 6, 2011
    #5

Share This Page