How to find out how long a SQL Server database backup took?

 

DECLARE @dbname SYSNAME

SET @dbname = 'RealDB'

 --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

 ref : mssqltips.com