Setting up a project with an existing database in SSMS
Published 15 August 2019
In this scenario, we’ll set up a new SQL Change Automation project for an existing database. Specifically, we’ll:
- Set up an example database, AdventureWorks
- Create a new SQL Change Automation project within SQL Server Management Studio (SSMS)
- Set up a new development database
- Baseline the development database from AdventureWorks
- Make changes to the development database, and generate migrations for the SQL Change Automation project
Setting up AdventureWorks
Create an AdventureWorks database matching your version of SQL Server.
Creating a new project
1. Open SQL Change Automation
In SSMS, start SQL Change Automation by clicking on the SQL Change Automation icon in the toolbar. If it doesn't show here, it will be under the Tools drop-down menu.
2. Create a new project
Click New project…. This will open the project setup wizard.
Enter a Project name and click Browse... to launch a file explorer window to choose where you want to save your project. Click Set development source… to open the datasource connection dialogue.
Next click on New Database and provide valid entries for the development database to create. We'll call our development database AdventureWorks_Dev.
Back on the wizard page, click Next.
The next page will allow you to specify a SQL Compare filter file for your project. We’re going to filter out a DDL trigger from our baseline of the existing database. We have specified a SQL Compare filter file, Filter.scpf. Import this filter file by clicking the Apply filters button, and click Browse... to launch a file explorer window.
Click Next to continue to the next page of the wizard.
3. Set a baseline
On the last page of the wizard, you can select a database from which to generate a baseline.
We’re going to create a baseline from the AdventureWorks database.
The schema of the deployment target will be read in order to create a baseline schema. This will not modify the deployment target in any way.
Click Connect to a database..., and specify credentials for the AdventureWorks database. Click Create project to begin the baseline process, this will:
- Create two folders – 1.0.0-Baseline and 1.1.0-Changes, under the Migrations directory
- Import the schema of your deployment target into the 1.0.0-Baseline folder as a migration script
- This is a deployable representation of the schema – by running this script on an empty database you can re-create the schema of your deployment target
- Populate the offline schema model
- This is a static representation of the schema on your deployment target
- You will be prompted to enable programmable objects later on in the process
4. Apply the baseline schema
The baseline overview page will appear.
In this case, we have an unsupported programmable object, which requires modification if we want it to be handled as a programmable object in the database project.
Click Apply baseline schema to apply the baseline to your development database.
You can now see an overview of all the scripts and programmable objects that will be applied as part of the baseline. Click Apply.
If you experience any issues applying the baseline schema (e.g. it contains invalid objects or cross database dependencies) consider using a SQL Clone image instead. Please check the full documentation for more details.
Now you can see all the scripts that have been executed on AdventureWorks_Dev as part of the baseline process. This means that AdventureWorks_Dev now reflects the schema of AdventureWorks.
Next steps
- You can now capture changes made to AdventureWorks_Dev in the SQL Change Automation project. See generate migrations.
- There’s no need to make any changes to AdventureWorks itself - all development work can occur on AdventureWorks_Dev.
- When you want to deploy your changes on AdventureWorks_Dev onto AdventureWorks, you can use the PowerShell cmdlets. See automated deployments.
