The topic describes methods for automating database deployments in a DevOps environment using dbForge tools.
1. Create a local copy of the scripts folder or check it out from source control.
2. Use the Schema Compare tool to deploy the scripts folder to the target database using the command-line interface.
dbForge DevOps Automation for SQL Server includes a PowerShell cmdlet that compares a scripts folder with a target database and applies changes.
For instructions, see the DevOps Automation for SQL Server documentation.
You can automate deployment by executing a batch file that uses sqlcmd to run a predefined SQL script.
To deploy changes:
1. Create a SQL file that contains a script to add a new database.
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.
2. 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.
3. Save the file with a .bat extension.
4. Open a command prompt and run the batch file.