Record changes to the database that is being version controlled
In the previous tutorial, we showed how to get started with DB Version Control tool to start versioning your SQL Server database.
In this tutorial, we will demonstrate how to make changes to the database (AdventureWorks) and keep them in the version control system (local GIT repository using Github Desktop).
First, let's make a small change to AdventureWorks database by adding a new column Notes (nvarchar(2000) NULL) to the Production.Product table.
Now, we go back to DB Version Control project and click Get changes in the Changes tab.
We will be shown Changes found message and can view a list of differences between the current AdventureWorks database and scripted database files.
We will see that Production.Project table has been modified, and if we click on the object, we can see the before and after versions of this table. Scrolling down, we will see that a new column now exists - the DB Version Control tool successfully detected the changes we did manually to AdventureWorks database.
If we are happy with the change, we can now script these changes to our local GIT repository.
To proceed, click Apply changes button - a popup will appear to enter additional information:
- Script version
- Script name
- Script options
What is "the script" and why does the tool ask to enter script details?
DB Version Control is a tool based on database migrations. That is, for each change in the database, a new database migration script will be generated automatically. The database migration script is a file with SQL commands that should be executed.
The script's version is mostly needed for advanced scenarios when database branching is used, therefore we can leave the current number as is.
The script's name will be added to the generated migration file name.
The script's options allow specifying how to execute the script.
When clicking Apply, the tool will:
- generate migration script automatically
- apply the script to the temporary database
- add migration script to the Migrations directory
- update scripted database objects in the files
- re-compare the current database with the temporary database to check for other changes
Viewing scripted changes
Once the change has been applied, and the database has been re-compared, there should be no changes between AdventureWorks database and scripted database files.
The scripted database files can be reviewed and committed to our version control system.
We can see that:
- a new migration script has been added to the Migrations directory
- Tables\Production.Product.sql file changed (an additional column was added)
- Two new were objects added - schema dbup and table dbup.Versions. This table is being used to record which scripts have been executed in the database (added once only)
Adding your own change script
If making changes through UI is not your thing or if the changes are more complex, you can add your own change script manually instead of relying on DB Version Control to generate a change script for you.
From Changes tab, click New migration button - a new empty SQL migration file will be created and opened within SSMS where you can enter your SQL statements.
For example, we modified [Production].[vProductAndDescription] view with our additional Notes column.
Once we go back to DB Version Control window, we will see a new script under Not applied scripts - this is the script that we just created.
To apply script, click Apply script - you will be presented with additional script options as before - you can rename the script, change the version number, or set script execution options. Note that if you already applied the script on the database that is being version controlled, you may want to tick Do not execute on source database option as the script may fail.
Viewing scripted changes
Same as before, we can review what Apply script did for our scripted database files:
- added a new file to Migrations directory with the script we wrote
- updated Views\Production.vProductAndDescription.sql
Note that not only our view changed but the unique clustered index no longer exists on this view! Why? This is documented by Microsoft:
ALTER VIEW can be applied to indexed views; however, ALTER VIEW unconditionally drops all indexes on the view
This is one of the benefits of scripting database objects into files - we can see how individual database objects changed (this change would be difficult to spot through migration scripts).
Why add a script through DB Version Control instead of adding it directly to the Migrations directory? Because DB Version Control updates scripted individual database files automatically after the script has been executed.
Preview automatically generated script
It may be difficult to trust a new tool to generate the change script automatically - but you can preview the script and even modify it as need.
For example, we added a new TemporaryField column to Product.ProductDescription table.
If we choose to see the changes with Get Changes, we will see this new change appearing in the list of differences.
Clicking on Preview script button will generate a new migration script and will open it in SSMS for review. The script will also appear under Not applied scripts section and can be applied or removed.
DB Version Control also allows discarding (revert) changes that have not been applied yet.
If we would like to remove TemporaryField, from Changes tab we would click Discard changes button - you would get an informational message that data may be affected (in this case, we are dropping a column).
Clicking Discard changes will drop the column and re-compare the database with scripted files - there should be no other differences between the database and scripted database files.
In this tutorial, we demonstrated:
- How DB Version Control automatically tracks changes in the database and generates migration scripts automatically
- The benefits of database object scripting into individual files
- Adding your own SQL script
- Previewing automatically generated change script
- Discarding not yet applied changes
In the next tutorial, we will deploy a database from the scripted database files.