0

enter image description hereCan anyone help me to add two more columns to the existing code? I am not a core developer. I want backup_size in GBs and name from msdb.dbo.backupset table, where msdb.dbo.backupset.name = **'XXXXXXX'**. I need this because we are using a third party tool for backups. That tool name is reflecting in msdb.dbo.backupset name column, so I want to filter by that name. Please see code below.

SELECT name,
       recovery_model_desc,
       state_desc,
       d AS 'Last Full Backup',
       i AS 'Last Differential Backup',
       l AS 'Last log Backup'
 FROM (SELECT db.name,
              db.state_desc,
              db.recovery_model_desc,
              type,
              backup_finish_date
        FROM   master.sys.databases db
         LEFT OUTER JOIN msdb.dbo.backupset a ON a.database_name = db.name
       ) AS Sourcetable 
 PIVOT (MAX(backup_finish_date) FOR type IN ( D, I, L )) AS MostRecentBackup
1
SELECT name , recovery_model_desc , state_desc , d AS 'Last Full Backup' , i AS 'Last Differential Backup' , l AS 'Last log Backup' , backupname
, Cast(backupsizeGB AS DECIMAL(10, 2)) backupsizeGB
FROM 
( SELECT db.name , db.state_desc , db.recovery_model_desc , a.type , LastBackupFinishedDate, b.name backupname, (b.backup_size /1024 / 1024 /1024) as backupsizeGB
FROM master.sys.databases db 
LEFT OUTER JOIN 
(select database_name dBase, type, max(backup_finish_date) LastBackupFinishedDate From msdb.dbo.backupset group By database_name, type)
 a ON a.dBase = db.name 
    Left outer join msdb.dbo.backupset b on b.database_name=a.dBase and backup_finish_date= LastBackupFinishedDate
) AS Sourcetable 
PIVOT ( MAX(LastBackupFinishedDate) FOR type IN ( D, I, L ) ) AS MostRecentBackup

This cleaned it up for me.
Can't do a true pivot (Placing the dates of each on one line) because the filenames and sizes prevent it.

1
  • Thanks @RObWaibel,It helped me to fetch exact data.Perfect. – SQLBoy Sep 2 '16 at 5:39
2

You should be able to tweak this for your needs

SELECT bs.database_name AS DbName
    ,CAST(bs.backup_size / 1024.0 / 1024 / 1024 AS DECIMAL(10, 2)) AS BackupSize_GB
    ,bs.backup_start_date AS Backup_StartDate
    ,bs.backup_finish_date AS Backup_EndDate
    ,bs.NAME AS BackupName
    ,bmf.physical_device_name AS DeviceName
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = 'DB_Name'
  AND  bs.NAME = 'BackupSetName'
ORDER BY bs.backup_start_date DESC
4
  • Hi @Scott,I checked this one.It adds two new columns I needed but gives no result somehow.can you please help me – SQLBoy Sep 1 '16 at 11:59
  • 1
    Did you change the 'DB_Name' literal to your database name and change bs.NAME to the backupset name you are looking for? – Scott Hodgin - Retired Sep 1 '16 at 12:01
  • Yes sir I did do that. – SQLBoy Sep 1 '16 at 12:04
  • Comment line AND bs.NAME = 'BackupSetName' and check the result – Marcin S. Sep 1 '16 at 12:06
1

Here is your query modified with the 2 columns.

SELECT name , recovery_model_desc , state_desc , d AS 'Last Full Backup' , i AS 'Last Differential Backup' , l AS 'Last log Backup' , backupname, Cast(backupsizeGB AS DECIMAL(10, 2)) backupsizeGB
FROM 
( SELECT db.name , db.state_desc , db.recovery_model_desc , type , backup_finish_date, a.name backupname, a.backup_size /1024 / 1024 / 1024 as backupsizeGB
FROM master.sys.databases db 
LEFT OUTER JOIN msdb.dbo.backupset a ON a.database_name = db.name 
Where a.name = 'BackupSetName') AS Sourcetable 
PIVOT ( MAX(backup_finish_date) FOR type IN ( D, I, L ) ) AS MostRecentBackup
7
  • Hi @WEI_DBA,it shows the two new columns but gives no result under the columns.can you please let me know sir – SQLBoy Sep 1 '16 at 11:52
  • Try querying the backupset table alone using, Select name, backup_size From msdb.dbo.backupset Where name = 'DBName' and see what you get. – SS_DBA Sep 1 '16 at 12:03
  • The multipart identifier "a.name" could not be found. – SQLBoy Sep 1 '16 at 12:06
  • I removed that. Sorry, copy paste error on my part. See my edited statement above. – SS_DBA Sep 1 '16 at 12:06
  • Actually DBname is supposed to be the backup set name. See edited code in the block area above in the answer. – SS_DBA Sep 1 '16 at 12:08

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.