How to find last backup history (time and details) of a specific database?
Please use below code (TSQL Script) to find last backup history
of a specific database.
For SQL 2005/2008/2008R2:
USE <DatabaseName>
GO
SELECT TOP 100 -- Change 100 to
any number you need.
a.database_name,b.physical_device_name,CAST(CAST(a.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, a.backup_start_date,
a.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
a.backup_start_date,
CAST(a.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(a.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE a.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
a.server_name,
a.recovery_model
FROM msdb.dbo.backupset a
INNER JOIN msdb.dbo.backupmediafamily b ON
a.media_set_id =
b.media_set_id
WHERE a.database_name = DB_NAME()
ORDER BY backup_start_date DESC, backup_finish_date
GO
For SQL 2000:
USE <DatabaseName>
GO
SELECT TOP 100 -- Change 100 to
any number you need.
a.database_name,b.physical_device_name,CAST(CAST(a.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, a.backup_start_date,
a.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
a.backup_start_date,
CAST(a.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(a.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE a.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
a.server_name
FROM msdb.dbo.backupset a
INNER JOIN msdb.dbo.backupmediafamily b ON
a.media_set_id =
b.media_set_id
WHERE a.database_name = DB_NAME()
ORDER BY backup_start_date DESC, backup_finish_date
GO
Comments