SQL Server UNDO

Question

I am a part time developer (full time student) and the company I am working for uses SQL Server 2005. The thing I find strange about SQL Server that if you do a script that involves inserting, updating etc there isn't any real way to undo it except for a rollback or using transactions.

You might say what's wrong with those 2 options? Well if for example someone does an update statement and forgets to put in a WHERE clause, you suddenly find yourself with 13k rows updated and suddenly all the clients in that table are named 'bob'. Now you have the wrath of 13k bobs to face since that "someone" forgot to use a transaction and if you do a rollback you are going to undo critical changes that were needed in other fields.

In my studies I have Oracle. In Oracle you can first run the script then commit it if you find that there isn't any mistakes. I was wondering if there was something that I missed in SQL Server since I am still relatively new in working developer world.

Answer 1

I'm no database/sql server expert and I'm not sure if this is what you're looking for, but there is the possibility to create snapshots of a database. A snapshot allows you to revert the database to that state at any time.

Check this link for more information: http://msdn.microsoft.com/en-us/library/ms175158.aspx

Answer 2

I don't believe you missed anything. Using transactions to prevent against these kind of errors is the best mechanism and it is the same mechanism Oracle uses to protected the end user. The difference is that Oracle implicitly begins a transaction for you whereas in SQL Server you must do it explicitly.

Answer 3

I think transactions work well. You could rollback the DB (to a previous backup or point in the log), but I think transactions are much simpler.

Answer 4

From what I understand, SQL Server 2008 added an Auditing feature that logs all changes made by users to the various databases and also has the option to roll them back after the fact.

Again, this is from what I've read or overheard from our DBA, but might be worth looking into.

EDIT: After looking into it, it appears to only give the ability to rollback on schema changes, not data modifications (DDL triggers).

Answer 5

How about this: never make changes to a production database that have not 1st been tested on your development server, and always make a backup before trying anything that is un-proven.

Answer 6

SET IMPLICIT_TRANSACTIONS is what you are probably looking for.

Answer 7

If I am doing something with any risk in SQL Server, I write the script like this:

BEGIN TRAN

Insert .... whatever

Update .... whatever

-- COMMIT

The last line is a comment on purpose: I first run the lines before, then make sure there's no error, and then highlight just the word Commit and execute that. This works because in Management Studio you can select a part of the T-SQL and just execute the selected portion.

There are a couple of advantages: Implicit Transactions works too, but it's not the default for SQL Server so you have to remember to turn it on or set options to do that. Also, if it's on all the time, I find it's easy for people to "forget" and leave uncommitted transactions open, which can block others. That's mainly because it's not the default behavior and SQL Server folks aren't used to it.