Here is a SQL Server script that will get you the database backup history for a server. It is a significant troubleshooting step to determine if a database has been backed up properly in your environment. Read more here.
If you are a DBA, you know the importance of a robust backup process. Backups should be performed regularly and should have all possible options to guard you against any data loss. At times you may be required to check the status of the backups to ensure that it is working fine.
This blog looks at a SQL SERVER script that can be used to get database backup history.
USE [Database]
GO
SELECT TOP 100
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
--WHERE s.database_name = DB_NAME() – uncomment this line for current database backup history
ORDER BY backup_start_date DESC, backup_finish_date
GO