An Overview of Database Scripts

  • Article

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 and post-deployment scripts), or they can be general management scripts that you store in the database project.

During a database refactoring operation on a schema object, you can automatically update any script that contains a database object that you rename as part of that operation.

The Scripts folder supports the following actions:

  • Add New Item

  • Add Folder

  • Add Script

  • Exclude from Project

  • Version Control actions (such as check in, check out, and so on)

  • Cut

  • Copy

  • Delete

  • Rename

  • Properties

Scripts that are contained within the Scripts folder support the following actions:

  • Open

  • Open With

  • Exclude from Project

  • Version control actions (such as check in, check out, and so on)

  • Cut

  • Copy

  • Delete

  • Rename

  • Properties

Deployment Scripts

When you build your database project, the pre-deployment script, the database object definitions, and the post-deployment script are merged into one build script. You can specify only a single pre-deployment script and a single post-deployment script, but you can include other scripts from within the pre-deployment script and the post-deployment script. Additional scripts are included by using SQLCMD. (See related section later in this topic.) When you build the database project, all included scripts are merged into the build script.

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. When you designate a script as a pre-deployment or post-deployment script, if another script had previously been designated with that build action, the other script will have its build action automatically set to Not in Build. This behavior is by design because you can only have one pre-deployment and one post-deployment script for each database project.

When you import a database schema, most schema information is imported into the database object definitions within the database project. Some information (such as logins, permissions, and rules, and defaults) is instead imported into additional pre-deployment or post-deployment scripts. When this operation occurs, additional files are created in the Pre-deployment or Post-deployment subfolder within the Scripts folder of the database project. These additional script files are included in the pre-deployment and post-deployment scripts, as appropriate. The included scripts have a Build Action property set to Not in Build because they are not directly included in the build. Instead, they are included indirectly through the pre-deployment or post-deployment script. Any unrecognized script statements are placed into the ScriptsIgnoredOnImport.sql file in the Scripts folder.

Important

If your deployment scripts reference database objects whose names or characteristics later change, you must manually modify the deployment scripts or deployment may fail. For example, if you have rules and defaults that are bound to a table and you rename the table, the deployment script is not updated, and the binding will fail unless you modify the script. If you rename the table by using rename refactoring, the deployment script will be updated as part of the refactoring operation.

Importing Database Schemas and Pre-Deployment Scripts

The file script.predeployment.sql is, by default, the primary pre-deployment script. This script is created automatically as an empty file when you create a database project. When you import a database schema, additional scripts might be created automatically in the same folder as your pre-deployment script. If additional scripts are created, statements are added to the script.predeployment.sql file to include the additional scripts. You can designate a different file as your pre-deployment script by setting its Build Action property to PreDeploy.

Note

You can have only one pre-deployment script with a Build Action property set to PreDeploy. To use multiple scripts in the pre-deployment step, you must designate a top-level pre-deployment script that includes other pre-deployment scripts by using the following statement: SQLCMD :r.\FileName.sql.

Important

You must manually modify the pre-deployment script to create any objects where sufficient information to re-create the objects cannot be imported during a schema import operation. For example, you must modify the script to create encryption keys and certificates.

The script files in the following table are generated when you import a database schema:

File name Include Order Notes

Logins.sql

First

This script contains definitions for all Microsoft SQL Server logins that were defined in the source database. Logins are scripted using CREATE LOGIN in Microsoft SQL Server 2005 and using sp_addlogin in SQL Server 2000.

LinkedServers.sql

Second

This script contains statements to create linked servers. (For more information, see Linking Servers.) When you import the database schema, a sp_addlinkedserver statement is added to the script for each linked server in the source database.

CustomErrors.sql

Third

This script contains user-defined error messages. When you import the database schema, an sp_addmessage statement is added to the script for each custom error message that is defined in the source database.

EncryptionKeysAndCertificates.sql

Fourth

This script contains placeholders for each CREATE SYMMETRIC KEY, CREATE ASYMMETRIC KEY, CREATE MASTER KEY, and CREATE CERTIFICATE statement in the source database.

Note

This script is used only on SQL Server 2005.

Importing Database Schemas and Post-Deployment Scripts

The file script.postdeployment.sql is, by default, the primary post-deployment script. This script is created automatically as an empty file when you create a database project. When you import a database schema, additional scripts might be created. If additional scripts are created, statements are added to the script.postdeployment.sql file to include the additional scripts. You can designate a different file as your post-deployment script by setting its Build Action property to PostDeploy.

Note

You can have only one post-deployment script with a Build Action property set to PostDeploy. To use multiple scripts in the post-deployment step, you must create a top-level post-deployment script that includes other post-deployment scripts by using the following statement: SQLCMD :r.\FileName.sql.

Important

You might need to manually modify the post-deployment scripts to create or update any objects where sufficient information to re-create or modify the objects cannot be imported during a schema import operation. An example would be associating users with roles.

The script files in the following table are generated when you import a database schema:

File name Order Notes

Storage.sql

First

This script contains definitions that relate to storage objects such as files and filegroups.

Permissions.sql

Second

This script contains any GRANT, REVOKE, or DENY permissions for the target database. When you import a database schema, statements are added to Permissions.sql for any permissions that are specified in the source database.

RoleMemberships.sql

Third

This script contains any sp_addrolemember statements that are needed to associate users with roles.

RulesAndDefaults.sql

Fourth

This script contains additional statements to define rules and defaults for the target database. When you import a database schema, additional statements are added to the RulesAndDefaults script to bind rules and defaults to columns or alias data types and to add security accounts as a member of an existing SQL Server database role in the database. See the following table for the statements that will be added to the script.

DatabaseObjectOptions.sql

Fifth

This script contains options that are applied to database objects after deployment has occurred.

Signatures.sql

Sixth

This script contains any signature definitions for the target database. When you import a database schema, additional statements are added to the Signatures.sql script for each signature that is defined in the source database.

Note   This script is used only on SQL Server 2005.

Rules, defaults, and security accounts are added by using the following:

To SQL Server 2005 SQL Server 2000

Bind a rule to a column or alias data type

ALTER TABLE TableName ADD CONSTRAINT ConstraintName CHECK

sp_bindrule

Bind a default to a column or alias data type

ALTER TABLE TableName ADD CONSTRAINT ConstraintName DEFAULT

sp_binddefault

Add a security account as a member of an existing SQL Server database role

sp_addrolemember

sp_addrolemember

Additional Scripts

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

See Also

Tasks

How to: Specify Pre-Deployment or Post-Deployment Scripts
How to: Add Management Scripts to a Database Project

Concepts

An Overview of Database Build and Deployment
Terminology Overview of Team Edition for Database Professionals

Other Resources

Managing Database Connections within the Transact-SQL Editor
Script Analysis and Execution in the Transact-SQL Editor