Using system object we can find the backup history details.
Backupmediafamily: Contains one row for each media family. If a media family resides in a mirrored media set, the family has a separate row for each mirror in the media set. This table is stored in the msdb database.
Backupset: Contains a row for each backup set. A backup set contains the backup from a single, successful backup operation. RESTORE, RESTORE FILELISTONLY, RESTORE HEADERONLY, and RESTORE VERIFYONLY statements operate on a single backup set within the media set on the specified backup device or devices.
Find backup history last one day
SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, bs.server_name,
BS.database_name,
BS.backup_start_date,
BS.backup_finish_date,
DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) AS 'Duration (sec)',
CASE BS.type WHEN 'D' THEN 'Database' WHEN 'L' THEN 'Log' When 'I' THEN 'Differential database'
WHEN 'F' THEN 'File/Filegroup' WHEN 'G' THEN 'Differential File' WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial' END AS backup_type,
ROUND(((bs.backup_size/1024)/1024),2) AS 'Backup Size (MB)',
ROUND(((bs.compressed_backup_size/1024)/1024),2) AS 'Compressed Backup Size (MB)',
BS.backup_size,
BMF.physical_device_name,
BS.name AS backupset_name
,bs.is_copy_only
,bs.compressed_backup_size
,bs.recovery_model
,bs.is_password_protected
,bs.last_lsn
,bs.compatibility_level
,bs.database_version
FROM msdb.dbo.backupmediafamily BMF
INNER JOIN msdb.dbo.backupset BS ON BMF.media_set_id = BS.media_set_id
WHERE (CONVERT(datetime, BS.backup_start_date, 102) >= GETDATE()-1 )
ORDER BY BS.backup_finish_date desc
Find Backup history with different column name
SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, bs.server_name,
BS.database_name,
BS.backup_start_date,
BS.backup_finish_date,
DATEDIFF(MINUTE, bs.backup_start_date, bs.backup_finish_date) AS 'Duration (min)',
CASE BS.type WHEN 'D' THEN 'Database' WHEN 'L' THEN 'Log' When 'I' THEN 'Differential database'
WHEN 'F' THEN 'File/Filegroup' WHEN 'G' THEN 'Differential File' WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial' END AS backup_type,
ROUND(((bs.backup_size/1024)/1024),2) AS 'Backup Size (MB)',
ROUND(((bs.compressed_backup_size/1024)/1024),2) AS 'Compressed Backup Size (MB)',
BS.backup_size,
BMF.physical_device_name,
BS.name AS backupset_name
,bs.is_copy_only
,bs.compressed_backup_size
,bs.recovery_model
,bs.is_password_protected
,bs.last_lsn
,bs.compatibility_level
,bs.database_version
FROM msdb.dbo.backupmediafamily BMF
INNER JOIN msdb.dbo.backupset BS ON BMF.media_set_id = BS.media_set_id
WHERE (CONVERT(datetime, BS.backup_start_date, 102) >= GETDATE()-1 )
ORDER BY BS.backup_finish_date desc
-- database backup not taken
SELECT S.NAME AS database_name,
'Nobackups' AS [Backup Age (Hours)]
FROM master.dbo.sysdatabases S LEFT JOIN msdb.dbo.backupset B
ON S.name = B.database_name
WHERE B.database_name IS NULL AND S.name <> 'tempdb'
ORDER BY B.database_name
To get a list of the most recent full backup of all the databases:
--To get a list of the most recent full backup of all the databases:
SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D'
GROUP BY
msdb.dbo.backupset.database_name
Backup percentage complete
--- backup percentage complete
SELECT query = a.text, start_time, percent_complete,
eta = dateadd(second,estimated_completion_time/1000, getdate())
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command LIKE 'BACKUP%'
Reference:
