SQL Server Restore history (last restore date)

pts.
Tags:
SQL Server
I am trying to find when the last time a database has been restored. We have several SQL servers. I have formed the following query based on an article I found. It worked great on the first one I tried, but wasn't recent enough. When I tried to go to other servers looking for a more recent version, I found there were no records at all in the RESTOREHISTORY table. I had been logged in to each as sa, so I don't believe its a permissions problem. Is there a way that someone can prevent the record from being written when they restore? How can these databases exists with thousands of records without having been restored?
USE MSDB GO SELECT TOP 1 * FROM RESTOREHISTORY WITH (nolock) WHERE (DESTINATION_DATABASE_NAME = 'RKDB') ORDER BY RESTORE_DATE DESC 


Thanks!

Answer Wiki

Thanks. We'll let you know when a new response is added.

RestoreHistory in msdb can be deleted by anyone with the required privileges or the command sp_delete_backuphistory. Could this be your problem?

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Scott00
    Thanks for your reply. That's what I thought originally, but couln't understand why anyone would do that, especially to every database on the server, and for several different servers. I finally found the reason for the empty history today. It turns out our DBA creates "snapshots" from production, copies them to development and then "attaches" them from our Dev SQL Servers. Therefore they are not "restores". Do you know if this process of "attaching" would leave a history somewhere?
    0 pointsBadges:
    report
  • Dbdisciple
    The scope of an "attachment" is the database only, so msdb data cannot be expected to follow - and it makes sense because the restore history refers to another server and different names, paths, etc. You can write some custom scripts if this is required for a unique situation. Restorehistory maintains information of what was RESTORED on the current server, not the history of a database.
    0 pointsBadges:
    report
  • Dbdisciple
    Sorry, I didn't answer the 2nd part of your question. Though a little clumsy, if you attach a database, you can look for the line "Starting up database 'xxxx'" in SQL Server Log either via Enterprise Manager or the text file sitting on your usual instal path for SQL Server.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following