1

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?

4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.