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.
[sql]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
WHILE @@FETCH_STATUS = 0
BEGIN
SET @script = ‘DROP PROCEDURE [‘ + @schema + ‘].[‘ + @object + ‘]’
EXEC (@script)
FETCH NEXT FRO obj TO @object, @schema
END
CLOSE obj
DEALLOCATE obj[/sql]
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.
Denny
