Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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

share|improve this question

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

2 Answers 2

up vote 2 down vote accepted

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;
share|improve this answer
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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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