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





Comments For This Article




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

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 (26850)

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 (18242)

type  D- Full backup

        I -- Transcation backup

        L ---transcation


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

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 (12938)

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 (4681)

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 (3664)

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 (3425)

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 (3424)

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 (3423)

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 (3422)
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


Recommended Reading

Simple script to backup all SQL Server databases

Script to retrieve SQL Server database backup history and no backups

How to monitor backup and restore progress in SQL Server

Backup to multiple files for faster and smaller SQL Server files

Changing the default SQL Server backup folder





get free sql tips
agree to terms


Learn more about SQL Server tools