TexCopy Sql Server 2005

Here is a very useful script that shows the backup history.

 

SELECT
    s.database_name,
    m.physical_device_name,
    CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
    s.backup_start_date,
    s.backup_finish_date,
    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 AS s
INNER JOIN msdb.dbo.backupmediafamily AS m     ON s.media_set_id = m.media_set_id
--WHERE s.type = 'D'
ORDER BY s.backup_start_date DESC