backup script

SELECT

CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,

msdb.dbo.backupset.database_name,

msdb.dbo.backupset.backup_start_date,

msdb.dbo.backupset.backup_finish_date,

--msdb.dbo.backupset.expiration_date,

CASE msdb..backupset.type

WHEN 'D' THEN 'Database'

WHEN 'L' THEN 'Log'

END AS backup_type,

msdb.dbo.backupset.backup_size,

--msdb.dbo.backupmediafamily.logical_device_name,

msdb.dbo.backupmediafamily.physical_device_name

--msdb.dbo.backupset.name AS backupset_name,

--msdb.dbo.backupset.description

FROM msdb.dbo.backupmediafamily

INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) and database_name='Test'

ORDER BY backup_finish_date desc

--msdb.dbo.backupset.database_name,

--msdb.dbo.backupset.backup_finish_date

History of Restore database

SELECT

 rh.destination_database_name AS [Database],

  CASE WHEN rh.restore_type = 'D' THEN 'Database'

  WHEN rh.restore_type = 'F' THEN 'File'

   WHEN rh.restore_type = 'I' THEN 'Differential'

  WHEN rh.restore_type = 'L' THEN 'Log'

    ELSE rh.restore_type 

 END AS [Restore Type],

 rh.restore_date AS [Restore Date],

 bmf.physical_device_name AS [Source], 

 --rf.destination_phys_name AS [Restore File],

  rh.user_name AS [Restored By]

FROM msdb.dbo.restorehistory rh

 INNER JOIN msdb.dbo.backupset bs ON rh.backup_set_id = bs.backup_set_id

 INNER JOIN msdb.dbo.restorefile rf ON rh.restore_history_id = rf.restore_history_id

 INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id

ORDER BY rh.restore_history_id DESC

GO