SQL Server - Database Deployment from QA environment to Production Environment

The objective of this document is to provide simple steps for SQL database deployment from one environment to another. For example, after completion of Testing in QA environment, it is best place to script the database and can deploy in another environment.

Following are the steps to deploy database from QA environment to Production environment:

1) Production Backup:

     a. Connect to the Production Database instance

     b. Take the backup of the MYAPPDB database

2) Scripting from QA environment

      a. Open the SQL Server Management Studio

      b. Connect to QA database server instance and script the required database object.

      c. Use if exists then DROP THEN CREATE Statement

      d. Mention the list of scripts so that same can be verify after deployment.

• [dbo].[usp_script1]   

• [dbo].[usp_script2]   

• [dbo].[usp_script3]   

• [dbo].[usp_script4]   

3) Script Deployment to Production environment

      a. Connect to the Production Database instance and deploy the script.

      b. Check the following stored procedure should be present in the Production database

• [dbo].[usp_script1]   

• [dbo].[usp_script2]   

• [dbo].[usp_script3]   

• [dbo].[usp_script4]   

4) Rollback (If required)

      a. Restore the MYAPPDB database from Backup if there is any issue.