Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

How to display sql restore history for a single database

06 July,2012 by Tom Collins

Use this script to display the sql restore 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 [myDatabase]
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 '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 on SQL Server Restores and Backups

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 ...


Author: Tom Collins (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on How to display sql restore history for a single database | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer