How to: Check Estimate Completion Time for Backup/Restore Process

The following script will be helpful when you wish to check the estimate completion time for the backup/restore process running at the background of SQL Server.

SELECT 
	command
	, s.text
	, start_time
	, percent_complete
	, CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
		+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
		+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time
	, CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
		+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
		+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go
	, dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM 
	sys.dm_exec_requests r
	CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE 
	r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')

How to: Get Database Backup History For A Single Database

I recently read an article Finding Last Backup Time for All Database from SQLAuthority.com. I found this useful T-SQL scripts.

USE AdventureWorks
GO
-- Get Backup History for required database
SELECT TOP 100
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() -- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date
GO

It is a very neat script. The picture below show the backup history for single database adventureworks only. The same can be ran for multiple database as well if you just remove the WHERE condition.