Fast Project Rollbacks

The projects that I work on require both deployment and rollback scripts. Using a database backup as a rollback strategy is not acceptable for a number of reasons.

Downtime needs to be measured in minutes and the restore would take hours.
The project normally affects a portion of the databases so rollbacks are intended to affect the objects and data that have been deployed and not any other objects or data.
Visibility of the rollback steps is needed as an audit log.

I have a particular approach to scripting up the rollback of database projects that I should like to share with you.

Goals for creating rollback scripts

Code readability

All code has to be peer-reviewed and the effort involved in a thorough peer review is not always appreciated. Indeed Cisco used to publish recommendations for peer reviews that include recommendations on the following

The amount of code that could be expected to be peer reviewed per session.
The amount of time in minutes allowed in any one peer review session before a break was needed.

The figures for both the volume of code and the time spent are likely to be far lower than you would expect therefore anything you can do to make the code easier to read is going to aid the peer review process.

Remember a peer review should not be regarded as an obstacle to overcome but as an insurance policy to ensure that it won't be your code that is to blame for a production incident.

If your code is readable then the peer review can be achieved quicker and any bugs should be easier to spot.

Code readability leads to my second goal

Code Maintainability

I don't code on the fly. I put quite a bit of time and effort into my design but if I can use the analogy of the battle plan then no battle plan survives engagement with the enemy.

Things that made perfect sense in the design stage might not be practical in the real world. My code is probably going to change so it must be easy to change.

Code Durability

If a rollback script runs more than once then it should do no harm and produce no error messages. I am only concerned with rollback scripts here but the same comment also applies to deployment scripts. Ideally the both deployment and rollback scripts should produce status messages to act as a log of what has taken place.

Most of my deployments start in the very early hours of the morning when I'm fully decaffeinated so any unexpected error messages are unwelcome.

Speed of execution

Time is money. If your database is unavailable then it is costing you money. In some cases using a backup as a rollback strategy simply isn't financially feasible except as a method of last resort.

Having a script that can execute in seconds, or at the worst minutes is far better than a restore of a terabyte database that may take hours.

Script headers

I use script headers for three purposes.

To provider brief notes on what the script is supposed to do, prerequisites, special actions
To generate dynamic status messages showing the server, time of deployment, the account running the script.
To set session settings such as XACT_ABORT and NOCOUNT ON.

Consider the code below.

SET XACT_ABORT,NOCOUNT ON
---------------------------------------------------
USE AW_TARGET
GO
---------------------------------------------------
PRINT '---------------------------------------------------'
DECLARE 
	@CRLF CHAR(2) ,-- Carriage return and line feed
	@Deployer SYSNAME, -- The account running the script
	@DeploymentDate CHAR(20), -- The date and time of execution
	@DBName SYSNAME -- The database name to which the action applies.
SET @CRLF=CHAR(13)+CHAR(10)
SET @Deployer = SUSER_SNAME()
SET @DeploymentDate = convert(CHAR(20),getdate(),120)
SET @DBName = DB_NAME()
RAISERROR('DEPLOYMENT SERVER: %s%sDEPLOYER: %s%sDeployment Date: %s%sDatabase: %s',10,1,@@SERVERNAME,@CRLF,@Deployer,@CRLF, @DeploymentDate,@CRLF, @DBName)
PRINT '---------------------------------------------------'

The first line simply switches the following two session options on.

Session option	Action when ON
XACT_ABORT	Any action resulting in an error will rollback the transaction in which the error occurred.
NOCOUNT	Suppresses the (n row(s) affected) message and makes the audit messages easier to read.

In this case the AW_TARGET database is simply a copy of AdventureWorks

The remaining line cause the following message to be displayed

---------------------------------------------------
DEPLOYMENT SERVER: DBDevelopment01
DEPLOYER: Development01\David
Deployment Date: 2008-09-07 15:54:33 
Database: AW_Target
---------------------------------------------------

Removing views

The following code may look familiar

IF  EXISTS (INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA='Production' AND TABLE_NAME='vProductAndDescription')
	DROP VIEW Production.vProductAndDescription
GO

Mechanically there is nothing wrong with the above but for removing multiple objects the repetitive nature of the code makes it easy to miss errors.

The following code shows an alternative method of removing a set list of views


DECLARE @SQL VARCHAR(MAX) , @CRLF CHAR(2)
SET @CRLF = CHAR(13)+CHAR(10)
SELECT @SQL=COALESCE(@SQL+';'+@CRLF,'') 
+ 'DROP VIEW '
+ QUOTENAME(TABLE_SCHEMA)
+ '.'
+ QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.[VIEWS]
WHERE 
	TABLE_SCHEMA='Production'
AND TABLE_NAME IN (
	'vProductAndDescription',
	'vProductModelCatalogDescription',
	'vProductModelInstructions'
)
PRINT @SQL
--EXEC (@SQL)
GO

Although it may seem like overkill for three simple views the process comes into its own when the view list gets longer. The view list acts as a manifest that can be checked against the deployment script or release notes.

To prevent accidents while experimenting I have commented out the EXEC statement but when run the script will produce the following.

---------------------------------------------------
DEPLOYMENT SERVER: D_POOLE
DEPLOYER: D_POOLE\David
Deployment Date: 2008-09-07 16:05:05 
Database: AW_Target
---------------------------------------------------
DROP VIEW [Production].[vProductAndDescription];
DROP VIEW [Production].[vProductModelCatalogDescription];
DROP VIEW [Production].[vProductModelInstructions]

Removing Stored Procedures

The method for removing stored procedures is almost identical differing only in the INFORMATION_SCHEMA view and associated field names.


DECLARE @SQL VARCHAR(MAX) , @CRLF CHAR(2)
SET @CRLF = CHAR(13)+CHAR(10)
SELECT @SQL=COALESCE(@SQL+';'+@CRLF,'') 
+	'DROP '
+	ROUTINE_TYPE
+	QUOTENAME(ROUTINE_SCHEMA)
+	'.'
+	QUOTENAME(ROUTINE_NAME)
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME IN(
	'ListSales',
	'SetSale',
	'AddSale',
	'RejectSale'
)
PRINT @SQL
--EXEC(@SQL) -- commented out to prevent accidents
GO

Removing tables

Removing tables is very similar but in this case we have to consider the dependencies between the tables.

If you try and delete a table where referenced by a foreign key constraint then the delete will fail. This means that we must first remove any foreign key constraints referencing the tables that we want to remove.

The first stage is to assemble a table containing the list of database tables we want to drop.

DECLARE @TableList TABLE (ID INT NOT NULL, TableName sysname NOT NULL)
INSERT INTO @TableList (ID,TableName)  
SELECT OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME),TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME IN (
	'BillOfMaterials',
	'Culture',
	'Document',
	'Illustration',
	'Location'
)

The next stage is to use this table to establish a list of foreign key constraints and generate the SQL to remove them.

DECLARE @SQL VARCHAR(MAX) , @CRLF CHAR(2)
SET @CRLF = CHAR(13)+CHAR(10)
-----------------------------------------------------------------------------
-- Generate SQL to remove FKs
-----------------------------------------------------------------------------
SELECT @SQL=COALESCE(@SQL+';'+@CRLF,'') 
+	'ALTER TABLE '
+	QUOTENAME(OBJECT_NAME(fkeyid))
+	' DROP CONSTRAINT '
+	QUOTENAME(OBJECT_NAME(constid))
FROM sysreferences
WHERE rkeyid IN (SELECT id FROM @TableList)

Finally we must append the SQL to remove the tables themselves.

SELECT @SQL=COALESCE(@SQL+';'+@CRLF,'') 
+	'DROP TABLE '
+	QUOTENAME(TABLE_SCHEMA)
+	'.'
+	QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME IN (SELECT TableName FROM @TableList)
PRINT @SQL
--EXEC(@SQL) -- Commented out to prevent accidents
GO

Our code above will produce the following output

ALTER TABLE [ProductDocument] DROP CONSTRAINT [FK_ProductDocument_Document_DocumentID];
ALTER TABLE [ProductInventory] DROP CONSTRAINT [FK_ProductInventory_Location_LocationID];
ALTER TABLE [ProductModelIllustration] DROP CONSTRAINT [FK_ProductModelIllustration_Illustration_IllustrationID];
ALTER TABLE [ProductModelProductDescriptionCulture] DROP CONSTRAINT [FK_ProductModelProductDescriptionCulture_Culture_CultureID];
ALTER TABLE [WorkOrderRouting] DROP CONSTRAINT [FK_WorkOrderRouting_Location_LocationID];
DROP TABLE [Production].[BillOfMaterials];
DROP TABLE [Production].[Culture];
DROP TABLE [Production].[Document];
DROP TABLE [Production].[Illustration];
DROP TABLE [Production].[Location]

The advantage to this method is that you don't have to worry about the order in which tables are deleted or the names of the foreign key constraints.

Removing Replication

If our tables are replicated then any attempt to delete them will still fail in which case we need to consider the code below.

DECLARE @TABLELIST TABLE(TableName sysname not null)
INSERT INTO @TABLELIST
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME IN (
	'BillOfMaterials',
	'Culture',
	'Document',
	'Illustration',
	'Location'
)

Once again we build up a table containing a list of the database tables for which we wish to drop replication.

I use the presence of the sysarticles table to detect whether or not the database is replicated but I could just as well use IF DATABASEPROPERTYEX('AdventureWorks','IsPublished')=1

IF EXISTS(SELECT 1 FROM sysobjects WHERE [name]='sysarticles')
	BEGIN
		DECLARE @SQL VARCHAR(MAX) , @CRLF CHAR(2)
		SET @CRLF = CHAR(13)+CHAR(10)
SELECT @SQL=COALESCE(@SQL+';'+@CRLF,'') 
		+	'exec sp_dropsubscription @publication = N'''
		+	P.[name]
		+	''',@article = N'''
		+ A.[name]
		+ ''', @subscriber = N''all'', @destination_db = N''all'''
		+ @CRLF
		+	'exec sp_droparticle @publication = N'''
		+ p.[name]
		+	''',@article = N'''
		+ A.[name]
		+    ''', @force_invalidate_snapshot = 1'
		FROM sysarticles AS A
			INNER JOIN syspublications AS P
			ON A.pubid = P.pubid
		AND A.name IN (SELECT TableName FROM @TableList
		)
		PRINT @SQL
		--EXEC (@SQL) -- Commented out to prevent accidents
	END

The output from this code is as follows

exec sp_dropsubscription @publication = N'AdventureWorks_Reporting',@article = N'BillOfMaterials', @subscriber = N'all', @destination_db = N'all'
exec sp_droparticle @publication = N'AdventureWorks_Reporting',@article = N'BillOfMaterials', @force_invalidate_snapshot = 1;
exec sp_dropsubscription @publication = N'AdventureWorks_Reporting',@article = N'Culture', @subscriber = N'all', @destination_db = N'all'
exec sp_droparticle @publication = N'AdventureWorks_Reporting',@article = N'Culture', @force_invalidate_snapshot = 1;
exec sp_dropsubscription @publication = N'AdventureWorks_Reporting',@article = N'Document', @subscriber = N'all', @destination_db = N'all'
exec sp_droparticle @publication = N'AdventureWorks_Reporting',@article = N'Document', @force_invalidate_snapshot = 1;
exec sp_dropsubscription @publication = N'AdventureWorks_Reporting',@article = N'Illustration', @subscriber = N'all', @destination_db = N'all'
exec sp_droparticle @publication = N'AdventureWorks_Reporting',@article = N'Illustration', @force_invalidate_snapshot = 1;
exec sp_dropsubscription @publication = N'AdventureWorks_Reporting',@article = N'Location', @subscriber = N'all', @destination_db = N'all'
exec sp_droparticle @publication = N'AdventureWorks_Reporting',@article = N'Location', @force_invalidate_snapshot = 1

Removing Columns

Just as we cannot remove tables if they are referenced by foreign key constraints we are also prevented from removing columns if they are referenced by check or default constraints.

The following code removes any column constraints on ModifiedDate in the HumanResource.EmployeeAddress table.


DECLARE @SQL VARCHAR(MAX) , @CRLF CHAR(2)
SET @CRLF = CHAR(13)+CHAR(10)
SELECT @SQL=COALESCE(@SQL+';'+@CRLF,'') 
+	'ALTER TABLE '
+	OBJECT_SCHEMA_NAME(COL.id)
+	'.'
+	OBJECT_NAME(COL.id)
+	' DROP CONSTRAINT '
+	OBJECT_NAME(C.ConstID)
FROM sysconstraints AS C
	INNER JOIN syscolumns AS COL
	ON C.Id = COL.Id
	AND C.ColID = COL.ColID
WHERE COL.id IN(
	OBJECT_ID('HumanResources.EmployeeAddress')
)
AND COL.Name = 'ModifiedDate'

Once the constraints have been removed we can remove the column itself.

SELECT @SQL=COALESCE(@SQL+';'+@CRLF,'') 
+	'ALTER TABLE '
+	OBJECT_SCHEMA_NAME(COL.id)
+	'.'
+	OBJECT_NAME(COL.id)
+	' DROP COLUMN '
+	COL.Name
FROM syscolumns AS COL
WHERE COL.id IN(
	OBJECT_ID('HumanResources.EmployeeAddress')
)
AND COL.Name IN( 
	'ModifiedDate' -- Add the list of fields that you want to remove.
)
PRINT @SQL
--EXEC(@SQL)    -- Commented out to prevent accidents

SQL2000 considerations

In SQL2005 and SQL2008 we have the VARCHAR(MAX) data type.

In SQL2000 and earlier we have an 8000 character limit to a VARCHAR. If your rollback has a large number of objects of a specific type then you have to consider whether your dynamic rollback commands will exceed the 8000 character limit.

Obviously you could repeat the scripts for a different block of objects but there is an alternative approach.

The example below uses our "Removing Columns" example

-- If the TEMPDB table already exists then get rid of it.
IF OBJECT_ID('TempDB..#RollbackCommands') IS NOT NULL
	DROP TABLE #RollbackCommands
GO
-- Create the table to hold the rollback commands
-- The IDENTITY column is to ensure that commands run in the correct order.
CREATE TABLE #RollbackCommands (
	CommandID INT NOT NULL IDENTITY(1,1) 
		CONSTRAINT PK_RollbackCommands PRIMARY KEY CLUSTERED,
	 CommandText varchar(1024) 
)
-- Place our commands in the temp table
INSERT INTO #RollbackCommands(CommandText)
SELECT 
	'ALTER TABLE '
+	OBJECT_SCHEMA_NAME(COL.id)
+	'.'
+	OBJECT_NAME(COL.id)
+	' DROP CONSTRAINT '
+	OBJECT_NAME(C.ConstID)
FROM sysconstraints AS C
	INNER JOIN syscolumns AS COL
	ON C.Id = COL.Id
	AND C.ColID = COL.ColID
WHERE COL.id IN(
	OBJECT_ID('HumanResources.EmployeeAddress')
)
AND COL.Name = 'ModifiedDate'
-- Place our commands in the temp table
INSERT INTO #RollbackCommands(CommandText)
SELECT 
	'ALTER TABLE '
+	OBJECT_SCHEMA_NAME(COL.id)
+	'.'
+	OBJECT_NAME(COL.id)
+	' DROP COLUMN '
+	COL.Name
FROM syscolumns AS COL
WHERE COL.id IN(
	OBJECT_ID('HumanResources.EmployeeAddress')
)
AND COL.Name = 'ModifiedDate'

Having assembled our commands we now have to execute them in order

DECLARE 
	@CommandID INT,
	@CommandText VARCHAR(1024)
SET @CommandID = 0
WHILE @CommandID IS NOT NULL
	BEGIN
		SELECT @CommandID = MIN(CommandID)
		FROM	#RollbackCommands
		WHERE CommandID>@CommandID
		IF @CommandID IS NOT NULL
			BEGIN
				SELECT @CommandText = CommandText
				FROM #RollbackCommands
				WHERE CommandID = @CommandID
				PRINT @CommandText
				--EXEC(@SQL)    -- Commented out to prevent accidents
			END
	END
ALTER TABLE #RollbackCommands
	DROP CONSTRAINT PK_RollbackCommands
-- If the TEMPDB table already exists then get rid of it.
IF OBJECT_ID('TempDB..#RollbackCommands') IS NOT NULL
	DROP TABLE #RollbackCommands
GO

Conclusion

Although these scripts may look a little verbose initially, the more objects you have to remove from a database the more useful these code snippets become. In fact it is easy to set these up as templates then whoever peer reviews the code knows that they have to verify the list of objects in the WHERE clause.

Three advantages of this approach to rollback are as follows

You don' t have to worry about the order in which objects are destroyed, simply maintain the list of the objects
If constraint names are SQL server assigned names then these scripts derive those names automatically. You don't need to know the names of the constraints.
If you accidentally run the code for a 2^nd or subsequent time it will do no harm. As the system is assembling the list of commands from system tables there is no chance of errors occurring when trying to delete an object that no-longer exists.

Fast Project Rollbacks