Post reply

Data Deletion in DB

  • July 26, 2012 at 11:24 pm

    #259503

    Hi Team,

    Some data deletion was happend in our Environment.

    Now i m planning to implementing AUDIT of the database & Server level any suggestions

    In various level we have to do AUDIT any body please let me know what are various levels..

    How can i start to do the AUDIT can you please help me.

    Thanks

  • July 27, 2012 at 9:59 am

    #1518612

    Depending on what you want to capture, you can use any of the three types of Auditing available. CDC, CT and SQL Audit. There are articles here in this website that explains all the three Auditing methods with examples.

    -Roy

  • July 28, 2012 at 8:40 am

    #1518877

    It looks like you want to audit DML, the options available will depend on the server version.

    Change data capture and SQL Audit are SQL 2008 features and therefore not available in SQL 2005.

    if you are working with SQL 2005 you may have to use good old server side trace. The challenge is that it adds a overhead unless it is configured to be very lightweight. Therefore you may want to use it sparingly and carefully on a a production server.

    if you are looking to find who dropped the objects ( i mean DDL) then that information is readily available from default trace which captured in the background unless you have disabled it explicitly.

    you can find information about default trace from the following URL:

    http://www.mssqltips.com/sqlservertip/1739/using-the-default-trace-in-sql-server-2005-and-sql-server-2008/[/url]

    if you willing to spend money you can also use thrid party tools that read logs and provide you with the required information (but maybe not all)

    and you can also us the dbcc loginfo command yourself but the output is cryptic and you will have to spend a lot of time with it and again you may not get everything that you want.

  • July 29, 2012 at 4:47 am

    #1518922

    shiv-356842 (7/26/2012)

    Hi Team,

    Some data deletion was happend in our Environment.

    Now i m planning to implementing AUDIT of the database & Server level any suggestions

    In various level we have to do AUDIT any body please let me know what are various levels..

    How can i start to do the AUDIT can you please help me.

    Thanks

    Only SQL Server Enterprise Edition would support in built AUDIT at a granular level like data deletes.

    If you are not using EE then go for DML triggers.They perform very very slow though in case you are having a high traffic database

    --------------------------------------------------------------------------------------------------
    I am just an another naive wannabe DBA trying to learn SQL Server

  • July 30, 2012 at 7:28 am

    #1519139

    If I'm not mistaken, here are some notes on the built-in auditing capabilities:

    Feature Enterprise Edition Notes

    =============== ================ =====================================

    SQL Server Audit Only Enterprise uses external OS-file.

    Change Tracking no info on user (who made change)

    Change Data Capture Only Enterprise no info on user (who made change)

    If you want to track WHO did it, I would use triggers, even if you have Enterprise Edition. The built-in audit functions do not have that capability.

  • July 30, 2012 at 8:00 am

    #1519153

    a server side DML trace is lightweight amd can provide the whodunnit info, but not the rollback of changes.

    For me, i think having a DML trace that rolls over on itself so you can query recent changes(similar to the Default Trace) is a huge help;

    way too often the issue is not "recover the data", but the need for a finger pointing session instead.

    hate that, but that's the facts of life.

    I've got a procedure marked as a startup procedure which creates that trace on every restart of SQL, so it's in place just like the Default trace.

    it also creates a view so i can select form it whenever necessary.

    see if this script example might help you out:

    sp_AddMyTrace_Latest.txt

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 30, 2012 at 8:09 am

    #1519158

    I like Lowell's and Vikrant's note about traces. The default trace is nice, but you need to comb through it regularly to look for things, or potentially alter it if you have a busy environment to ensure you don't lose data.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply