Our database was recently restored. It caused some real grief. Is there an audit mechanism to determine who executed the most recent restore? Thanks.

migrated from stackoverflow.com Aug 9 '10 at 16:15

This question came from our site for professional and enthusiast programmers.

  • 3
    You'll probably have more and better answers as Serverfault.com - that's where the DBA's and SysAdmins hang out.... – marc_s Aug 9 '10 at 16:12

I just tested a restore and it did seem to be recorded in the default trace. Does this query shed any light? (If the currently running trace file doesn't go back far enough you might need to adjust this somewhat, there will likely be a few more *.trc files to investigate in the log folder)

declare @filepath nvarchar(1000)

SELECT @filepath = cast(value as nvarchar(1000)) FROM [fn_trace_getinfo](NULL)
WHERE [property] = 2 and traceid=1

print @filepath

FROM [fn_trace_gettable](@filepath, DEFAULT)
  • 3
    D'oh! It was me! – Jerry Nixon Aug 9 '10 at 18:11

Unless you have third party backup software that does the task and records it for you or you were running a SQL Profiler trace then about the best you can do is look at the owner of the database, and hope it didn't get changed from whoever restored it. SQL doesn't record who performed the restore in the server log, nor does it report this in the event log.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.