BACKUP DATABASE [DMT_TRD_LIVE] TO  DISK = N'W:\BackUP_DMT_TRD_LIVE\DMT_TRD_LIVE_backup_2021_12_30_154216_7667859.bak' WITH NOFORMAT, NOINIT,  NAME = N'DMT_TRD_LIVE_backup_2021_12_30_154216_7667859', SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'DMT_TRD_LIVE' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'DMT_TRD_LIVE' )

RESTORE VERIFYONLY FROM  DISK = N'W:\BackUP_DMT_TRD_LIVE\DMT_TRD_LIVE_backup_2021_12_30_154216_7667859.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND

SELECT TOP 20 bs.server_name
	,bs.database_name
	,bs.recovery_model
	,convert(NUMERIC(10, 2), backup_size / 1024 / 1024) [backup, Mb]
	,convert(NUMERIC(10, 2), bs.compressed_backup_size / 1024 / 1024) [Compressed (Mb)]
	,bs.backup_start_date
	,bs.backup_finish_date
	,datediff(ss, bs.backup_start_date, bs.backup_finish_date) [Duration (sec)]
	,CASE 
		WHEN datediff(ss, bs.backup_start_date, bs.backup_finish_date) >= 86400
			THEN Convert(VARCHAR(5), datediff(ss, bs.backup_start_date, bs.backup_finish_date) / 86400) + ' days '
		ELSE ''
		END + Convert(VARCHAR(8), DateAdd(Second, datediff(ss, bs.backup_start_date, bs.backup_finish_date), 0), 108) AS [Duration]
	,bs.name
	,bs.user_name
	,CASE bs.type
		WHEN 'D'
			THEN 'Database full'
		WHEN 'I'
			THEN 'Database Differential'
		WHEN 'L'
			THEN 'Log'
		WHEN 'F'
			THEN 'File or Filegroup'
		WHEN 'G'
			THEN 'File Differential'
		WHEN 'P'
			THEN 'Partial'
		WHEN 'Q'
			THEN 'Partial Differential'
		ELSE 'Unknown'
		END 'backup type' -- from BOL
	,bmf.physical_device_name
FROM msdb..backupset bs
INNER JOIN msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE 1 = 1
	AND database_name = 'DMT_TRD_LIVE'
	AND type IN ('D')
ORDER BY backup_finish_date DESC