NOTE: This works whenever your script does not contain GO. If it does, a solution is to also turn on SQLCMD mode on and preface your script with a line «:ON ERROR EXIT» so that subsequent statements aren’t executed outside the transaction’s scope.

Authored by Ben Clothier

For those who use the winning combination of Access as the front-end client to SQL Server, chances are that you’ve had to use SQL Server Management Studio (SSMS) for designing & manipulating SQL Server objects. For small firms with no IT departments or no DBA, you may also be responsible for maintaining all aspects of SQL Server; from maintaining the data and creating stored procedures to backups. Now before you think I’m going to talk about backing up, that’s not what this is about – it’s already assumed you’re backing data up. To learn more about backups see this post by Juan.

SSMS is a real joy, it’s powerful and easy to work with, but working with production data has it’s hazards, what if an errant query wreaks havoc on your data?

When disastrous queries strike…

The client asks you to add a new row to a lookup table and also modify a single record in the main table to use the new key from the lookup table. Simple enough!

DECLARE @lk INT;

INSERT INTO tblCompanyTypes (CompanyType)
VALUES ('Some New Company Type');
SET @lk = SCOPE_IDENTITY();

UPDATE tblCompanies
SET CompanyTypeID = @lk
WHERE CompanyTypeID = 1;

The message returned is «(12371 rows affected)». Ah, perf…. wait!! 12371!!! There’s supposed to be only one! Geez, I used the wrong column in the WHERE. We now have to undo the accident.

Time to restore that backup?

We have a full backup before we ran the script so all should be good, right? Well, maybe the system is still in use so you have to ask the users to log off so you can restore the backup. That should never happen. We’re also looking at lost time & money, which is just bad all around. This can be prevented with a simple strategy:

Rollback your data modifications

You may know that SQL Server is fully transactional which means we can ask SQL Server to hold back from actually writing data until all operations have succeeded and if one operation fails, everything else must fail also. We make this request by using keywords; BEGIN TRANSACTION, COMMIT, ROLLBACK. When a script issues a command to begin a transaction, all data modifications are processed but not actually saved until we give the command to commit. If we don’t, or explicitly issue a rollback command, all modifications are then discarded and the database remains unchanged. We want to make most use of that feature whenever we need to modify data. Returning to the scenario, this is how we should write our script:

BEGIN TRANSACTION;
--COMMIT;
--ROLLBACK;

DECLARE @lk INT;

INSERT INTO tblCompanyTypes (CompanyType)
VALUES ('Some New Company Type');
SET @lk = SCOPE_IDENTITY();


UPDATE tblCompanies
SET CompanyTypeID = @lk
WHERE CompanyTypeID = 1;

Notice how the COMMIT and ROLLBACK commands are commented out with double dash. When I execute the commands only BEGIN TRANSACTION is used, allowing me to evaluate the outcome of my query and decide later if I want to commit the results or roll them back. This allows me the time to make sure all is well before making a final commit of the data.

The above takes advantage of two wonderful features of SSMS:

1) Each query window in SSMS holds onto the same connection

Even though you may have executed a SQL statement then edited it and executed it again, it’s considered to be still in the same connection and therefore in same scope, allowing you to roll back the data at a later time.

2) You can execute parts of SQL commands in the query window by highlighting.

If you highlight any part of query window, only SQL within that selection will be processed. It allows you to execute a single statement instead of all the statements in the query window. This even works when you highlight part of a commented-out line like the COMMIT in the line

--COMMIT;

Together, what it means is that by prefacing any SQL script with a BEGIN TRANSACTION, you can ensure that nothing is actually written until you’ve had a chance to review the effects the modification had. Going back to the scenario above, when we see that 12137 rows instead of 1 row was updated, all we need to do is then highlight ROLLBACK and execute it, then the mistake is undone. So instead of sinking at least an hour of restoring backup and getting everything back in their place, it’s just a highlight & click worth of fixing.

Remember the scope

I alluded earlier that each query window (e.g. each individual tab in the SSMS) has their own connection. This implies that you shouldn’t try to execute queries on the same objects between two query windows or you may get unexpected results. For example, don’t modify data in tblCustomers in window 1 and window 2 and expect it to rollback for both. Better to limit your objects to just one window. This is also true when you wish to verify the results, (perhaps doing a SELECT after an UPDATE for instance), you have to place the SELECT inside the same query window where you do the UPDATE so it remains in the same transaction scope and therefore can see the changes the UPDATE made without having to actually commit the changes.

It’s great for making design changes to objects, too!

SQL Server allows transactions even for any design changes we may make to any objects such as tables, views or stored procedures. To guarantee a smooth upgrade, this is what I typically do when I’m working in the beta database: Whenever I change a SQL Server object, I right-click the object in the object explorer and use «Script <object> As…» to either generate CREATE (if it’s a new object) or ALTER (if it’s an existing object) and then place them in a single script with the same preface. I also remove the USE statement from the generated script to prevent the script changing databases when running. When the beta database has been tested and the changes are ready to be rolled out into production, I run the script against the production database and when it has succeeded in entirety, highlight & execute the COMMIT statement so I know that all design changes will be applied at once.

Generally speaking, the set of SQL statements that cannot be wrapped in a transaction tend to be those that operates at database or full-text index level. For precise listing of statement that cannot be wrapped in a transaction, consult this page.

Just one more failsafe mechanism to go with a robust software lifecycle strategy

As alluded to in the start of the post, it’s just one more failsafe mechanism in addition to keeping regular backups and maintaining a separate copy for development & testing and a solid software lifecycle strategy. It can’t possibly be a be-all, end-all solution, but it certainly can make a difference between a highlight & click and a wasted hour of restoring backup.

To see another strategy in safe guarding your data, take a look at the audit table technique’s described in Juan’s post here and here.