0

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

1
  • 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, 2010 at 16:12

2 Answers 2

2

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

SELECT * 
FROM [fn_trace_gettable](@filepath, DEFAULT)
WHERE TextData LIKE '%RESTORE DATABASE%'
ORDER BY StartTime DESC;
1
  • 3
    D'oh! It was me! Aug 9, 2010 at 18:11
0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .