Migrations scripts is all you need to update your database when deploying.
All heavy lifting has already been done when migration scripts were created therefore the deployment process is quite simple:
- Get a list of migrations from the version control system
- If the target database does not exist:
- Create a new database using Create database migration script
- Find and execute last Baseline migration script
- Apply migration scripts after last Baseline migration script
- If the target database exists:
- Get a list of applied migrations from Versions table
- Compare with a list of migrations from version control (excluding Baseline migration scripts)
- If a migration file exists but it is not recorded in Versions table, execute the script inside a transaction (unless NOTRANSACTION option specified)
You can use any tool to execute migration scripts during deployment process but we recommend using Migration runner (free) because it:
- Keeps track of applied migration scripts and script output in Versions table
- Supports variable substitution
- Executes each migration script inside a transaction
What happens if the transaction fails?
The change is rolled back, Versions table is not updated and the rest of migration scripts are not applied.
Does Migration runner check for target database drift?
No, Migration runner does not check if there were any changes made to the target database.
Does Migration runner verify that target database is exactly the same as scripted database files?
No, Migration runner simply executes the scripts.