Hi,
I have a SQL Server 2005 database that has been deleted, and I need to discover who deleted it. Is there a way of obtaining this user name?
Thanks, MagicAndi.
+5
A:
If there has been little or no activity since the deletion, then the out-of-the-box trace may be of help. Try running:
DECLARE @path varchar(256)
SELECT @path = path
FROM sys.traces
where id = 1
SELECT *
FROM fn_trace_gettable(@path, 1)
Mitch Wheat
2009-06-25 13:38:03
+1 - Thanks, that's very useful. I just ran that and it gives me about 2 days worth of backups. Is there a way to get more? What options take care of controlling those trace settings?
Wadih M.
2009-06-25 13:58:41
@mitch: that is an awesome tip. it is a very small trace, though. is there any way i can set this up to be a larger trace?
Raj More
2009-06-25 15:31:18
@Tapori: I blogged about SQL Server’s Built-in Traces here: http://mitch-wheat.blogspot.com/2009/01/sql-servers-built-in-traces.html : it's possible to alter the size of the Black Box trace...
Mitch Wheat
2009-06-26 00:43:52
@Mitch - thanks for your answer. Accepted as the answer.
MagicAndi
2009-06-26 11:33:09
+2
A:
The best way to retrieve the information is to restore the latest backup.
Now to discuss how to avoid such problems in the future.
First make sure your backup process is running correctly and frequently. Make transaction log baclup evey 15 mintues or half an hour if it is a higly transactional database. Then the most you lose is a half an hour's worht of work. Practice restoring the database until you can easily do it under stress.
In SQL Server 2008 you can add DDL triggers (not sure if you can do this in 2005) which allow you to log who did changes to structure. It might be worth your time to look into this.
Do NOT allow more than two people admin access to your production database - a dba and a backup person for when the dba is out. These people should load all changes to the database structure and code and all of the changes should be scripted out, code reviewed and tested first on QA. No unscripted, "run by the seat of your pants" code should ever be run on prod.
HLGEM
2009-06-25 13:54:56
+1, this is the answer to the question that you needed to ask before this current question: "how do I prevent and/or deal with someone deleting my database"...
KM
2009-06-25 14:12:10
+1: An excellent answer due to thinking laterally in order to identify the root underlying cause of the issue I.E. a failing due to lack of database platform auditing and management processes.
John Sansom
2009-06-25 16:29:16
@MagicAndi, I aswered part of what you asked which is how to retreive the information. I added the other information because, it may not be possible to get back what was lost if you weren't doing backups, at least you can go to your boss with a plan to avoid a repetition. It might be thing that saves your job if you can't find a way to determine who did the deleting. Sometimes you have provide people with what they need to know or consider not just what they asked.
HLGEM
2009-06-25 18:44:08
HLGEM, I would make two points. 1. You did NOT answer the question asked, i.e. how to identify the user who deleted the database in the first place? How will restoring the last backup do this? 2. If you had wanted to answer a question about preventing this problem occurring again, you should have asked this as a separate question, and linked to this question in your answer to my question. Lateral thinking or not, you were answering a different question; surely the point to SO is to provide answers to specific questions?
MagicAndi
2009-06-26 11:31:48
you asked and I quote:"Is there a way of retrieving this information?" I answered that part of the question. Restoring the backups is how to do that. You can be sure I won't be answering any more of your questions since you clearly are incapable of accepting that the information you may need isn't the necessarily the direct answer to your question. BTW two other people agreed my answer was appropriate.
HLGEM
2009-06-26 13:16:49
HLGEM, Given that you never answered the question that I had asked in the first place, I am sure I can live without you answering any more of my questions in the future.
MagicAndi
2009-06-29 08:12:07
+1
A:
I would first ask everyone who has admin access to the Sql Server if they deleted it.
DancesWithBamboo
2009-06-25 15:26:55
And if I had to ask more than three people, I would also ask the DBA why there are so many...
stephan
2009-06-29 12:24:08