Backup History

We can get all the information for this task out of below msdb System Table: 

      msdb.dbo.backupset.  - Provides information concerning details of the backup process .The important columns of note for this object are itemized below. 
§  database_name - name of the database the backup file pertains to.
§  type - Type of backup process performed:
·         D = Database
·         F = File or Filegroup
·         G = Differential File
·         I = Differential (database)
·         L = Transaction Log
§  backup_finsh_date - time when the backup process completed.
§  backup_start_date - time the backup process was initiated.
§  name - name of the backupset
§  user_name - user performing the backup process
§  expiration_date - date the backup expires

SQL Scripts 1

SELECT
      sys.sysdatabases.Name AS DATABASE_NAME,
      COALESCE(CONVERT(VARCHAR(12),MAX(msdb.dbo.backupset.backup_finish_date), 101),'NEVER') AS LAST_DATABASE_BACKUP_DATE
FROM
      sys.sysdatabases
      LEFT OUTER JOIN msdb.dbo.backupset
      ON msdb.dbo.backupset.database_name = sys.sysdatabases.name
GROUP BY
      sys.sysdatabases.Name

SQL Scripts 2

SELECT
      msdb.dbo.backupmediafamily.physical_device_name AS FILE_LOCATION,
      msdb.dbo.backupset.database_name AS DATABASE_NAME,
      CAST(CAST(msdb.dbo.backupset.backup_size / 1000000 AS INT) AS VARCHAR(14))+ ' ' + 'MB'  AS BACKUP_SIZE,
      CAST(DATEDIFF(second, msdb.dbo.backupset.backup_start_date,      msdb.dbo.backupset.backup_finish_date) AS VARCHAR(4)) + ' '+ 'Seconds' AS BACKUP_TIME_TAKEN,
      msdb.dbo.backupset.backup_start_date AS BACKUP_DATE_TIME,
      CASE msdb.dbo.backupset.[type]
      WHEN 'D' THEN 'Full'
      WHEN 'I' THEN 'Differential'
      WHEN 'L' THEN 'Transaction Log'
      END AS BACKUP_TYPE,
      msdb.dbo.backupset.server_name AS SERVER_NAME
FROM
      msdb.dbo.backupset
      INNER JOIN msdb.dbo.backupmediafamily 
      ON msdb.dbo.backupset.media_set_id = msdb.dbo.backupmediafamily.media_set_id

SQL Scripts 3

SELECT
      ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(Backup_finish_date)))), 'NEVER') AS DAY_LAST_BACKUP_TAKEN,
      master.dbo.sysdatabases.name AS DATABASE_NAME,
      ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') AS LAST_BACKUP_DATE
FROM
      master.dbo.sysdatabases
      LEFT OUTER JOIN msdb.dbo.backupset
      ON msdb.dbo.backupset.database_name = master.dbo.sysdatabases.name
      AND msdb.dbo.backupset.type = 'D'
GROUP BY
      master.dbo.sysdatabases.Name

SQL Scripts 4

SELECT
      database_id AS DATABASE_ID,
      convert(varchar(25), sys.databases.name) AS DATABASE_NAME,
      convert(varchar(10), Databasepropertyex(name, 'status')) AS DATABASE_STATUS,
      (SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = sys.databases.name AND type_desc = 'rows') AS DATAFILES ,
      (SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = sys.databases.name AND type_desc = 'rows') AS DATA_FILE_SIZE_IN_MB,
      (SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = sys.databases.name AND type_desc = 'log') AS LOG_FILES,
      (SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = sys.databases.name AND type_desc = 'log') AS LOG_FILE_SIZE_IN_MB,
      CASE compatibility_level
      WHEN 60 THEN '60 (SQL Server 6.0)'
      WHEN 65 THEN '65 (SQL Server 6.5)'
      WHEN 70 THEN '70 (SQL Server 7.0)'
      WHEN 80 THEN '80 (SQL Server 2000)'
      WHEN 90 THEN '90 (SQL Server 2005)'
      END AS COMPATIBILITY_LEVEL,
      CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS DATABASE_CREATION_DATE,
      CASE WHEN is_fulltext_enabled = 1 THEN 'Fulltext enabled' ELSE ' ' END AS FULL_TEXT,
      CASE WHEN is_auto_close_on = 1 THEN 'autoclose' ELSE ' ' END AS AUTOCLOSE,
      CASE WHEN is_read_only = 1 THEN 'read only' ELSE ' ' END AS  READ_ONLY,
      CASE WHEN is_auto_shrink_on = 1 THEN 'autoshrink' ELSE ' ' END AS AUTOSHRINK,
      CASE WHEN is_auto_create_stats_on = 1 THEN 'auto create statistics' ELSE ' ' END AS AUTO_CREATE_STATISTICS,
      CASE WHEN is_auto_update_stats_on = 1 THEN 'auto update statistics' ELSE ' ' END AS AUTO_UPDATE_STATISTICS,
      CASE WHEN is_in_standby = 1 THEN 'standby' ELSE ' ' END AS STAND_BY,
      CASE WHEN is_cleanly_shutdown = 1 THEN 'cleanly shutdown' ELSE ' ' END AS CLEANLY_SHOUTDOWN,
      user_access_desc AS USER_ACCESS,
      recovery_model_desc AS  RECOVERY_MODEL
FROM
      sys.databases