Retrieving list of backups performed for specific database


Problem

When creating your backup strategy with Cloudberry Backup sometimes you need to analyze backup perfomance.


Suggestions and Resolution

  1. Start MS SQL management studio;

  2. Create a new script;

  3. Select the database to run against;

  4. Insert the following script and execute it.

SELECT s.database_name, m.physical_device_name, CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14))
+ ' ' + 'MB' AS bkSize, CAST(DATEDIFF(second, s.backup_start_date, s.backup_finish_date) AS VARCHAR(4))
+ ' ' + 'Seconds' TimeTaken, s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type] WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name, s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() 
ORDER BY backup_start_date DESC, backup_finish_date
GO

Reference: Pinal Dave https://blog.sqlauthority.com)


Contact Us

Tech questions: tech@cloudberrylab.com
Sales questions: sales@cloudberrylab.com