solving sql server problems for millions of dbas and developers since 2006

SQL Product Highlight

Red Gate Software - SQL Developer Bundle

Improve database development with a bundle of 12 SQL developer tools from Red Gate. The SQL Developer Bundle will help you:

Improve testing and productivity. Find and fix errors caused by differences between databases with SQL Compare.
Troubleshoot errors. Browse a history of changes, find broken code or conflicts, and automatically generate scripts to resolve inconsistencies.
Simplify database deployments. Use one-click deployment across multiple servers.

Learn more!

SQL Server Backup History Analysis

By: Atif Shehzad | Read Comments | Related Tips: More > Backup

Free SQL Server Learning - Making the most out of SQL Server Agent

Problem
Database backups hold primary importance among daily DBA tasks. This task is typically automated through maintenance plans, scheduled SQL Server Agent Jobs or third party tools. With the importance of backups it is necessary to regularly analyze the performance and efficiency of the process. So how can we get insight into the performance of a backup process for any database?

Solution
Let's take a look at a few different scripts to see what sort of insight we can get. For the purposes of this tip, I have created a database by the name of 'BackupReport' to use in our examples. For testing purposes, full, differential and transaction log were performed to outline the value of the script. Check out the following script:

Script - Generate Backup Process Statistics

SELECT s.database_name,
m.physical_device_name,
cast(s.backup_size/1000000 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,
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 = 'BackupReport'
ORDER BY database_name, backup_start_date, backup_finish_date

Here are the results based on my example:

Now we have statistics for all of the backup processes for a given database. With this data we are able to analyze the changes in a specific time period or for different backup types.

Customizing the script

The script is for SQL Server 2005. If you are going to run this script on SQL Server 2000, the you have to remove the column 'recovery_model' from select list.
The script generates data for a specific database provided in the WHERE clause. If you want to generate statistics for all databases then simply modify the WHERE clause in the script above.
The script generates time taken in seconds. To get time in minutes or hours simply change the datediff parameter in the second line of the script to the required time unit.
The table 'backupset' in the 'msdb' database has additional information. Any of the following columns can be added to the SELECT statement of if additional information is required.

Column Name	Information
[name]	logical name of backup
[user_name]	user performing backup
[description]	description of backup
[first_lsn]	first log sequence number
[last_lsn]	last log sequence number
[checkpoint_lsn]	checkpoint log sequence number
[database_creation_date]	date of creation of database
[compatibility_level]	compatibility level of backed up database
[machine_name]	name of SQL Server where the backed originated
[is_password_protected]	either database backup is password protected or not
[is_readonly]	either database backup is read only or not
[is_damaged]	either database backup is damaged or not

Next Steps

Regularly generate and analyze the backup statistics for all of your databases to understand the growth and performance trends.
If you have specific needs, customize the script based on the backup and recovery tables in the MSDB database.
Check out these backup and recovery related tips:

Last Update: 10/2/2008

About the author

Become a paid author

Comments and Feedback:

Sponsor Information

"Amazing, Amazing, Amazing! SQL doctor is truly one of the most powerful tools I have seen."

SQL Monitor: prioritize your SQL Server workload with easy-to-use performance monitoring

What grade do you think your SQL Servers get? Find out with a SQL Server Health Check consultant in the USA.

Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now.

The SQL Server Security THREAT - It’s Closer Than You Think