Scenario: Create and Modify Database Scripts

Database scripts are additional files that contain Transact-SQL (T-SQL) statements or utilities such as SQLCMD that are not part of the database schema definition. You can use database scripts as part of the deployment step (pre-deployment scripts and post-deployment scripts), or you can store general management scripts in the database project.

If you rename a database object, you can use database refactoring to automatically update any references to that object in your scripts.

Deployment Scripts

When you build your database project, the pre-deployment script, the database object definitions, and the post-deployment script are compiled into one database schema file (.dbschema). You can specify only a single pre-deployment script and a single post-deployment script, but you can use SQLCMD to include other scripts from within those scripts. For more information, see Including multiple scripts in the pre-deployment or post-deployment step. When you build the database project, all included scripts are merged into the build script.

By default, your database project contains an empty pre-deployment script and an empty post-deployment script. These scripts are named script.predeployment.sql and script.postdeployment.sql.

You designate a pre-deployment script by setting the Build Action property of the script file to PreDeploy. Similarly, you designate a post-deployment script by setting the Build Action property of the script file to PostDeploy. If you designate a script as a pre-deployment or post-deployment script and then designate another script the same way, the first script will have its build action automatically set to Not in Build. This behavior is by design because you can have only one pre-deployment and one post-deployment script for each database project.

Important Note:

Your deployment might fail if you change the names or characteristics of objects to which your deployment scripts refer but you do not update the references in your scripts. For example, you might insert data into a table in your post-deployment script. If you rename the table but do not update the script, the INSERT statements will fail. If you rename the table by using rename refactoring, the deployment script will be updated.

Including multiple scripts in the pre-deployment or post-deployment step

To use multiple scripts in the pre-deployment or post-deployment step, you must designate a top-level script that includes other pre-deployment or post-deployment scripts by using the following statement:

SQLCMD :r.\FileName.sql

Additional Scripts

In addition to deployment scripts, you can add other general-purpose scripts to your database project. You can organize these scripts by creating subfolders under the Scripts folder in your database project. When you open any one of these additional scripts in the T-SQL editor, you can connect to your database server and execute all or part of that script. If you run routine auditing scripts, this approach is a convenient way to keep each script together with its associated database, and also to put both the script and the database under the same version control system as your database schema. These scripts will have their Build Action property set to Not in Build.

Common Tasks

In the following table, you can find descriptions of common tasks that support this scenario and links to more information about how you can successfully complete those tasks.

Task	Supporting Content
Specify pre-deployment or post-deployment scripts: You can add T-SQL statements to scripts that run before or after your database is deployed. When you import objects and settings from a database, the deployment scripts might be updated with statements that are not represented as database objects, such as permissions, role definitions, or signatures.	How to: Specify Pre-Deployment or Post-Deployment Scripts
Specify management or utility scripts: You can add management and utility scripts to your database project. You can also update your scripts when you refactor objects in your database project.	How to: Add Management Scripts to a Database Project Refactor Database Code and Data
Check in your changes: You can check your scripts in to version control to share them with your team. By putting your scripts under version control, you can store and retrieve the deployment and maintenance scripts that matched an older version of the database if you must service that version.	How to: Add a Project or Solution to Version Control Using the Check In and Pending Changes Windows How to: Check In Pending Changes
Troubleshoot problems: You can learn more about how to troubleshoot common problems with database and server projects.	Troubleshooting Database Project, Build, and Deployment Issues

Related Scenarios

• Scenario: Create and Modify Database and Server Objects
  You update your database or server when you modify the object definitions in your project and then deploy those changes to a target database or server.

• Refactor Database Code and Data
  You can use database refactoring to eliminate some repetitive and error-prone tasks, such as renaming all references to an object, fully qualifying names, expanding wildcard characters, or moving an object to a new schema.

• Build and Deploy Databases to an Isolated Development Environment
  After you modify objects in your project, you build and deploy the changes to your isolated development environment.

• Improving Database Code with Static Analysis
  You can analyze your database code to find and fix common design, naming, and performance issues.

See Also

Concepts

Performing Iterative Database Development

Terminology Overview of Database Edition

Rename All References to a Database Object

Build and Deploy Databases to an Isolated Development Environment

Build and Deploy Databases to a Staging or Production Environment