Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Backup History Analysis


By:   |   Read Comments (3)   |   Related Tips: More > Backup

Attend a SQL Server Conference for FREE >> click to learn more


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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Atif Shehzad Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, February 18, 2016 - 6:38:43 AM - Elisha praveen Back To Top

I configured the Logshipping to the database which is already running in AlwaysOn in secondary server

Now the Backup Job and copy Job are running fine but except the restore job getting failed with below error

 

The log shipping secondary database xxxxxxxx has restore threshold of 60 minutes and is out of sync. No restore was performed for 1065 minutes. Restored latency is 0 minutes. Check agent log and logshipping monitor information. [SQLSTATE 42000] (Error 14421).  The step failed

 

 

can someone help me on this

 

Thanks in Advance

Praveen


Monday, December 28, 2015 - 2:48:17 AM - Atif Shehzad Back To Top

Looks that we cannot get finish time of restore process in tables like msdb.dbo.restorehistory. Other source of this info may be default trace from where completion time may be get to apply with start time and have the duration of whole process.


Wednesday, December 23, 2015 - 3:46:36 PM - FK Back To Top

 

 AOA Atif,

Please inform is there a way we can list total execution time of restore processes on sql server?

 

Wassalam,

FK


Learn more about SQL Server tools