Use this script to display the sql restore history for a single database.

For Backup history read  Display sql backup history for a single database

The script doesn’t display all information in the tables. It contains enough  restore  information – to make some decisions. Such as:

a)       Which LSN was restored?

b)       What type of restore ?

c)       Has there been a SQL Restore ?

USE use [myDatabase]
GO
select bus.server_name as 'server',rh.restore_date,bus.database_name as 'database',
  CAST(bus.first_lsn AS VARCHAR(50)) as LSN_First,
CAST(bus.last_lsn AS VARCHAR(50)) as LSN_Last,
CASE rh.[restore_type]
WHEN 'D' THEN 'Database'
WHEN 'F' THEN 'File'
WHEN 'G' THEN 'Filegroup'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Log'
WHEN 'V' THEN 'Verifyonly'
END AS rhType
FROM msdb.dbo.backupset bus
INNER JOIN msdb.dbo.restorehistory rh ON rh.backup_set_id = bus.backup_set_id
WHERE bus.database_name = DB_NAME()  

Read More

SQL Server faster restores with instant file initialisation

Display sql backup history for a single database

Differential backup cannot be restored . BACKUP with COPY_ONLY ...