To import objects from an existing database:
Create a new ReadyRoll Database project/solution. Once the project is created, the ReadyRoll window should appear.
If the ReadyRoll window does not automatically appear, from the View menu in Visual Studio, select ReadyRoll.
Step through the wizard to connect to the database you would like to import.
As ReadyRoll creates objects within your database during import (a table called [dbo].[__MigrationLog] plus a view called [dbo].[__MigrationLogCurrent]), we recommend against connecting your project directly to a Production instance of SQL Server to avoid any potential impact to your business systems.
Instead, we recommend taking a backup of your Production database, removing any sensitive data, and restoring it onto a Test or Development SQL Server that you can import from.
Click Import to generate a baseline script for the existing objects within the database.
The wizard will import the entire schema within the database, generating scripts for all of the contained objects. If this is what you want, then you can skip straight to step 6 to verify the imported migration.
On the other hand, if you only want to import a sub-set of objects, go ahead and delete the migration that was added to the project by this step (within the Migrations sub-folder) and continue to the next step to de-select the objects that you do not want to import.
To create a new migration containing the existing objects, click Import (Generate Script).
The ReadyRoll window will update, showing a summary of the objects imported:
This step inserts a row into the
[__MigrationLog] table within the source database, marking the migration as deployed. This prevents the migration from being executed against the database that you are currently connected to. However in order to prevent the script from being executed against other environments, you will need to set a baseline.
To test the deployment of the newly-generated script against the Shadow database, click Refresh.
For more information about the shadow database, see Target and shadow databases.
This set of steps will allow you to reproduce the "shell" of your database from scratch; you will be able to deploy your tables, views, procedures etc to a new database, however it will not include any data.
For information on how to populate your database with real-world data (including reference/lookup/static data), see this article on data population techniques.
Before you can start deploying your database project to a pre-existing database, you need to set a baseline within the database. A baseline is a "line in the sand" between the current state of your existing Production database and any new changes to be deployed. If your Production database does not exist yet, then you do not need to set a baseline.
There are a number of different ways that you can set up the database project for your first ReadyRoll deployment. However what actually happens when you use these different methods is basically the same:
What happens when you Import directly from Production
If you followed the last method (connected your project directly to Production and imported migrations) then your database is already set up for the first deployment: no further action is needed, as your Production environment was already populated with the required migration log records when you clicked Import. Once you've authored some new migrations, you'll be ready to perform your first deployment.
Ensuring that new db changes are included in deployments
When deciding which migrations to include in the baseline, firstly ensure that the T-SQL logic to reproduce your Production database is kept in separate migrations to any changes that you want to actually be deployed to Production. For example, the CREATE TABLE statements for any pre-existing objects should be included in the baseline (e.g. within a script called 001_Imported-Objects.sql), whereas a subsequent ALTER TABLE ADD COLUMN statement should not be included in the baseline (e.g. within a script called 002_New-Changes.sql).
Pre-deployment baseline check
If you attempt to deploy your database project (or a package created from your database project) to an existing database prior to configuring a baseline, ReadyRoll will raise an error and abort the deployment before any scripts are executed. This is to ensure that you do not accidentally deploy over the top an existing database. To override this error and force the deployment to continue, specify the $ForceDeployWithoutBaseline = True variable in PowerShell (or add ForceDeployWithoutBaseline=True as a variable within your Octopus Deploy project).
If you have many pre-existing databases in your environments, to save time you may like to consider using the semver method to set the baseline. This method actually stores the baseline within the database project itself, effectively making the act of setting the baseline a one-time operation.
For detailed information on how this method works, see setting the baseline with semantic versioning.
To use the semver method to configure the baseline for your project: select the Set the baseline for existing database using the first semver folder checkbox within the project designer (Project Settings tab). During the first deployment to a pre-existing database, this will cause ReadyRoll to mark-as-deployed any scripts have been put into the Migrations\1.0.0-Baseline folder, thus preventing the scripts from being executed. Any scripts placed within subsequent semver folders (e.g. 1.1.0-NewChanges) will then subsequently be executed. this folder will be executed.
You only have one opportunity to set the baseline in a given target database: once the first deployment to an existing database is completed, no additional scripts will be marked as deployed. For example, if there are five scripts in the baseline folder when the database is deployed with ReadyRoll for the first time, then all five scripts will be marked as deployed. However, if a sixth script is added to the baseline folder later, those scripts will be executed on the target database rather than added to the baseline.
If you have enabled the programmable objects feature in your project, it is possible to also exclude those from being executed during the first deployment to a pre-existing database. To mark-as-deployed any scripts within the Programmable Objects sub-folder, select the Mark all programmable objects/additional scripts as deployed when setting the baseline checkbox (this can be found under the Additional Scripts section of the Project Settings tab).
Programmable objects baseline
If you plan to use the Mark all programmable objects/additional scripts as deployed when setting the baseline option, it is highly recommended that you do not make any changes to the logic contained within your programmable objects prior to the initial deployment to an existing database. This is because any changes to the object scripts will not be applied in the first deployment; the deployment of the object will only occur once a further change to the script files is made and subsequently deployed.
If you do need to make logic changes to a programmable object as part of your first deployment, copy the contents of the affected object and paste it into a new migration, e.g. Migrations/1.1.0-NewChanges/001_Alter-MyProc.sql.
To set a baseline within Visual Studio, connect the project to the existing database and open the imported object script for editing.
Click Mark as Deployed to insert the migration log record. Repeat this process for any other scripts that were imported from your Production database.
When you deploy the database project, the scripts that you marked as deployed will not be executed.
To set a baseline at the command line, use the MSBuild tool to deploy the migration log objects and insert the baseline row(s). Pass in a value for the
/p:BaselineUptoMigrationId= argument containing the ID of the Migration script up to which you want your baseline to be established (see notes below). To retrieve the ID, open the migration script (e.g. 0001_Imported-Objects.sql) and copy the value of the ID attribute within the
<Migration ID="guid" /> header.
C:\Windows\Microsoft.NET\Framework\v4.0.30319\MSBuild.exe AdventureWorks.sqlproj /p:DBDeployOnBuild=True /p:TargetServer=MYDBSERVER /p:BaselineUptoMigrationId=7903c16c-8c7c-4c7f-bc24-956ab976b895
Including multiple scripts in the baseline
Typically the first script you have imported is used as the baseline, but If you created multiple scripts when you imported your database, specify the last script that was imported/added to the project for the BaselineUptoMigrationId property value. For example, if you specify the ID of
003_Imported-Data.sql, then scripts 001, 002 and 003 will be included in the baseline.
By default, ReadyRoll will use Windows Authentication to connect to your database. If you would like to use SQL Server authentication instead, specify the /p:TargetUserName=... and /p:TargetPassword=... command line arguments.