How to Get Backups & Restores Start and Finish Times? I backup my databases from prod environment to file server. After taking my backups, i restore them to my report server. And i want to know that if i can get information about when backup and restore has started and finished or not. How can i query how long it takes to complete its each process?
The backups of the database can be retrieved using the following statement:

--------------------------------------------------------------------------------- 
--      Database Backups for all databases For Previous Week 
--------------------------------------------------------------------------------- 
SELECT  

/* Columns for retrieving information */

   -- CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SRVNAME, 
   msdb.dbo.backupset.database_name,  
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date, 
   msdb.dbo.backupset.expiration_date, 
   CASE msdb..backupset.type  
       WHEN 'D' THEN 'Full'  
       WHEN 'I' THEN 'Diff'
       WHEN 'L' THEN 'Log'  
   END AS backup_type,  
   -- msdb.dbo.backupset.backup_size / 1024 / 1024 as [backup_size MB],  
   -- msdb.dbo.backupmediafamily.device_type,
   msdb.dbo.backupmediafamily.physical_device_name,
   -- msdb.dbo.backupmediafamily.logical_device_name,
   -- msdb.dbo.backupset.name AS backupset_name, 
   -- msdb.dbo.backupset.description,
   msdb.dbo.backupset.is_copy_only,
   -- msdb.dbo.backupset.is_snapshot,   

   msdb.dbo.backupset.first_lsn,
   msdb.dbo.backupset.last_lsn,
   msdb.dbo.backupset.database_backup_lsn,
   msdb.dbo.backupset.checkpoint_lsn,
   msdb.dbo.backupset.differential_base_lsn,
   msdb.dbo.backupset.fork_point_lsn,


   msdb.dbo.backupmediaset.name,
   msdb.dbo.backupmediaset.software_name,
   msdb.dbo.backupset.user_name,
   'EOR'

FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset 
   ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
   INNER JOIN msdb.dbo.backupmediaset
   on msdb.dbo.backupmediaset.media_set_id = backupmediafamily.media_set_id


/* ----------------------------------------------------------------------------
        Generic WHERE statement to simplify selection of more WHEREs    
-------------------------------------------------------------------------------*/
WHERE 1 = 1

ORDER BY  
          2 desc, -- backup start
          1,      -- database name
          3 desc  -- backup end





The restore history table only contains when the database was restored and not how long it took. However, if you run the following statement just after the restore has occurred, then you will have an estimate of how long it took:

SELECT 
    rh.destination_database_name AS RestoredDatabaseName,  
    rh.restore_date              AS RestoreDate, 
    GETDATE()                    AS CurrentDateAndTime,
    RIGHT('0' + CAST(CAST(DATEDIFF(ss,rh.restore_date,GETDATE()) AS NVARCHAR(20)) / 3600 AS VARCHAR),2) + ':' +
    RIGHT('0' + CAST((CAST(DATEDIFF(ss,rh.restore_date,GETDATE()) AS NVARCHAR(20)) / 60) % 60 AS VARCHAR),2) + ':' +
    RIGHT('0' + CAST(CAST(DATEDIFF(ss,rh.restore_date,GETDATE()) AS NVARCHAR(20)) % 60 AS VARCHAR),2)                          AS [Restore HH:MM:SS]
FROM msdb.dbo.restorehistory AS rh


This statement basically takes the current date &amp; time (GETDATE()) and the time the restore was performed, and calculates the difference in HH:MM:SS.



Additionaly you can capture the time before and after the restore using GETDATE() as per Dat Nguyen's suggestion and then calculate the difference:

USE [master]
GO
ALTER DATABASE [YOURDATABASE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DECLARE @RestoreStarted  AS DATETIME
DECLARE @RestoreFinished AS DATETIME
SELECT @RestoreStarted=GETDATE() -- capture the date/time before the restore starts
RESTORE DATABASE [YOURDATABASE] FROM  DISK = N'C:\SQL\Backup\YOURDATABASE\FULL\YOURDATABASE_FULL_20191109_223000.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5
SELECT @RestoreFinished=GETDATE() -- capture the date/time after the restore finishes

SELECT TOP 1 rh.destination_database_name AS RestoreDatabase, rh.restore_date AS RestoreDateInRestoreHistory, @RestoreStarted AS RestoreStarted, @RestoreFinished AS RestoreFinished,
RIGHT('0' + CAST(CAST(DATEDIFF(ss, @RestoreStarted,@RestoreFinished) AS NVARCHAR(20)) / 3600 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST((CAST(DATEDIFF(ss, @RestoreStarted,@RestoreFinished) AS NVARCHAR(20)) / 60) % 60 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST(CAST(DATEDIFF(ss, @RestoreStarted,@RestoreFinished) AS NVARCHAR(20)) % 60 AS VARCHAR),2) AS [Restore HH:MM:SS]
FROM msdb.dbo.restorehistory AS rh
WHERE rh.destination_database_name = 'YOURDATABASE'
ORDER BY 2 desc


This returns the following output:

RestoreDatabase | RestoreDateInRestoreHistory | RestoreStarted          | RestoreFinished         | Restore HH:MM:SS
----------------+-----------------------------+-------------------------+-------------------------+--------------------
YOURDATABASE    | 2019-11-29 10:20:43.577     | 2019-11-29 13:16:29.590 | 2019-11-29 13:16:34.800 | 00:00:05


Reference Reading


SQL SERVER – Convert Seconds to Hour : Minute : Seconds Format
restorehistory (Transact-SQL)
backupmediafamily (Transact-SQL)
backupset (Transact-SQL)
backupmediaset (Transact-SQL)