Rolling back can be a very painful experience but it doesn’t need to be. Bad things will happen even if you have tested and planned your deployment. People tend to forget until it is too late that the database is a key piece of the business.
What can we do on the SQL Server side to minimize rollback failures?
1. DBCC CheckDB \ Backup \ Restore
How do these three items affect my rollback?
- Is the database free of errors?
- When was the last time you ran DBCC CheckDB or reviewed the results?
- Are you performing good backups?
- Are you performing regular restores, the ones from tape?
- When was the last time you restored the master database or any system databases?
2. Adding a Log Table to your deployment scripts.
A log table, this is a great way to keep track of the deploy script progress. For each progress step insert a comment.
Create table ##LogTable
(rownbr int IDENTITY(1,1),
INSERT INTO ##LogTable
(dt, comment) values (GETDATE(), ‘Add Your Progress Comments’)
3. Create a Rollback Script
A Rollback Script will undo the changes from the deployment script, for example drop the new objects (table, store procedure, index) that was created.
4. Explicit Transactions
By adding Explicit Transactions (Begin Transaction, Commit and Rollback) your deployment scripts will rollback on any error in your code. I am working on an Explicit Transaction demo for an upcoming blog post.
A few thoughts
No matter how much testing you do, Bad Things Happen.
What takes seconds to run, will take hours to unwind.