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:
- the data changed
- 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.