How to rollback changes made to the database by a specific user?

Question

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)?

Answer 1

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

• Third Party tools - the best one out is ApexSQL Log

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

• Change Data Capture
• Change Tracking
• Run SQL Server Audit
• Server-Side Tracing and Collection - with appropriate events selected.

Answer 2

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.

Answer 3

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

Answer 4

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.