How to deploy changes to a database from source control

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

Contents

Manual deployments

Deploy changes with the help of Get Latest changes of Source Control

1. After you have added some changes to a local database in SQL Server Management Studio, right-click this database in Object Explorer and navigate to Source Control > Commit.

2. Ensure that Local Changes is selected and click Commit.

3. Confirm that you want to commit the changes, click OK.

4. Right-click a remote database in Object Explorer and navigate to Source Control > Get Latest.

5. You will see that there is a conflict because the databases are different. To apply new changes for the remote database, select Conflict and click Get Remote. For more information, refer to Resolving conflicts.

6. Select Remote Changes and click Get Latest. For more information, refer to Getting the latest version.

7. Confirm that you want to pull the latest changes, click OK. That’s it. The changes will be added to the remote database.

Deploy changes using Schema Compare

  1. Launch dbForge Schema Compare for SQL Server.

  2. Under Source, select a required revision from version control.

  3. Run the comparison.

  4. Launch the Schema Synchronization Wizard and deploy the 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 the 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 '[email protected]db_name+N' SET SINGLE_USER WITH ROLLBACK IMMEDIATE');
   EXEC (N'DROP DATABASE '[email protected]db_name);
 END;
 EXEC (N'CREATE DATABASE '[email protected]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 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.