I had some very thoughtful conversations (not lectures … really) this week about deploying changes to SQL Server databases. I want to share some of those best practice tips here with you today.

1. Don’t rely on manual steps: As I see it, there are two reasons why manual steps are bad. Humans are imperfect, are errors happen. When you rely on manual steps you are increasing the odds that some important step will be missed or done incorrectly. It’s a fact of life, especially in I.T., that people make mistakes. Especially if it has been a long night and people are tired.
   
   Secondly, I often see people use manual steps in place of things they don’t want to bother fixing or automating. If they can push the work off on someone else by making it a manual step then as far as they’re concerned, the problem doesn’t exist. It’s their problem. If I am asked to perform a manual task I require that they have a plan to correct it and/or a bug filed so that it doesn’t become “my problem”.
2. Always have a rollback plan: Sometimes things go wrong even with thorough testing. Every deployment plan must have a rollback plan. And before you say, “we back up the database before the deployment,” that’s not a rollback plan. That’s a last resort plan. What if the problem that necessitates a rollback doesn’t present until a day or two into the business week? Are you going to restore the old backup and lose days worth of data? There may be cases where you can do that, but if you can’t afford to lose a lot of data, you have to be able to back out any change that is deployed.
3. Always have a backup: The number one job of a DBA is to never need a backup and not have one. Under no circumstances is that okay. If the database is small enough, take a copy-only full backup prior to the deployment. The copy-only part is so that it doesn’t change your restore process in case of a disaster. You don’t even have to save this backup long-term.
   
   If the database is large, then at least make sure that you have proper backups in place, and start the deployment immediately after the log backup job runs. Be sure to note somewhere when you started so you know when to restore to if you have to restore.
   
   And remember, this is your last resort plan, not your rollback plan.
4. Scripts should be re-runnable: Scripts will occasionally experience errors in the middle. The longer the script, the greater the chance it will have an error. Have you ever fixed an error in the script and then ran it again only to get a flood of errors that objects already exist or that objects don’t exist. Part of the script committed and part of it did not. For these cases, making the script re-runnable is as simple as including checks for existence when creating or dropping objects. In fact, it’s even easier now that T-SQL supports IF EXISTS in certain drop and create commands.
   
   Or you can use the SET XACT_ABORT on to ensure that the whole script commits or rolls back.
5. Review scripts before running them: Over the years, I’ve seen some crazy things in deployment scripts. People granting themselves sysadmin permissions, setting the page verification setting to none for the database, changing the recovery model, etc. Even if there is no formal code review process, you should review the code yourself for potentially dangerous code like this.