| Author |
Topic |
|
pdset
Constraint Violating Yak Guru
310 Posts |
 Posted - 2010-02-25 : 20:06:10
|
| Hello Patrons,Sometime back we happened to execute some Stored Procedures and in the process of that we saw one of the Database is totally missing from the instance. This happened some months ago and it is the Ware House server and on a d-day end-user unable to connect to that database, which is then missing, and the whole matter has propped up.Now my request is how to keep track of these events from the server point of view that I can still UNEARTH the MISSING OBJECTS from the Server. Is there any method / procedure that I can adopt to know and still keep the track of them in time to come. EX: I should have a stored procedure / Job which keeps track of the Database objects from a given server and by means of database practises / methods still I could able to surface the MISSING OBJECTS.Can anyone help me in deducing this scenario, which would be great asset and thanks to all. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2010-02-25 : 22:11:19
|
| Thanks for that. however, I have no clue as to how I have to proceed for this MISSING OBJECTS from the Instance to trap them or else can I get any such script please to monitor and still retrace them back.Your expertise will much more appreciated as I dont have any clue to do that. |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-02-25 : 23:36:07
|
| Tara pretty much just told you what you need.Do you have backups?If not, nothing is going to bring it back.As for DDL triggers, see here --> http://msdn.microsoft.com/en-us/library/ms186406.aspx |
|
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2010-02-25 : 23:54:51
|
| I acknowledged that. but I want to to know what would be the steps in either SP or Event or Trigger, which guides me to do.Thanks |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-02-26 : 01:56:56
|
| Your DDL Trigger would prevent objects from being dropped |
|
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2010-02-26 : 02:42:26
|
| Thanks once again.Actually sometime back we have experienced of Dropping a database and was not noticed until the end-user complained of not getting access to the ware house instance.Can you tell as how to identify the dropping as my error logs were not old enough to capture this database drop. |
|
|
|
Jaime
Starting Member
19 Posts |
Posted - 2010-02-26 : 23:31:51
|
| If you are accidentally dropping databases on production servers, then I doubt that any technology to track this event will make your life any better. You really need to control these events with a change control process, not with auditing. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-27 : 00:20:36
|
I disagree. Accidents happen. Having an alert would mean that you restore the database before it becomes an emergency. Once it is an emergency it is hurting the business/someone, and will spoil your day (because whatever promises you made to fix/build stuff is going to be delayed).Depends on your level of paranoia how much stuff you want to track for alerts, and as Jaime says not dropping it in the first place (because of good change controls, and strict permissions) is obviously better than having to get the band-aid out to fix the problem  An Admin Database could have some scheduled tasks that:- Check that all expected databases are present, add any new ones that have appeared, alert for any that have disappeared . Gone offline/read-only/etc .
- Check for all Sprocs / Triggers in each DB. Log any that have changed, or are new. Alert for any that have been dropped. Alert for any changes in databases that are marked as "closed" to DEV (i.e. Any changes to Production databases)
In fact I quite like that last one. I am sure I am not the first, or last, DBA to set about running a revised Sproc and accidentally run it on the wrong database. But it would also catch naive operations, and deliberate abuse.But that is all after-the-fact. As Tara and Russell have said, a DDL trigger could stop it happening in the first place, or log it (and alert if necessary) in-real-time, rather than after-the-fact. |
|
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2010-02-28 : 18:01:39
|
| Thanks all for the discussion so far. I would like to draw my conclusions on what I have done till now. I have checked and Application Even log Viewer and Even Database Error log Files from the ‘Doubtful date’ but couldn’t find evidence as the particular entry of database was last successfully backed up but later there is no written consent for its missing from the instance. This makes me even harder for finding where it went wrong.Can anyone suggest is there any other method that I can still get the information of the MISSING Object? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-02-28 : 19:00:20
|
| drop database wont be logged in eventvwr.again, if you don't have a backup, you're sunk. start recreating it from scratch. at least from a dev copy hopefullyif you want to prevent it in the future, create a ddl trigger to disallow it. |
|
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2010-02-28 : 19:19:52
|
| Rightly managed and I got the DB back on, However, I tried with the Eventvwr ON ALL SERVER but still able to drop the Database.I have used the following code just correct please.IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = 'SURVEILLANCE_database')DROP TRIGGER SURVEILLANCE_databaseON ALL SERVER;GOCREATE TRIGGER SURVEILLANCE_database ON ALL SERVER FOR DROP_DATABASE AS PRINT 'Database Drop failure.' SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')GOThanks |
|
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2010-03-01 : 15:49:54
|
| I have tried but could not succeed as where could be the error,Can anyone please Give me the right code on checking above parameters.Thanks all so far. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-03-01 : 15:55:48
|
| [code]CREATE TRIGGER no_drop_allowedON ALL SERVER FOR DROP_DATABASE AS raiserror('Drop database disallowed', 12, 1) rollbackGO[/code] |
|
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2010-03-01 : 17:32:23
|
| I have tried on to the server but still didn't work.I have restarted the services of the server and implemented then it works but how it still deletes with the system related. How can I still desist in doing this.Thanks all for your patient replies so far. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-03-01 : 19:59:13
|
| you need to be admin on the server to create the trigger. are you? |
|
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2010-03-01 : 21:52:50
|
| Yeah I am (Admin). But Once it created I cannot edit the trigger content.Thanks. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-03-01 : 22:24:03
|
| [code]ALTER TRIGGER SURVEILLANCE_databaseON ALL SERVER FOR DROP_DATABASE AS raiserror('Drop database disallowed', 16, 1) rollbackGO[/code] |
|
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2010-03-02 : 18:12:33
|
| Thanks Russel for the timely aid, However, it hinders me in invoking the trigger content, may be I went wrong somewhere as the services were bone of contention as several times it won't invoke so that I have manually start the services there after it works.ALTER TRIGGER <Name of the trigger> ? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-03-02 : 21:14:21
|
| you don't need to stop the services.i've already given you the exact syntax. what happens when u try it? |
|
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2010-03-03 : 16:05:57
|
| It has worked but I still can't EDIT the trigger which I have created.Thanks Very much for your support. |
|
|
|
Next Page
|
Track Missing DB Objects