Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In mssql, is it possible to rollback changes made by a specific user to the database? For example if that user ran an update query and did not write begin transaction before, is it possible to revert changes made by his query WITHOUT restoring the entire Database (I want to preserve changes made by other users)?

share|improve this question

4 Answers 4

up vote 3 down vote accepted

It would be difficult to do that using Native tools or you will have to look for third party tools.

Natively:

  • Default trace

Can be used to track Object Altered, Object Created and Object Deleted along with other stuff. Refer to the link below.

Refer to : The default trace in SQL Server - the power of performance and security auditing

Note this is very limited as it will just give you info related to who did what e.g. Who dropped the table, etc.

  • fn_dblog function ==> [undocumented]

Note: You need to transaction log backups available or the transaction should not have been cleared from the active portion of the log

Excellent reading at : Using fn_dblog, fn_dump_dblog, and restoring with STOPBEFOREMARK to an LSN and Joining sys.dm_tran_database_transactions to fn_dblog Results

As a side note:

I want to preserve changes made by other users

Depending on your version of sql server and your business requirements, you can implement

share|improve this answer

In addition to the answers provided by @Kin you also can restore the database under a different name or onto a different server. This could be a quick and dirty solution if you are looking to do just a one time data retrieval.

You could even look at a product like Idera Virtual Database (http://www.idera.com/productssolutions/sqlserver/sqlvirtualdatabase) - you can download the trial to perform a one off virtual restore, or purchase this if you are going to need to perform data retrieval some what frequently.

Once you can capture the original values you can then perform an update to revert the users changes effectively rolling back his change.

share|improve this answer

If you're not using a third party tool, I recommend the fn_dblog function. As it's undocumented, it's not easy to use and the results it returns are not easy to read. Try with the scripts someone has already tested:

SQL Server – How to find Who Deleted What records at What Time

How to recover deleted data from SQL Server

Keep in mind that fn_dblog can read only the online transaction log For transaction log backups, use fn_dump_dblog

share|improve this answer

No. You dont even know which changes were his unless you recorded it wih triggers etc. - for sql that would be useless information, outside what the logging is supposed to handle.

share|improve this answer
    
The log file does actually record the sid. Not sure if any third party tools make use of it to allow selective rollback. –  Martin Smith Nov 18 '13 at 17:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.