Last week I had a situation to check & confirm some data in one of the master table with current live database with two-week old data so obviously I have to restore two-week or older backup somewhere and check the data of the master table from restored database to current live database.
We have one development server and we used to restore live database there as and when needed so I wanted to check when was the live database restored in development environment last time. If database was restored in last 15 days, I have to take one of the two-week old backup and restore it in development environment. If development database wasn’t restored in last two weeks, I can directly check and compare data of master table and save my few hours. So I have developed one TSQL script which can give me the history of my database restore.
I have used some system table of MSDB database in SQL Server because backup/restore history of databases resides in MSDB database. Here is the list of system table resides in MSDB database which I have used to find database restore date in SQL Server along with its official definition given by Microsoft in BOL.
Click Here to read full article.