How to find out how long a SQL Server backup took

By:   |   Updated: 2009-05-08   |   Comments (11)   |   Related: More > Backup

Problem

Many shops do full backups of their databases as part of a job within SQL Agent, typically as part of a maintenance plan. This job will often times do all database backups in secession. As such, while you may know how long the job takes, you may not know how long any one particular database takes to have a full backup taken. When deploying changes it is advised to take a full backup of a database prior to deploying the change and a common question faced will be "how long will it take?".

Solution

The information is readily available inside the msdb database, making the solution as easy as a few lines of T-SQL.

The T-SQL provided below allows for you to input the name of a database if desired. I have also added a line that will filter the results, limiting your view to only the databases that are currently listed in the master.dbo.sysdatabases table. If you comment out that line of code you will return information on the last time a database was backed up on the instance, regardless if the database is currently listed in master.dbo.sysdatabases.

List of Most Recent SQL Server Backups

Here is the T-SQL

DECLARE @dbname sysname
SET @dbname = NULL --set this to be whatever dbname you want
SELECT 
  bup.user_name AS [User],
  bup.database_name AS [Database],
  bup.server_name AS [Server],
  bup.backup_start_date AS [Backup Started],
  bup.backup_finish_date AS [Backup Finished]
  ,CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/3600 AS varchar) + ' hours, ' 
  + CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/60 AS varchar)+ ' minutes, '
  + CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))%60 AS varchar)+ ' seconds'
  AS [Total Time]
FROM msdb.dbo.backupset bup
WHERE bup.backup_set_id IN
  (SELECT MAX(backup_set_id) 
   FROM msdb.dbo.backupset
   WHERE database_name = ISNULL(@dbname, database_name) --if no dbname, then return all
   AND type = 'D' --only interested in the time of last full backup
   GROUP BY database_name) 
/* COMMENT THE NEXT LINE IF YOU WANT ALL BACKUP HISTORY */
AND bup.database_name IN (SELECT name FROM master.dbo.sysdatabases)
ORDER BY bup.database_name

The script will return the following result set:

Column Name Description
User The name of the user that issued the BACKUP DATABASE command.
Database The name of the database.
Server The name of the server instance.
Backup Started The time at which the backup was started.
Backup Finished The time at which the backup was completed.
Total Time The total amount of time it took to complete the backup for that database.

Here is a screenshot of a sample result set returned by the script.

adventure works

List of SQL Server Backups

If you want to get a list of all backups and not just the most recent you can issue the following:

DECLARE @dbname sysname
SET @dbname = NULL --set this to be whatever dbname you want
SELECT 
  bup.user_name AS [User],
  bup.database_name AS [Database],
  bup.server_name AS [Server],
  bup.backup_start_date AS [Backup Started],
  bup.backup_finish_date AS [Backup Finished]
  ,CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/3600 AS varchar) + ' hours, ' 
  + CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/60 AS varchar)+ ' minutes, '
  + CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))%60 AS varchar)+ ' seconds'
  AS [Total Time]
FROM msdb.dbo.backupset bup
/* COMMENT THE NEXT LINE IF YOU WANT ALL BACKUP HISTORY */
WHERE bup.database_name IN (SELECT name FROM master.dbo.sysdatabases)
ORDER BY bup.database_name
Next Steps
  • Take the above code and execute against your instance, making certain to insert the correct database name if you want to filter it to a specific database


Last Updated: 2009-05-08


get scripts

next tip button



About the author
MSSQLTips author Thomas LaRock Thomas LaRock is a Head Geek at SolarWinds and a Microsoft Certified Master, Microsoft Data Platform MVP, VMware vExpert, and a former Microsoft Certified Trainer with over 20 years’ experience.

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
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Wednesday, September 18, 2013 - 8:34:00 PM - Jay Meneses Back To Top

No need to calculate TIME by yourself, let SQL calculate it for you dynamically, I also included Backup Size in MB.  Use my modified code below if you want.  Thanks.

 

DECLARE @dbname sysname
SET @dbname = "PPLNET_TR" --set this to be whatever dbname you want
SELECT bup.user_name AS [User],
 bup.database_name AS [Database],
 bup.server_name AS [Server],
 bup.backup_start_date AS [Backup Started],
 bup.backup_finish_date AS [Backup Finished],
 
 
CAST(((bup.compressed_backup_size)* 0.00000095367432) AS DECIMAL(15,2)) as BackupSizeInMB

, Cast(DAtepart(hour,(bup.backup_finish_date - bup.backup_start_date)) as varchar) + ' hour(s) '
+ Cast(DAtepart(mi,(bup.backup_finish_date - bup.backup_start_date)) as varchar) + ' minute(s) '
+ Cast(DAtepart(ss,(bup.backup_finish_date - bup.backup_start_date)) as varchar) + ' second(s) '
AS [Total Time]

 --,CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/3600 AS varchar) + ' hours, '
 --+ CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/1300 AS varchar)+ ' minutes, '
 --+ CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))%60 AS varchar)+ ' seconds'
 --AS [Total Time2]
FROM msdb.dbo.backupset bup
WHERE bup.backup_set_id IN
  (SELECT backup_set_id FROM msdb.dbo.backupset
  WHERE database_name = ISNULL(@dbname, database_name) --if no dbname, then return all
  AND type = 'D') --only interested in the time of last full backup)
  --GROUP BY database_name)
/* COMMENT THE NEXT LINE IF YOU WANT ALL BACKUP HISTORY */
AND bup.database_name IN (SELECT name FROM master.dbo.sysdatabases)
ORDER BY bup.database_name

 


Wednesday, September 18, 2013 - 8:26:41 PM - Jay Meneses Back To Top

USE THIS TO GET THE CORRECT TIME CONSISTENTLY

Cast(DAtepart(hour,(bup.backup_finish_date - bup.backup_start_date)) as varchar) + ' hour(s) '
+ Cast(DAtepart(mi,(bup.backup_finish_date - bup.backup_start_date)) as varchar) + ' minute(s) '
+ Cast(DAtepart(ss,(bup.backup_finish_date - bup.backup_start_date)) as varchar) + ' second(s) '
AS [Total Time]


Thursday, June 28, 2012 - 5:12:10 AM - Dinesh Vishe Back To Top

type  D- Full backup

        I -- Transcation backup

        L ---transcation


Wednesday, June 13, 2012 - 11:16:53 AM - Michael Marques Back To Top

Could you include the average time it took to do the backup?

 

Thanks Mike...


Tuesday, February 15, 2011 - 8:39:45 PM - Ashok Back To Top

This code is little buggy, when there is date is changed. Specially when date backup starts 11:00PM and finsihes after mid night, say 2:00AM. Then code returns incorrect minutes.

Here is the code modified.

-------------------------------------------------

DECLARE @dbname sysname
SET @dbname = NULL --set this to be whatever dbname you want

SELECT bup.user_name AS [User],
 bup.database_name AS [Database],
 bup.server_name AS [Server],
 bup.backup_start_date AS [Backup Started],
 bup.backup_finish_date AS [Backup Finished],

(CONVERT(varchar(6), DATEDIFF(s,  bup.backup_start_date, bup.backup_finish_date)/3600) + 'H:' + RIGHT('0' + CONVERT(varchar(2), (DATEDIFF(s,  bup.backup_start_date, bup.backup_finish_date) % 3600) / 60), 2) + 'M:' + RIGHT('0' + CONVERT(varchar(2), DATEDIFF(s,  bup.backup_start_date, bup.backup_finish_date) % 60), 2)+'S')
 AS [Total Time]
FROM msdb.dbo.backupset bup
WHERE bup.backup_set_id IN
  (SELECT MAX(backup_set_id) FROM msdb.dbo.backupset
  WHERE database_name = ISNULL(@dbname, database_name) --if no dbname, then return all
  AND type = 'D' --only interested in the time of last full backup
  GROUP BY database_name)
/* COMMENT THE NEXT LINE IF YOU WANT ALL BACKUP HISTORY */
AND bup.database_name IN (SELECT name FROM master.dbo.sysdatabases)
ORDER BY bup.database_name

-----------------------------------

Enjoy

Ashok Gupta


Thursday, January 07, 2010 - 2:53:38 PM - phillips_jim Back To Top

Yes, I am interested in the time and date of a restore and how long it took (duration of restore).  I have not been able to find any system table or view that would provide this information.  The article posted on mssqltips about obtaining the last time a restore was done does shown when a restore started but not when it ended and no restore duration.  We do quarterly restore testing and must document this information to satisfy Auditors.  The only way I have been able to determine the completion time is looking at the SQL logs not from any dictionary tables or views.  You would think that Microsoft would have recorded this type of information somewhere.


Tuesday, June 30, 2009 - 7:10:13 AM - TomGroszko Back To Top

What about the opposite. How long did a recovery take?

 

Thanks

Tom Groszko


Friday, May 22, 2009 - 11:59:48 AM - handle1 Back To Top

The minutes are not correct when the number of seconds is greater than or equal to 3600.  You need another modulo 60.

+ CAST(((CAST(DATEDIFF(ss, bup.backup_start_date, bup.backup_finish_date) AS int))/60)%60 AS varchar)+ ' minutes, '


Friday, May 22, 2009 - 10:51:42 AM - PratapPrabhu Back To Top

Hi,

I use something very similar, except that it shoots a HTML formatted email twice a day listing the Last FullBackup and subsequent Transaction Logs. It is published on SQL Server Central. Please do check it out http://www.sqlservercentral.com/scripts/sp_send_dbmail/66701/

The reason I am posting this comment is that not only is the time taken important but also the fact that the backup did happen. Listing a Full Backup + Subsequent transaction logs means your database restoration path is visible to you and you know if you are in trouble or not if a backup was missed.

Its great to see a like minded coder online.


Thanks
Pratap

 


Friday, May 22, 2009 - 9:21:49 AM - RobertLDavis Back To Top

Just curious why you chose to use "Cast(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/3600" instead of "DATEDIFF(h, bup.backup_start_date, bup.backup_finish_date)". Or you could have used a CTE to only perform the DateDiff once and then performed the calculations on the calculated seconds.

 Also, DateDiff returns an int value. You don't need to cast it as int.

 Otherwise, good information!!


Friday, May 22, 2009 - 9:20:06 AM - PaulNations Back To Top
OK, now I'm worried. I've been having trouble with some transaction logs growing extremely large and have been working to control that when I saw this tip. So I ran the query and learned that one of my smaller databases (300 MB) took the longest 1 min 23 sec to backup while my largest database (14+ GB) took only 1 min 8 sec. So I looked at the underlying table and found the column [backup_size] and included that in Mr. LaRock's query. And boy was I surprised to learn that the backup size for my 14+ GB database is only 6656. I'm guessing that's bytes because some values from much earlier in this database's life are reasonable. The reasonable numbers occur when the [is_snapshot] value is 0. What am I seeing with these low sizes?


download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools