Check the backup location, restore order, size, duration in SQL Server

Check the last backup date and location in SQL Server

WITH LastBackUp AS
(
SELECT  bs.database_name,
        bs.backup_size,
        bs.backup_start_date,
        bmf.physical_device_name,
        Position = ROW_NUMBER() OVER( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC )
FROM  msdb.dbo.backupmediafamily bmf
JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id

)
SELECT 
        database_name AS [Database],
        CAST(backup_size / 1048576 AS DECIMAL(10, 2) ) AS [BackupSizeMB],
        backup_start_date AS [Last Full DB Backup Date],
        physical_device_name AS [Backup File Location]
FROM LastBackUp
WHERE Position = 1
ORDER BY [Database];

Check the backup size , order of backup restore, its backup type in SQL Server

with backupdetail as  ( SELECT  B.name as Database_Name,
        ISNULL(Convert(char(11), MAX(backup_finish_date), 113)+ ' ' + CONVERT(VARCHAR(8),MAX(backup_finish_date),108), 'NEVER') as LastBackupDate
        ,BackupSize_GB=CAST(COALESCE(MAX(A.BACKUP_SIZE),0)/1024.00/1024.00/1024.00 AS NUMERIC(18,2))
        ,BackupSize_MB=CAST(COALESCE(MAX(A.BACKUP_SIZE),0)/1024.00/1024.00 AS NUMERIC(18,2))
        ,Backuptype = ( Case  A.type when 'D' then 'FULLBACKUP' when 'L' then 'LOGBACKUP' when 'I' then 'DIFFERNTIALBACKUP' else A.type end )
		,Restoreorder = (case A.type when 'd' then 1 when 'I' then 2 when 'L' then 3 end ) 
		FROM sys.databases B 
			LEFT OUTER JOIN msdb.dbo.backupset A 
                 ON A.database_name = B.name 
                 AND A.is_copy_only = 0
            GROUP BY B.Name, A.type
			)  
			select * from backupdetail where database_name not in  ('master','msdb','model','tempdb') order by 1,6, 2 desc 

Check the more detail like day since last backup, backup duration, backup type – Full, Differential, & Log.

SELECT  @@Servername as [Server_Name],
            B.name as Database_Name, 
            ISNULL(STR(ABS(DATEDIFF(day, GetDate(), MAX(Backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
            ISNULL(Convert(char(11), MAX(backup_finish_date), 113)+ ' ' + CONVERT(VARCHAR(8),MAX(backup_finish_date),108), 'NEVER') as LastBackupDate
            ,BackupSize_GB=CAST(COALESCE(MAX(A.BACKUP_SIZE),0)/1024.00/1024.00/1024.00 AS NUMERIC(18,2))
            ,BackupSize_MB=CAST(COALESCE(MAX(A.BACKUP_SIZE),0)/1024.00/1024.00 AS NUMERIC(18,2))
           ,media_set_id = MAX(A.media_set_id)
          ,[AVG Backup Duration]= AVG(CAST(DATEDIFF(s, A.backup_start_date, A.backup_finish_date) AS int))
           ,[Longest Backup Duration]= MAX(CAST(DATEDIFF(s, A.backup_start_date, A.backup_finish_date) AS int))
            ,Case  A.type when 'D' then 'FULLBACKUP' when 'L' then 'LOGBACKUP' when 'I' then 'DIFFERNTIALBACKUP' else A.type end
            FROM sys.databases B 
			LEFT OUTER JOIN msdb.dbo.backupset A 
                         ON A.database_name = B.name 
                      --  AND A.is_copy_only = 0
                      --  AND (@backup_type IS NULL OR A.type = @backup_type  )
            GROUP BY B.Name, A.type
			order by 4 desc