March 26, 2008 at 1:03 am
Looks like someone at work accidentally deleted a DB that was on its way out to the client in a couple of days.
Everyone here logs on with the same authentication details.
I'm in a fix now and I'd atleast like to know who must've deleted it.
All backup files have gotten deleted too!
March 26, 2008 at 2:53 am
If all your files has been wiped, then I'd say that all the traces as to who dunnit is wiped as well.
Regardless of that, since all log on as the same user(?), you wouldn't know who 'really done it' anyway..?
/Kenneth
March 26, 2008 at 3:12 am
Do the users have to go onto a server (RDC etc) to access the databases or do they do it through a client?
Look at the security log in event viewer for the time of the deletion to see if it tells you the host name of any user logging on to the server.
You'll probably have to go down the detective route and try to get someone to admit to it.
When I started where I work now it took me ages to stop everyone using a generic login, now they can't log in as that user, even on development machines, I had a few incidents I never got to the bottom of because no one would admit to stopping a production server etc and you can't go round acusing people if they won't admit it, it's a very difficult situation to be in, I just complained a lot about the fact everyone uses the same login and that it's an accident waiting to happen and when it did I was able to turn round and say 'I told you so', at which point they let me stop users using generic username, it really annoyed a lot of people but that's what being a DBA is about!!!
How many users can access the database, have you asked them, do they know it happened, if not just ask the users who was logged onto the server at the rough time it happened.
March 26, 2008 at 3:44 am
Buxton69 (3/26/2008)
Do the users have to go onto a server (RDC etc) to access the databases or do they do it through a client?
It has happened through a client and I don't think anyone was sitting at the server m/c.
Look at the security log in event viewer for the time of the deletion to see if it tells you the host name of any user logging on to the server.
I'm going to check this right away. Does it actually mention 'DELETE' operations and host name too?
When I started where I work now it took me ages to stop everyone using a generic login, now they can't log in as that user, even on development machines
I know what you mean now.. I'm just a few months old at this.. but great lesson this one!
How many users can access the database, have you asked them, do they know it happened, if not just ask the users who was logged onto the server at the rough time it happened.
Well, as you can imagine, everyone's acting like I'm talking an alien language.. no one seems to know a thing!
March 26, 2008 at 3:46 am
I've managed to recover the ldf file using un-delete.. anything I can do with this? I can't get the mdf though..
March 26, 2008 at 4:16 am
It won't mention delete in the security log, only users that accessed the server, depending on the set up it might give you a host name or it might just list the server name as the host name but it's worth a look.
You won't be able to do anything with the ldf file alone, did you not take any backups to tape, have you tried undeleting the backup files?
March 26, 2008 at 4:26 am
The default trace should show the drop operation, including the hostname and spid where it came from.
For example:
SELECT * FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_1.trc',0)
March 26, 2008 at 5:05 am
It shows me User as 'N/A' in the Event Viewer!!!
And I just see NULLS everywhere in the log trace!
What am I doing wrong?
Un-delete isn't able to recover anything but the ldf.. no mdf.. no bak!
And yea.. the backups were only on the server m/c! Big mistake, right?
March 26, 2008 at 5:31 am
I should have qualified the fn_trace_gettable comment. Was the database still attached and you suspect someone used DROP DATABASE? Then the default trace should show that. If the files were already detached then no.
EventClass 47 would show the object deletion. Make sure to use your trace file name(s).
SELECT starttime,spid,hostname, eventclass,databasename
FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_1.trc',0)
WHERE eventclass = 47
But as far as file recovery you might want to try other recovery products to see if you get different results. Just make sure to load them on a drive different from the one you're trying to recover from; Ontrack[/url].
March 26, 2008 at 6:06 am
Default trace would help you in such cases if you are on SQL 2005. very few knows the power of it.
March 26, 2008 at 7:41 am
Balmukund Lakhani (3/26/2008)
Default trace would help you in such cases if you are on SQL 2005. very few knows the power of it.
Heh... don't tease us, now. Would you mind sharing how to use it? Or maybe a URL that shows how to use it?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 26, 2008 at 8:07 am
One thing to bear in mind regarding the default/blackbox traces, is that they have a low file-rollover threshold and on busy systems, may only show very short windows of activity...
It's worth it to spend the time demystifying Profiler, IMO. I'm no guru with it by any means, but I have a very good understanding of it's workings and how to use the data and it's a huge help in many aspects.
Your friendly High-Tech Janitor... 🙂
March 26, 2008 at 8:42 am
If the database AND the backups are deleted, it doesn't sound like an accident. Are you sure it was backed up at all ? If you've only been at it a few months, who was doing the DBA work before you ?? Anyone to ask there with more experience or knowledge ?
I did have a developer storm down here 1 day wanting to know where his 3 stored procedures went. Luckily, I have Compliance Manager installed, so it showed me that HE deleted them ... He didn't even realize it. So it's possible that someone deleted it and doesn't realize it.
A bit late now, but it's time to review your security & backup procedures. (or lack of)
March 26, 2008 at 10:52 am
The default trace should show the drop operation, including the hostname and spid where it came from.
Todd,
I am not sure how this would work. This function is used to import trace files from SQL profiler. In order for this to work, the OP would have had to been running profiler.
An I missing something?
March 26, 2008 at 10:59 am
This was news to me as well but there is a default trace running in SQL 2005, something I knew nothing about, it looks to be invaluable, I wish I knew about it before:
Viewing 15 posts - 1 through 15 (of 44 total)
You must be logged in to reply to this topic. Login to reply