Data Deletion in DB

Question

Data Deletion in DB

shiv-356842

SSCarpal Tunnel

Points: 4312
More actions
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

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

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

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

Vikrant S Patil SSCrazy Points: 2294 More actions · Answer 2

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.

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 3

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

vikingDBA SSCertifiable Points: 5356 More actions · Answer 4

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

FeatureEnterprise Edition Notes

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

SQL Server Audit Only Enterpriseuses external OS-file.

Change Tracking no info on user (who made change)

Change Data CaptureOnly Enterpriseno 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.

Lowell SSC Guru Points: 323518 More actions · Answer 5

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!

Steve Jones - SSC Editor SSC Guru Points: 736445 More actions · Answer 6

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.