0

I know this question was asked many times for previous versions of SQL Server but I’m wondering if this was made easier in SQL Server 2014.

I’m familiar with the techniques for other versions that are shown here

How can I rollback an UPDATE query in SQL server 2005?

Undo table updates in SQL Server 2008

So question is: Is there anything that will allow to rollback and UPDATE query in SQL Server 2014 that was not available in previous versions?

I’m talking committed command – I know you can rollback if query is inside transaction.

We have full, differential and transaction log backups in place but we’d really like to avoid recovery scenarios with restoring backups into secondary database and such…

Community
  • 1
  • 1
Stanley Norman
  • 133
  • 1
  • 1
  • 5
  • You could look at using an async trigger to write a journal of the updates. It's nothing new in 2014, but would likely do the trick. – Kevin Dahl Jul 24 '13 at 13:54

2 Answers2

5

Nothing has changed with the newer versions of SQL Server: a committed transaction is still considered permanent, so the only method of rollback is to revert to a previous backup and apply transaction logs up to that point.

Edited to add...
There is one other tool that has existed for a few versions of SQL Server, and that's Snapshots. It is possible to revert a database to a previous snapshot, so for example the instructions for SQL 2008 at http://msdn.microsoft.com/en-us/library/ms189281(v=sql.105).aspx. However this is not a replacement for backups and transaction logs which you will still need to keep available. You will also need to research what impact the snapshot has on performance (if any) - I can't vouch for their usability: you might find that they're ideal in a dev or test environment, but not so for production.

Chris J
  • 30,688
  • 6
  • 69
  • 111
1

From what I know there is nothing new here. MS is focusing on enterprise level functionality in SQL Server (in memory queries, more high availability and such).

As far as I know your options to undo accidental update are:

  • Third party log readers that do cost some $ (I know about Apex Log and Quest Toad but there may be others too)
  • Restore latest backup in another database and check the difference between databases
  • Spend a ton of time trying to read transaction log using fn_dblog function like shown here
Nathan Dunleavy
  • 71
  • 4