Why is a shadow database/schema required?

A shadow database/schema is required so we can identify any changes to your development database that have not been scripted out to the project yet.  We consider the migration scripts in your project to be the source of truth for deployments, so we need to know what the final state of running all the migrations scripts produces and we do this by running them on the Shadow.  We then compare the development database's schema model to the Shadow to show the differences when you go to the Generate migrations tab.  The only reliable way to get this final state of what all the migrations in the project results in is to actually run the scripts against a real database. 

When is the shadow reprovisioned or cleaned?

The shadow database/schema gets automatically updated from the migration scripts in your project when you visit/refresh the Generate migrations tab. This update involves potentially reprovisioning the database (i.e. resetting the shadow database to an empty or baseline state) prior to updating it with the migration scripts depending upon the current state of the shadow.

If no provisioners are used, then the reprovisioning is done by cleaning the database (i.e. dropping all objects). When provisioniners are used, the reprovisioning is done using the associated technology.

Reprovisioning will then require applying all the migration scripts from the project, which is sometimes unnecessary if some of those migration scripts have already been applied. Because of this, the shadow is typically only reprovisioned in the following situations:

• If the migration scripts have been edited in a way that changes their checksum from those recorded in the schema history table.
• If the version of the shadow database is greater than the migration scripts.
• If names or descriptions of the migration scripts on disk do not match what's recorded in the history table.
• If a prior migration attempt failed when applying to the shadow.
• If your project only contains repeatable migration scripts.

If the shadow database is not reprovisioned, then Pending migrations are simply applied on top of the existing state of the shadow.

Creating multiple shadow databases at once

We sometimes get asked how to create a shadow database for the 300 databases I currently have on my dev server.  Here's a sample script for SQL Server that may give you ideas to help with this.

SELECT 'CREATE DATABASE ' + 'z' + name + '_Shadow'
FROM master.sys.databases
WHERE name NOT IN
  ('master', 'model', 'msdb', 'tempdb', 'Resource',
       'distribution' , 'reportserver', 'reportservertempdb')

If there are other databases that are not being actively developed and you don't want a shadow for, then add these names to the NOT IN list.