Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Database User and Programming Tips

Posted October 3, 2016

Reading Backup History Information for a Database

By Greg Larsen

There are times when you might want to read backup / restore history information for a SQL Server database.  This information is stored in the backup / restore metadata tables within the msdb database. The backup information is useful for identifying when backups ran (start and finish date/time), the location where the backup was written, the size of the database backup, etc.  Additionally the backup history information might be useful to identify which databases have not been backed up.

To read the backup history information you need to understand the design of the backup / restore metadata tables in the msdb database.  The follow table contains a description of the eight different backup / restore metadata tables in the msdb database that contains backup / restore history information:

History table

Description

backupfile

Contains one row for each data or log file that is backed up.

backupfilegroup

Contains a row for each filegroup in a backup set.

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.

backupmediaset

Contains one row for each backup media set.

backupset

Contains a row for each backup set.

restorefile

Contains one row for each restored file. This includes files restored indirectly by filegroup name.

restorefilegroup

Contains one row for each restored filegroup.

restorehistory

Contains one row for each restore operation.

This table above, about the backup / restore history tables, came from:  https://msdn.microsoft.com/en-us/library/ms188653.aspx#BnRHistoryTables.  You can use this source to find out more information about each of these different backup / history tables.  To show you how to use these backup / restore history tables let me show you a couple of examples. 

For the first example, suppose you have a need to show the database backups that have been taken for in last 30 days.  To meet this requirement you can run the following code:

USE msdb;
GO
SELECT 
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS server_name, 
bs.database_name, 
bs.backup_start_date, 
bs.backup_finish_date, 
DATEDIFF(mi,bs.backup_finish_date, bs.backup_start_date) AS duration_in_sec,
bs.expiration_date, 
bs.backup_size, 
bmf.logical_device_name, 
bmf.physical_device_name, 
bs.name AS backupset_name, 
bs.description 
FROM msdb.dbo.backupmediafamily bmf
 JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id 
WHERE (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() - 30) 
ORDER BY 
bs.database_name, 
bs.backup_finish_date;

Alternatively sometimes you want to find databases that have not been backed up for a period of time.  Here is some code that displays the databases that have not been backup up in the last seven days:  

USE msdb;
GO
SELECT 
sd.name AS database_name, bs.backup_finish_date 
FROM msdb.dbo.backupset bs
RIGHT OUTER JOIN sys.sysdatabases sd
ON bs.database_name = sd.name
AND bs.backup_finish_date > DATEADD(day,-7,getdate())

WHERE bs.database_name is NULL

See all articles by Greg Larsen



Database User and Programming Tips Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.