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?
1 Answer
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