5 Tips for Making Changes on Live SQL Server Databases

One of the scariest things you will do as an Access developer is make direct data changes on a production database.  (If you don't think it's scary, you probably haven't been doing this job very long.)

One of the strengths of a database is that it is very easy to make changes to a lot of data with a single simple command.  One of the most dangerous things about databases is that it is very easy to make changes to a lot of data with a single simple command.

With great power comes great responsibility.

Mitigating the Risk

There are several strategies you can use to lower the risk of making changes to a production database.  This article's examples are specific to SQL Server, but most of these concepts apply to databases of any kind.

1. Test the changes in a different environment
2. Script the changes
3. Use excessive WHERE clauses
4. ROLLBACK first, then COMMIT
5. Wear a big blue foam cowboy hat

Test the Changes in a Different Environment

Hopefully, you have another environment where you can test database changes before you make them on the production system, such as a test or development database.

Keep a few things in mind, though:

• The test/dev database structure may differ from production (this is one reason why you should script database changes, so as to minimize such differences)
• The test/dev database likely has different data than production (this can especially impact performance, as oftentimes a test database has several orders of magnitude less data than production)
• The test/dev database has fewer users (in many cases, it's just you, while the production database may have many concurrent users)

Depending on the nature of your changes, some or all of the above caveats may come into play.  If it's practical, you may consider making a current backup of the production database and restoring it in a dev/test environment so as to minimize the impact of the above considerations.  

For step-by-step instructions, refer to my article, Cloning a SQL Server Database.

Script the Changes

With few exceptions, I write a SQL script for every direct change I make to production data.

This provides several advantages:

• It's more reliably repeatable, especially important if you are testing the script in a dev/test environment first
• It's easy to save in a bug database for later debugging purposes
• It forces you to think about exactly what it is that you are trying to accomplish

Even if you use the query by example window in Access or the equivalent in SQL Server, I find it valuable to review and save a copy of the actual SQL that you will be executing against the database.

Use Excessive WHERE Clauses

If you only intend to change a single record, find that record's primary key and include it in the WHERE clause.  Don't rely on a date-vendor combination to update a single invoice record only to find out too late that that vendor had several invoices on that particular date.  Instead of changing one record, you accidentally changed a dozen!

For example, instead of this...

UPDATE Invoice
SET PmtAmt = 42
WHERE VendorID = 10
  AND PurchaseDate = '2022-1-12'

...use this...

UPDATE Invoice
SET PmtAmt = 42
WHERE InvoiceID = 1783
  AND VendorID = 10
  AND PurchaseDate = '2022-1-12'
  AND PmtAmt = 52
  AND DeliveredOn Is Null
  AND ProductID = 683

The chances of accidentally updating the wrong record in the second version are close to zero.  

What else do you notice about the second version?  It provides very clear documentation about several other values in the existing record.  This is handy if you have to go back and look at this in a bug database.  Just by looking at the WHERE clause, I know the original payment amount, which product was purchased, the fact that it never got delivered, etc.

This approach is somewhat similar to the DocTest concept in that it is a form of verifiable documentation.  

It's one thing to write in your bug database notes that the product was never delivered, but what if it was and you didn't realize it?  If you include it as part of the WHERE clause and it actually had been delivered, then the UPDATE clause would not have updated any records.

Think of it as a Debug.Assert for your SQL queries.

ROLLBACK First...Then COMMIT

When I want to confirm that a change will work the way I expect, one common technique I use is to run the whole thing inside of a transaction and roll it back rather than committing it.

Using the earlier example, I might do something like this:

BEGIN TRAN

-- Show the BEFORE results
SELECT * 
FROM Invoice
WHERE VendorID = 10
  AND PurchaseDate = '2022-1-12'

-- Make the changes
UPDATE Invoice
SET PmtAmt = 42
WHERE InvoiceID = 1783
  AND VendorID = 10
  AND PurchaseDate = '2022-1-12'
  AND PmtAmt = 52
  AND DeliveredOn Is Null
  AND ProductID = 683
  
-- Show the AFTER results
SELECT * 
FROM Invoice
WHERE VendorID = 10
  AND PurchaseDate = '2022-1-12'

ROLLBACK TRAN

If you execute the above SQL statement in SQL Server Management Studio (SSMS), you will see two different result sets in the Results tab.  You will also see the number of rows affected in the Messages tab.  It will look something like this:

(5 rows affected)

(1 row affected)

(5 rows affected)

Completion time: 2023-01-12T09:37:05.1054179-05:00

The second result set should be what I'm looking for.  If it's not, no harm no foul.  I just edit the UPDATE/INSERT INTO/DELETE/etc. statement and tweak it until I get the result I need.

When everything looks good, I just have to change the last line from...

ROLLBACK TRAN

...to...

COMMIT TRAN

...and execute the query to make the changes permanent.

Wear a Big Blue Foam Cowboy Hat

I thought this was a genius idea:

Cover image created with Microsoft Designer