Checking database backup history

Sometimes, especially with large databases, the duration of a backup can be significant (e.g. multiple hours).

So, if you’re wondering exactly how far along a backup is, and when it is estimated to complete, you can run the query below

DECLARE @DatabaseName NVARCHAR(50)
SELECT @DatabaseName = '[DATABASE NAME GOES HERE]'

SELECT TOP 1
	s.database_name AS 'DatabaseName',
	m.physical_device_name AS 'BackupFilePath',
	s.database_creation_date AS 'DatabaseCreated',
	s.backup_start_date AS 'BackupStartDate',
	s.backup_finish_date AS 'BackupFinishDate',
	DATEDIFF(minute,s.backup_start_date, s.backup_finish_date) AS DurationInMinutes,
	(s.backup_size / 1000000000) AS 'BackupSizeGB'
FROM
	msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m
	ON s.media_set_id = m.media_set_id
WHERE
	s.database_name = @DatabaseName
	AND s.[type] = 'D'
ORDER BY
	backup_start_date DESC,
	backup_finish_date
	

I hope that helps!

Subscribe

0 comments