Search This Blog

SQL Server db backup history-Part1

Below is a script that I use to find the backup history for all the databases on a single instance. There is an inline ‘WHERE’ clause that is currently commented out that you can add in to filter by database name.
use MSDB
GO
SELECT TOP 96
bckset.database_name AS DatabaseName,
bckmdiaset.physical_device_name AS BackupLocation,
CASE WHEN bckset.backup_size <= '10485760' THEN
CAST(CAST(bckset.backup_size/1024 AS INT) AS VARCHAR(14)) + ' ' + 'KB'
ELSE
CASE WHEN bckset.backup_size <= '1048576000' THEN
CAST(CAST(bckset.backup_size/1024/1024 AS INT) AS VARCHAR(14)) + ' ' + 'MB'
ELSE
CAST(CAST(bckset.backup_size/1024/1024/1024 AS INT) AS VARCHAR(14)) + ' ' + 'GB'
END
END backupSize,
CAST (bckset.backup_start_date AS smalldatetime) AS StartTime,
CAST (bckset.backup_finish_date AS smalldatetime)FinishTime,
CASE WHEN CAST(DATEDIFF(second, bckset.backup_start_date, bckset.backup_finish_date )AS VARCHAR (4)) <= 60 THEN
CAST(DATEDIFF(second, bckset.backup_start_date,bckset.backup_finish_date) AS VARCHAR(4))+ ' ' + 'Seconds'
ELSE
CAST(DATEDIFF(minute, bckset.backup_start_date,bckset.backup_finish_date) AS VARCHAR(4))+ ' ' + 'Minutes'
END AS TimeTaken,
CAST(bckset.first_lsn AS VARCHAR(25)) AS FirstLogSequenceNumber,
CAST(bckset.last_lsn AS VARCHAR(25)) AS LastLogSequenceNumber,
CASE bckset.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential database'
WHEN 'L' THEN 'Transaction Log'
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential file'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial'
END AS BackupType,
bckset.server_name As ServerName,
bckset.recovery_model As RecoveryModel,
CASE bckset.is_snapshot
WHEN '0' THEN 'FALSE'
WHEN '1' THEN 'TRUE'
END AS IsSnapshot,
CASE [compatibility_level]
WHEN 60 THEN 'SQL Server 6.0'
WHEN 65 THEN 'SQL Server 6.5'
WHEN 70 THEN 'SQL Server 7.0'
WHEN 80 THEN 'SQL Server 2000'
WHEN 90 THEN 'SQL Server 2005'
WHEN 100 THEN 'SQL Server 2008'
WHEN 110 THEN 'SQL Server 2012'
END AS CompatibilityLevel,
--CONCAT (
CAST (software_major_version AS VARCHAR (2)), +'.'+
CAST (software_minor_version as VARCHAR (2)), +'.'+
CAST (software_build_version AS VARCHAR (5))
--)
 as SqlVersionNumber
FROM msdb.dbo.backupset bckset
INNER JOIN msdb.dbo.backupmediafamily bckmdiaset ON bckset.media_set_id = bckmdiaset.media_set_id
--WHERE bckset.database_name = 'db_name'-- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date
GO

Out Put:

DatabaseName BackupLocation backupSize StartTime FinishTime TimeTaken FirstLogSequenceNumber LastLogSequenceNumber
BackupType ServerName RecoveryModel IsSnapshot CompatibilityLevel (No column name) (No column name) SqlVersionNumber
model D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\model_backup_2014_01_22_173257_4531250.bak 1355 KB 2014-01-22 17:33:00 2014-01-22 17:33:00 0 Seconds 18000000028800037 18000000032000001 Full MAHALIRAJESH FULL FALSE SQL Server 2008 10 .0 .1600
A few things I want to comment on:

  • I select the top 96 on the basis that this should be a full days worth of backups: 15 minute T-Logs and one Full over the course of 24 hours equates to 96. I’ll fully admit that this is a little bit pedantic…
  • Lines 6-14 calculate the size of the size of the backups based on diving the bytes by 1024. Theoretically any of the following 3 are correct for converting bytes to megabytes:
  1. megabytes=bytes/1000000
  2. megabytes=bytes/1024/1024
  3. megabytes=bytes/1024/1000
    however which one is right is a far more contentious debate. As I was using boundaries for bytes to megabytes and gigabytes I felt that dividing by 1024/1024 was the most correct way in this script.
  • I’ve included whether the backup is a snapshot or not as I work mainly with Developer and Enterprise edition.
  • To aid the compatibility, I’ve included info about what version of SQL the backups are running on. As different service packs can impact restoring I’ve concatenated the version numbers to one readable column.