SQL SERVER – Get Last Restore Date

Let us learn how to get the last restore date for the backup in SQL Server. Here is a very simple script for the same.

SELECT
[d].[name] AS [Database],
[d].[create_date],
[d].[compatibility_level],
rh.restore_date,
rh.restore_history_id,
rh.restore_type
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.[restorehistory] rh
ON rh.[destination_database_name] = d.Name
ORDER BY [Database], restore_history_id

Well, that’s it. It will give you all the necessary history from the msdb database.

If you want to clear any backup or restore history for the database, you can just run the following simple script and it will remove your history from the msdb database.

Here is an example of how you can remove the history for the AdventureWorks Database.

USE msdb;
GO
EXEC sp_delete_database_backuphistory @database_name = 'AdventureWorks'; 

Well, that’s it for today. Let me know your thoughts about this blog post.

Here are my few recent videos and I would like to know what is your feedback about them. Do not forget to subscribe SQL in Sixty Seconds series.

• MAX Columns Ever Existed in Table – SQL in Sixty Seconds #182
• Tuning Query Cost 100% – SQL in Sixty Seconds #181
• Queries Using Specific Index – SQL in Sixty Seconds #180
• Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
• One Scan for 3 Count Sum – SQL in Sixty Seconds #178
• SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
• COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176

Reference: Pinal Dave (http://blog.SQLAuthority.com)