Creating and Modify Database Scripts
Database scripts are additional files that contain Transact-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
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 Transact-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 Transact-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. |
|
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. |
|
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. |
|
Troubleshoot problems: You can learn more about how to troubleshoot common problems with database and server projects. |
Related Scenarios
Creating 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.Analyzing Database Code to Improve Code Quality
You can analyze your database code to find and fix common design, naming, and performance issues.
See Also
Concepts
Writing and Changing Database Code
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