Backup Metadata Fun

Tim Ford Backup, compression, last backup, Metadata, SQL, SQL Server, world backup day 0 Comment Mar 30, 2012

Until yesterday I never heard of World Backup Day. I checked Target and I couldn’t find a single greeting card either. I was wondering if I was being Punk’d by Brent Ozar in his post that tomorrow (March 31, 2012) has that distinction. Then I figured what the heck, I’ve some scripts I use frequently that involve tapping into backup and restore metadata that would be fun to share with the community.

Coincidentally I’ll be discussing these and other scripts I use to work smarter and not harder in Madison, Wisconsin in April at SQL Saturday 118 in my presentation: Performance-Enhancing Laziness.

Latest Backup Information

I frequently want to determine when my databases were last backed up. I use this information to satisfy such issues as restore requests, determining backup aging tolerances are being met, and in analyzing retention policies as well as for backup volume capacity planning. This information is the basis of one of my Morning Coffee Reports that are generated daily from SQL Server Reporting Services that I review while grabbing that first cup of liquid courage to see how my day is going to fare. I look at both full backups and transaction log backups independently. I believe Brent presented a similar script but I also incorporate backup location into my results because I not only want to see when the last backup occurred; I also want to know where it is.

Last Full Database Backup for Each Database

WITH full_backups AS
(
SELECT
	ROW_NUMBER() OVER(PARTITION BY database_name ORDER BY database_name ASC, backup_finish_date DESC) AS [Row Number]
	, database_name
	, backup_set_id
	, backup_finish_date
FROM msdb.dbo.[backupset]
WHERE [type] = 'D'
)
 
SELECT
	BS.server_name
	, BS.database_name
	, FB.backup_finish_date
	, BMF.physical_device_name
	, BMF.logical_device_name AS [backup_device_name]
FROM full_backups FB
	INNER JOIN msdb.dbo.[backupset] BS ON FB.backup_set_id = BS.backup_set_id
	INNER JOIN msdb.dbo.backupmediafamily BMF ON BS.media_set_id = BMF.media_set_id
WHERE FB.[Row Number] = 1
ORDER BY FB.database_name;

Last Transaction Log Backup for Each Database Running in Full or Bulk-Logged Recovery

WITH log_backups AS
(
SELECT
	ROW_NUMBER() OVER(PARTITION BY database_name ORDER BY database_name ASC, backup_finish_date DESC) AS [Row Number]
	, database_name
	, backup_set_id
	, backup_finish_date
FROM msdb.dbo.[backupset]
WHERE [type] = 'L'
)
 
SELECT
	BS.server_name
	, BS.database_name
	, FB.backup_finish_date
	, BMF.physical_device_name
	, BMF.logical_device_name AS [backup_device_name]
FROM log_backups FB
 INNER JOIN msdb.dbo.[backupset] BS ON FB.backup_set_id = BS.backup_set_id
 INNER JOIN msdb.dbo.backupmediafamily BMF ON BS.media_set_id = BMF.media_set_id
WHERE FB.[Row Number] = 1
ORDER BY FB.database_name;

Is Every Database Getting Backed Up?

When I answer this question I run the identical script Brent blogged about in his post. I encourage you to check it out there.

When is this @&#ing Backup/Restore Going to @&#ing Finish?

A watched pot never boils and a watched backup or restore never finishes. The nice thing is there is a column in the sys.dm_exec_requests Dynamic Management Object that does provide a percentage of completion metric for certain commands that does allow you to estimate when the process will complete however. Keep in mind that SQL Server has much in common with palm readers, fortune tellers and con artists though when it comes to providing you with an accurate estimate of completion time: the more information your provide (in this case, more bits that are written to disk) the better the estimate of completion.

Since I’m using the value of sys.dm_exec_requests.percent_complete to return results this query will not be limited to just BACKUP and RESTORE commands. That means you’ll also be able to answer the following questions as well with the same query:

When will this @&*#ing DBCC CHECKDB or DBCC CHECKTABLE etc. complete?
When will this @&*#ing SHRINK DATABASE be over?
When will this @&*#ing SHRINKFILE finish?
When will this @&*#ing INDEX DEFRAG or ALTER INDEX REORGANIZE be done?
When will this @&*#ing ROLLBACK TRANSACTION finish?

Please note however it will not answer the question concerning how many cute, fluffy, innocent kittens you’re viciously murdering in cold blood by performing that SHRINK DATABASE command.

SELECT eR.session_id
	, eR.percent_complete
	, eR.total_elapsed_time/1000 AS elapsed_secs
	, DATEADD(s,100/((eR.percent_complete) / (eR.total_elapsed_time/1000)), eR.start_time) AS est_completion_time
	, eST.text
FROM sys.dm_exec_requests eR
	CROSS APPLY sys.dm_exec_sql_text(eR.sql_handle) eST
WHERE eR.percent_complete &gt; 0
	AND eR.session_id &lt;&gt; @@spid;

Use Backup History to Examine Database File Growth

I typically advocate only retaining 30 days of msdb job and backup history for your SQL Servers. There is no scheduled or configurable task to keep a running X days of history so you must roll your own solution to avoid not only msdb growing wildly un-checked like bacon references on Twitter but to avoid the pitfalls of deficiencies in indexing of tables within msdb. There is a side benefit of withheld records in msdb though… you can use it to get a rough idea of database file growth trending without persisting information from master.sys.database_files or master.sys.all_files to a user database elsewhere.

SELECT
	BS.database_name
	, CAST(BF.file_size/1024/1024 AS bigint) AS file_size_mb
	, CAST(BF.backup_size/1024/1024 AS bigint) AS consumed_size_mb
	, BF.logical_name
	, BF.physical_name
	, BS.backup_finish_date AS polling_date
FROM msdb.dbo.backupset BS
	INNER JOIN msdb.dbo.backupfile BF ON BS.backup_set_id = BF.backup_set_id
WHERE  BS.type = 'D'
ORDER BY BS.database_name
	, BF.logical_name
	, BS.backup_finish_date DESC;

Determine Compression Factor for Individual Databases

If you’re using a recent version of SQL Server that supports compression then the following query will allow you to estimate your space savings by going the route of compressing backups. Different types of data compress differently. This query will toss errors if you run it on a version of SQL Server that does not support compressed backup funtionality (SQL Server 2005 and older.)

WITH full_backups AS
(
SELECT
	ROW_NUMBER() OVER(PARTITION BY BS.database_name ORDER BY BS.database_name ASC, BS.backup_finish_date DESC) AS [Row Number]
	, BS.database_name
	, BS.backup_set_id
	, BS.backup_size AS uncompressed_size
	, BS.compressed_backup_size AS compressed_size
	, CAST((BS.compressed_backup_size / BS.backup_size) AS decimal(4,1)) AS compression_ratio
	, BS.backup_finish_date
FROM msdb.dbo.[backupset] BS
WHERE BS.[type] = 'D'
)
 
SELECT
	FB.database_name
	, FB.backup_finish_date
	, FB.uncompressed_size
	, FB.compressed_size
	, FB.compression_ratio
FROM full_backups FB
 INNER JOIN msdb.dbo.[backupset] BS ON FB.backup_set_id = BS.backup_set_id
 INNER JOIN msdb.dbo.backupmediafamily BMF ON BS.media_set_id = BMF.media_set_id
WHERE FB.[Row Number] = 1
ORDER BY FB.database_name;

Happy World Backup Day

Now that you’ve had a chance to see what more there is to do with the data contained in msdb on your SQL Servers get out there and celebrate World Backup Day as tradition holds. Don your kilt, pour a glass of bacon gravy, sing the songs we all learned as children and backup up all that personal data we now keep out in the cloud.

SQL