The shadow database is automatically created and maintained by SQL Change Automation if you are using a database as your development source. If you are using a SQL Source Control project you will need to specify one. This database represents the result of running the migrations in your project against an empty database.

The shadow database is used by SQL Change Automation to:

• Perform comparisons against the development source for generating and applying migrations
• Verify the contents of the project
• Generate the offline schema model

SQL Change Automation will create and delete the shadow database as needed, however it is safe to remove manually at any time.

Performance

The shadow database is used heavily with the SQL Change Automation Visual Studio and SSMS plugins and it is important to have a fast connection to it. It is very often preferable for it to be located locally.

When dealing with large databases, dropping and rebuilding the shadow database will be slower.

SQL Change Automation will therefore only trigger a full clean and rebuild of the shadow database if the existing state of the shadow database is considered to no longer match the project.

This will happen if a migration is manually edited or deleted or a programmable object is manually deleted. In such scenarios the next operation involving the shadow database will take longer than usual to complete. 

Generating migrations or programmable objects automatically, or adding new scripts manually will never cause the shadow database to be rebuilt.

Configuration

By default, the shadow database will be created on the same SQL Server Instance as the development database with the name [project]_[YourUserName]_SHADOW. When connecting to a development database which is remote, it may be desirable to configure the shadow to be local instead, either to improve performance or to avoid incurring additional cost.

When using a SQL Source Control project as the development source a shadow database must be configured.

You can change where the shadow database is created by editing the user settings file for your project:

• You may need to create the file if it doesn't exist, make sure it follows the naming convention: '<YourProjectName>.sqlproj.user'
• This user file contains settings specific to your local environment which shouldn't be committed to version control – see our list of files to be excluded from version control
• When using a database as a development source, you may choose to omit the "Initial Catalog" portion of the ShadowConnectionString. In this case the shadow database name will continue to be derived from the development database name and the shadow database will change if the development database is changed.

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="15.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <ShadowConnectionString>Data Source=DEV-DBServer;Initial Catalog=DevelopmentDatabase_shadow;Integrated Security=True</ShadowConnectionString>
  </PropertyGroup>
</Project>

Learn more

• Using Azure to host you development database