Voice of the DBA

Category: Blog

Using SQL Compare to Revert a Stored Procedure
Imagine this situation, someone edits a stored procedure on a production server to “fix” something. However, they broke the procedure and you find out the next day. How do you fix this?

We’ll use SQL Compare since most of you don’t have version control (according to surveys), but you do (hopefully ) have backups. Let’s see how SQL Compare can help.

This is part of a series of posts on SQL Compare.

The Scenario

I get a call one morning that we have problems with a report. This report wasn’t producing the expected values. A user said they saw this:

However,the value the day before had been 2301.54. What was wrong?

Checking with SQL Compare

When I hear this, I think immediately two things:
1. the data changed
2. the code changed
Either is possible, but data is most likely. In this case, when I inquired, the user said no new orders were in the system. While I think humans can easily make mistakes when checking data, code is a possibility.

I asked people, but no one responded with a change being made. So let’s check. I know I have backups, so when I run SQL Compare, I change the source to a backup.

Next, I pick the “add backup files”

I choose my file(s) and confirm they are correct. In this case, just one file. I set the target to my database.

I run the comparison, and I see this. There are a few changes, but the one I’m looking for is my procedure. You can see the code is different.

Arguably, the new code is more correct, but if I need to revert, I can click the deploy button at the top. I’ll want to also just select the SalesReport item.

This will produce a script that I can run in SSMS. I can also just let SQL Compare run this. Note the old code is in the script.

Once I run this, the report works, or at least returns the expected results.

Summary

This is a short demo of using a backup as a comparison source against a database to revert code. In this case, I can see the old code and get that back to ensure my database functions as expected..

SQL Compare is an amazing tool that millions of users have enjoyed for 25 years. If you’ve never tried it, give it an eval today and see what you think.
August 13, 2025
Adding a Named Default Constraint to a Table: #SQLNewBlogger
As part of a demo recently I was adding a default value to a new column with a simple DEFAULT and a value. Under the covers this creates a constraint, however, I want to ensure this is named explicitly and not auto generated. This post shows how to do this.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Setup

Let’s create a simple table like this one:
```
CREATE TABLE dbo.OrderHeader (
OrderHeaderID INT NOT NULL CONSTRAINT OrderHeaderPK PRIMARY KEY,
OrderDate DATETIME,
CustomerID INT
)
GO
```
Now I want to add a Created column to the table, with a default value of the current date and time. I decide to do this with an ALTER TABLE statement. In the past, I’ve done this with this code:
```
ALTER TABLE dbo.OrderHeader 
ADD Created DATETIME DEFAULT GETDATE()
```
The problem is this creates a constraint with a system generated name. If I deploy this code to different systems, I get different names. If I need to change the constraint or drop it, I have to query to find the name as it isn’t explicit. You can see this below.

What I’d rather do is have a named constraint that makes sense to me. Let’s drop this column and do a better job. However, I cant’ just drop the column because I need to drop the constraint and that means I need to get the name.

That’s the problem I’m trying to solve. Here is what I need to do.

Now that I’ve dropped this, let’s add it back with an explicit name. This is simple SQL, and easy to add, just like I can do for Primary Keys. We’ll add a CONSTRAINT keyword and name before the default.
```
ALTER TABLE dbo.OrderHeader 
ADD Created DATETIME CONSTRAINT df_OrderHEader_Created_Getdate DEFAULT GETDATE()
GO
```
When I run this, now I see a named constraint.

Note that I’ve named this for the column as if I need similar constraints in this table, they need to be uniquely named. This is in the database, not the table, as all constraints are stored in sys.default_constraints.

Do this and your database deployments go easier, especially across multiple systems.

SQL New Blogger

This is a simple thing, but it’s a good coding practice and better software engineering than allowing the system to name things. I explained how to do this and related this to a real issue in database development: deployments.

This post took me about 10 minutes, and it would likely take you about the same to start showcasing your knowledge. In today’s world, maybe you use AI to help you solve this problem and showcase that skill.
August 11, 2025
Just a few weeks to PASS on Tour and the Redgate Summit

It’s just a few weeks until the PASS Summit on Tour in New York City. This is the first event in the series, and I’m excited to go back to New York City this year. I’ve been there a few times and this time my wife is coming so we can take a little vacation around the event.

You can register here and come see some amazing speakers on Aug 18-19, 2025 for the PASS Summit. Aug 20 is the Redgate Summit, where we’ll be talking about our direction, research, and products. Make it a week and come to both events.

August 6, 2025
Sabbatical 3 – A week of work

I hadn’t made many plans on the sabbatical, and that was fine with me. It’s been a busy year, and I wanted to be unwired and unwind. It turned out this has been a good test of retirement for me and I’ve enjoyed it.

However, we did have a big project on the books and decided to schedule it during my time off. This is my wife’s indoor riding arena.

It’s been on the ranch since 2010, but a very heavy hailstorm damaged it almost two years ago. A number of small holes in the fabric let rain in, which has worsened over time. We worked through an insurance claim to get new fabric and some installation compensation, but decided to do some of the work ourselves since the install cost was high. We did pay for a vendor consultant to help for a week, but my wife, kids, and did all the work.

This was scheduled during my sabbatical and it turned out to be a bigger project than expected. They told us to budget 2-3 days, but it turned out to be 6 for my middle son and I, with lots of help on the other days getting things done.

Here’s the fabric being cut by my son to be pulled off.

It took a day to unwind all the nuts holding things down and then cut down the fabric. A lot of labor, and more than needed, since a custom tool that they send for installs wasn’t available for us. I suspect the original crew didn’t save it for us.

However, the consultant asked if I knew a welder, and we do. The husband of one of my wife’s clients has a shop and has been teaching my daughter to weld. I bought a 7/8″ socket and a piece of pipe and my daughter went over there during the day. She returned with a custom made 20″ socket, which made the install easier later in the week. Here’s my kid using it.

Day 2 was pulling over the first piece of fabric. We used the tractor to do this during a time of low wind. We can pull up to about 10mph, but after that it’s too dangerous.

This was the easy part. We had to then pull the piece sideways to cover the truss. That’s a manual process, working around the edges, slowly pulling things right with 2″ webbing. Once that was done, we then wove in 1″ webbing between the frame and the fabric. We got this done on Day 2, and we were tired.

We worried a bit about getting this done, but on Day 3 we managed to pull both the 2nd and 3rd pieces. We did the middle, and then stretched it. We started weaving in the fabric, but had a moment of low wind, so we quickly pivoted and got the third piece pulled.

We managed to get it partially stretched and tied down before it got too late. Our consultant left and we worked to get the 2nd piece finished.

Day 4 was us working along. Once we got the third piece stretched, my wife and son were tightening wire that spends the edges of each piece of fabric. A few remote Facetime calls with our consultant had them working through it as I wove in the last piece.

A good view from above. My wife and daughter were a little scared about the height, so my son, a friend, and I did all the weaving above 10 feet.

A shot of my son and I working.

We rented a second lift late on Day 2 and I wish we’d done it earlier. Things went much faster with two lifts.

Day 5 was the final weaving and tightening things down. We closed the day with most everything done.

Day 6 was me cleaning things up, picking up trash, patching a couple holes in the ends while we had the lift and my son tightening the nuts (shown above).

We finished just in time as Day 7 was my wife and I heading to Cancun for a well deserved break.

August 4, 2025