Who is right? - SQL Rollback Question

Question

Hi together,

a few minutes ago I had a discussion with my boss and want now more meanings, if I am wrong or he is.

Following problem is given: After a fast change from a colleague a stored procedure went wrong. (I think for the theory here is no more information requiered)

We decide to provide the correct version in an Update Script:

IF EXIST PROCEDURE X .... DROP PROCEDURE X .... CREATE PROCEDURE X ....

For the DBO that the correcture will be execute, we must (should) prepare an rollback mechanism. My Boss comes to me and ask me for preparing an rollback script. I tell him, that we don't have one: Because when the main script failed, there is no option. It went now completly wrong, we can't rollback to the wrong one. It's senseless.

He told me, that we need it and it is not senseles..

When I asked for a reason, he goes away.

I am wrong?

Answer 1

There is some logic to what you're saying, but I could agree with your boss if the argument is tantamount to saying "we don't need to back up code that isn't working"

It's still somebody's expensive hours put into that, and the flaw may be a minor one. If it is conceivable that further updates could somehow make things even more wrong, then it is reasonable to request the ability to roll back to the less broken state.

Answer 2

Two wrongs don't make a right.

If the previous change to the SP had a rollback option then you wouldn't be in this situation. So even though you know that any rollback will go to a "bad" version. It should still be there.

The update you do here could break things a lot more than they currently are.

Answer 3

After a fast change from a colleague a stored procedure went wrong. (I think for the theory here is no more information requiered)

More information is required.

When changing a script, your colleague did two things:

1. He did (or did not) improve a stored procedure he fixed
2. He introduced a bug in this stored procedure

Whether you should or should not write this script, depends on these factors:

1. How significant the improvement was (if any)
2. How severe is the bug, and
3. How hard is to write this script.

Answer 4

If this is a production server, where you wouldn't want downtime, you should have a rollback procedure. Even if that procedure is to just back the database up before the upgrade and restore from backup on failure.

You also imply that 'create procedure' will either work or not. This is true, but it doesn't mean the procedure will actually work. You can create a stored procedure (at least on SQL Server) that references a table that doesn't exist, and it will compile just fine.