How to deploy changes to a database from version control

There are several ways to deploy changes from a version control repository to a target database.

Deploy changes with the help of Get Latest in Source Control
Deploy changes using Schema Compare
Deploy changes with the Schema Compare command line
Deploy changes using DevOps Automation
Deploy changes with a batch file from a scripts folder

Manual deployments

Deploy changes with the help of Get Latest in Source Control

Suppose there are two databases - development and production linked to a development repository in the dedicated database development mode. The books table in the development database has been modified.

1. In Object Explorer, right-click the database containing the changes and select Source Control > Commit.

2. In the Local Changes section, select the checkboxes next to the changes you want to commit and click Commit.

Commit the changes

3. The Commit progress window opens, showing the stages of the commit operation. When all the stages are complete, click OK to close the window.

Note

The flow may differ depending on the timeline you link the databases to version control - before or after the commit operation. For more information, see Deploy changes to databases.

4. In Database Explorer, right-click the remote production database and select Source Control > Get Latest.

Note

If you have modified the same object in both databases, Source Control will mark it as a conflict. To resolve a conflict, go to the Conflicts section, select the object and click Get Remote to apply new changes to the remote database. For more information, see Resolve conflicts.

5. In the Remote changes section, select the object and click Get Latest. For more information, see Get the latest version.

Get Latest

6. Confirm that you want to pull the latest changes and click OK. The Get Latest progress window opens, showing the stages of the operation. When all the stages are complete, click OK to close the progress window. The production database will be updated with the latest changes.

Deploy changes using Schema Compare

1. Launch dbForge Schema Compare for SQL Server.

2. On the toolbar, click New Schema Comparison to open the wizard.

3. On the Source and Target page of the wizard, do the following:

Select Source Control as a source type, the required repository, and the revision from version control.
Select Database as a target type, the required connection, and the database.

4. Click Compare to run the comparison.

5. In the Schema Comparison document that opens, select the objects you want to synchronize and click Synchronize objects to the target database at the top of the page.

6. On the Output page of the Schema Synchronization Wizard, select Execute the script directly against the target database and click Synchronize to deploy the database to the specified revision.

For more detailed instructions, see the Schema Compare documentation.

Automated deployments

Deploy changes with the Schema Compare command line

1. Create a copy of the scripts folder on your local machine or check it out from source control.

2. Deploy the local scripts folder to the target database.

For more detailed instructions, see deploying a scripts folder with the dbForge Schema Compare command line.

Deploy changes using DevOps Automation

The dbForge DevOps Automation for SQL Server product has a PowerShell cmdlet allowing you to compare and synchronize a required scripts folder with the target database.

To learn more about deploying a database using the cmdlet, please refer to our DevOps Automation for SQL Server documentation.

Deploy changes with a batch file from a scripts folder

1. Create a batch file with the following content:

sqlcmd -S "server"-U "login"-P "password"-i "input_file" 
PAUSE 

where input_file is a path to the SQL file that will create the database.

2. Create a single SQL file that contains a script to add a new database. For example, the script can be as follows:

SET NOCOUNT ON
 GO
 PRINT 'Creating sales_demo1 database'
 USE [master]
 GO
 DECLARE @db_name NVARCHAR(255);
 SET @db_name = N'sales_demo';
 IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = @db_name)
 BEGIN
   EXEC (N'ALTER DATABASE '+@db_name+N' SET SINGLE_USER WITH ROLLBACK IMMEDIATE');
   EXEC (N'DROP DATABASE '+@db_name);
 END;
 EXEC (N'CREATE DATABASE '+@db_name);
 GO
 USE sales_demo
 GO
 :On Error exit
 :r "D:\sales_demo\Tables\dbo.Customers.sql"
 :r "D:\sales_demo\Tables\dbo.OrderLines.sql"
 :r "D:\sales_demo\Tables\dbo.Orders.sql"
 :r "D:\sales_demo\Tables\dbo.Products.sql"
 PRINT 'Creation is Completed'
 GO

where :r is a SQLCMD command that parses additional Transact-SQL statements and SQLCMD commands from the file specified by <filename> into the statement cache.

3. Launch the command prompt and execute the batch file you’ve created.

To learn more about how to deploy a database from Source Control, see How to Build a Database from Source Control.