regarding sql server transaction log

if some one delete any object from my database like table,view,sp etc then how can get those detail like who delete and when开发者_开发问答 delete from transaction log. is it possible. please tell me easy way to read transaction log as a result i can get those detail properly.

thanks

---

No, ransaction log was created for different purposes. There are some product different vendors which is trying to get information from transaction log, but it is not right way.

who delete and when delete

If you need this information you need to create triggers to table for delete or update and collect this information.

If you use MS SQL 2008 you can use Change Data Capture feature.

---

Apparently you could use a third part product such as Apex SQL Log, although personally I have not used it.

Dependant on how recent the incident occured, you may also be able to extract the information you require from the built in reports in SQL Server 2005 such as the Schema Changes History Report. This information is accessable to you via means of the Default Trace. See using the Default Trace for details.

What you really need to take away from your incident is to use the lesson to devise a schema audit strategy for your environment. There are plenty of articles on the internet that detail how this can be achieved using Triggers. For example see Using DDL Triggers in SQL Server 2005 to Capture Schema Changes

---

You can restore the database (without overwriting it!) from a full backup / transaction log backup and then copy the deleted objects from there. It's good practice to save the source code for your stored procedures, views and tables outside the database, usually in a source control system, so you don't have to restore database backup to get them.

---

You can use either DDL triggers or The SQL Server Audit feature

DDL triggers fire on CREATE, ALTER, DROP, and operations related to database object security settings (e.g. GRANT, DENY…)

In the following example, a DDL trigger tracks the CREATE, ALTER, and DROP operations executed on database tables, stored procedures, functions, and views. The trigger example uses a previously created repository table (DDL_Events_by_DDL_TRIGGER) with appropriate rows

CREATE TRIGGER DDL_TRIGGER ON DATABASE
FOR CREATE_TABLE ,
   ALTER_TABLE ,
   DROP_TABLE ,
   CREATE_PROCEDURE ,
   ALTER_PROCEDURE ,
   DROP_PROCEDURE ,
   CREATE_FUNCTION ,
   ALTER_FUNCTION ,
   DROP_FUNCTION ,
   CREATE_VIEW ,
   ALTER_VIEW ,
   DROP_VIEW
AS
DECLARE
    @event xml;
 SET
 @event = EVENTDATA();
 INSERT INTO DDL_Events_by_DDL_TRIGGER
 VALUES
 (
 REPLACE(CONVERT(varchar(58),
 @event.query('data(/EVENT_INSTANCE/PostTime)')), 'T', ' ')
 ,
 CONVERT(varchar(185),
 @event.query('data(/EVENT_INSTANCE/LoginName)'))
 ,
 CONVERT(varchar(185),
 @event.query('data(/EVENT_INSTANCE/DatabaseName)'))
 ,
 CONVERT(varchar(185),
 @event.query('data(/EVENT_INSTANCE/SchemaName)'))
 ,
 CONVERT(varchar(185),
 @event.query('data(/EVENT_INSTANCE/ObjectName)'))
 ,
 CONVERT(varchar(185),
 @event.query('data(/EVENT_INSTANCE/ObjectType)'))
 ,
 CONVERT(varchar(max),
 @event.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
 );

The repository table will contain (as specified in the trigger) DDL operations on the database schema, along with information about who, when, and what was altered

Another native method that can be used to determine whether a SQL Server database has been altered is the SQL Server Audit feature. The feature was introduced in SQL Server 2008 and it collects both server and database level actions raised by the SQL Server Extended Events feature. However, the database level action groups are available in SQL Server Enterprise and Developer editions only