This is the first time I am posting to this forum.
I am an amateur user to SQL enterprise manager administration.
I want to log all the activities that hit SQL enterprise manager. What i mean is that i want to record all the events that happen in enterprise manager. for example, i want to record all the events that occur while creating, editing, deleting a DTS package or whatsoever in the enterprise manager.
It was so happened that some of the DTS packages on one of our servers were accidentally deleted / lost??. I want to trace this and how it has occured? Now as a post-mortem, i want to find related possibilities of this event to occur.
Can anyone please help me how should i configure enterprise manager to record all events?? I was currently using SQL SERVER 2000 Enterprise edition with SP3.
First of all, if you're backing up msdb (which you should), then you can restore it to the date when you know the package was still there. Second, if the only thing you care is DTS packages, then monitor activities against sysdts% tables in msdb.
This is not a configuration/setting that you make via Enterprise Manager. It has much more to do with the msdb database and how often you are backing it up.
It also has to do with how well you have set up administrative rights and privileges for your server. Off the top of my head, I do not recall the permissions required to manipulate DTS packages. But if you are the only who is supposed to be editing them, I'm sure that you can set up security appropriately.
If need be, you can set up a separate server to use as a development/test platform and promote packages created by other users from it to your production machine.
They r very encouraging. I do take a backup of the msdb database regularly and i managed to restore the packages.
out of curiosity, is there any possibility for the event(in our case deletion of packages) to be recorded in the msdblog file?? i want to run a audit and know the root cause for this event to happen, so that necessary measures can be taken for the future.
if the deletion of packages/databases can be recorded in the msdblog file, then how to view the log file? is there any special viewer software available for that or any other solution?