Query SQL backup history

The Job Activity Monitor and the View History option are just fine in SQL Server Management Studio, but sometimes you want to run a query to look at the all the successful backups you’ve run.

Don’t be dreary, get to the query.

SELECT
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 s.database_name = 'ImportantDB' /*Remove this line for all the databases*/
and s.backup_start_date > DATEADD(d,-1,GETDATE()) /*How far do you want to go back? This returns 1 day of data */
ORDER BY backup_start_date DESC, backup_finish_date
GO

We have two tables here, backupset and backupmediafamily. The backupset does most of the heavy lifting giving you the pertinent info like database name, how big the backup is, how long it took, when the backup started, what kind of back up it is and then some.

msdb.dbo.backupset,”Contains a row for each backup set. A backup set contains the backup from a single, successful backup operation. RESTORE, RESTORE FILELISTONLY, RESTORE HEADERONLY, and RESTORE VERIFYONLY statements operate on a single backup set within the media set on the specified backup device or devices.”

And we only bring in backupmediafamily for the physical_device_name field, a.k.a. the file path and file name of your backup.

msdb.dbo.backupmediafamily “Contains one row for each media family. If a media family resides in a mirrored media set, the family has a separate row for each mirror in the media set.”

You want to see some results? Go run this script you pulled off the internet on your nearest production server. What could possibly go wrong. But really, if you are a good and curious DBA/DBA in training/DBA-curious dev/etc. start with just looking at the msdb.dbo.backupset table.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s