Damn rollback scripts are a pain

Published On: 2009-08-26By:

Writing release scripts for a database is easy.  What ever new objects you want you create you create, and what ever objects you need to change you change.  Piece of cake.  Rollback scripts however are a bit trickier since you need to know what everything should look like after the script is done.

Now if you need to clean out everything from the database, it’s not all that hard to clean out the database.

First drop all your procedures.  You can get a list of the procedures from the sys.procedures catalot view.  A simple cursor can handle this.

DECLARE @object sysname, @schema sysname, @script NVARCHAR(4000)
DECLARE obj CURSOR AS SELECT schema_name(schema_id), name FROM sys.procedures
OPEN obj
FETCH NEXT FROM obj TO @object, @schema
SET @script = 'DROP PROCEDURE [' + @schema + '].[' + @object + ']'
EXEC (@script)
FETCH NEXT FRO obj TO @object, @schema

You can then use similar scripts for sys.views.  Before you start dropping the tables you’ll need to remove all the foreign key constraints which you can find in the sys.foreign_keys.  After you remove the foreign key constraints you should be able to remove the tables without issue.

Now dropping everything int he database isn’t the norm, unless you are creating a script to roll back the first release of a database.  When doing a more normal rollback script you’ll want to be careful to remove your objects which are being created in the matching release script.

You can test to see that each object exists by querying the system catalog views.  Then drop the various objects, including columns, tables, procedures, etc.  You’ll also need to include the prior version of the procedures in the script, assuming that they aren’t new procedures.

Rolling back data changes can be a little bit trickier.  If you are changing meta data you probably know what the origional version was so you can hard code it.  However when you are changing data that could be anything a little more care will be needed.  A technique which I’ve found works well for me is to create a ROLLBACK schema in the database.  Then before changing the data in the release script I create a table in the ROLLBACK schema using the same table name, with the release script number after the table name.  Then I backup the data into that table, then make the change.  I ensure that only the time the data is moved into the ROLLBACK table is the first time the script is run.  That way the script can be run more than once and my rollback data always remains valid.

Then if I need to rollback the data change, I’ve got all the old values sitting in the ROLLBACK table waiting for me.


Contact the Author | Contact DCAC


Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award    FT Americas’ Fastest Growing Companies 2020   
Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link