This example explains how to deploy a database using SQL Release without a release management tool.

You'll create a staging database, WidgetStaging, which has the latest version of the development schema. You want to deploy the schema of WidgetStaging to the production database, WidgetProduction, using PowerShell scripts. You can review the update script before you run it.

There are four steps:

1. Create the databases
2. Create the deployment resources 
3. Review the deployment resources
4. Run the deployment

1. Create the databases

To create the example databases, WidgetStaging and WidgetProduction:

1. Download the SQL creation script for the databases here.

2. Run the SQL script in Management Studio.

2. Create the deployment resources

In this step, you run a PowerShell script so SQL Release can:

• connect to WidgetStaging and WidgetProduction and take a snapshot of their schemas
• create the deployment resources that includes the update script SQL Release needs to run a deployment 
• export the deployment resources to a specified folder so you can review the update script

To create the deployment resources:

1. From the Start menu, open Windows PowerShell ISE. If you haven't used PowerShell ISE before, you need to run it as an administrator. 

2. In PowerShell ISE, in the lower pane, run this cmdlet:

    Set-ExecutionPolicy -ExecutionPolicy Unrestricted

   By default, PowerShell ISE only lets you run cmdlets, not scripts. After you run the cmdlet above, you'll be able to run the scripts used in this example.

3. In the top pane of PowerShell ISE, paste this PowerShell script:

   Create and export the deployment resources

   $errorActionPreference = "stop"
    
   # creates objects that let SQL Release connect to the databases
   $stagingState = New-DlmDatabaseConnection -ServerInstance your-server\sql2012 -Database WidgetStaging -Username sa -Password p@ssw0rd
   $productionState = New-DlmDatabaseConnection -ServerInstance your-server\sql2012 -Database WidgetProduction -Username sa -Password p@ssw0rd
   
   # sets up the deployment resources and exports them to disk
   New-DlmDatabaseRelease -Source $stagingState -Target $productionState | Export-DlmDatabaseRelease -Path C:\Work\Export

4. On lines 4 and 5 of the script, change the ServerInstance parameters to the SQL Server you installed the databases on.
5. On lines 4 and 5 of the script, change the Username and Password parameters to the credentials of a SQL Server sa account.
   If you specify the Username and Password parameters, SQL Release connects to the databases using SQL Server authentication. If you leave out these parameters, it uses Windows authentication, and connects using the account that runs PowerShell.
6. On line 8 of the script, change the Path parameter to an empty folder that SQL Release can export to. If the folder isn't empty, the cmdlet will fail.
7. Press F5 to run the script.

3. Review the deployment resources

Go to the location you specified in the Path parameter of the Export-DatabaseRelease cmdlet. In this example, the path is C:\Work\Export. The folder contains the database deployment resources:

Update.sql

This is the update script that you can review to see what will be deployed to WidgetProduction. 

Reports/Changes.html

This web page shows the differences between the schemas of WidgetStaging and WidgetProduction. Review this if you want to see the current schemas of the databases, and a summary of what changes the update script will make. This includes changes to static data. The report also shows warnings of any potential data loss and the SQL update script that will be run.

Reports/Warnings.xml

This XML report contains warnings of potential data loss. This information is also available in the Change.html report.

Filter.scpf

This file contains the filters that were applied to exclude/include specific objects when generating the update script. 

A Filter.scpf file is only included in the database deployment resources when the -Source contains a filter file (eg a scripts folder generated by SQL Source Control), or you specify one using the FilterPath parameter. For more information, see Using SQL Compare filters in SQL Release

RedGateUpdateMetadata.xml and the States folder

You don't need to look at these files. They're used by SQL Release for pre- and post-deployment checks. 

4. Run the deployment

In this step, you run a PowerShell script so SQL Release can:

• import the deployment resources you exported in step 2: Create the deployment resources
• connect to WidgetProduction to check its schema hasn't changed
• deploy WidgetStaging's schema to WidgetProduction

To deploy the changes from WidgetStaging to WidgetProduction:

1. In PowerShell ISE, create a new PowerShell script under File > New.

2. In the top pane of PowerShell ISE, paste this PowerShell script:

   Import and deploy the deployment resources

   $errorActionPreference = "stop"
    
   # creates object that lets SQL Release connect to the production database
   $production = New-DlmDatabaseConnection -ServerInstance your-server\sql2012 -Database WidgetProduction -Username sa -Password p@ssw0rd
   
   # imports the deployment resources you reviewed, and runs the deployment
   Import-DlmDatabaseRelease C:\Work\Export | Use-DlmDatabaseRelease -DeployTo $production

3. On line 4 of the script, change the ServerInstance, Username, and Password parameters to match the PowerShell script from step 2: Create the deployment resources.

4. On line 7 of the script, change the Path parameter to match the PowerShell script from step 2: Create the deployment resources.
5. Press F5 to run the script.

In Management Studio, look at the changes that have been made to WidgetProduction.

What next?

Now you've tried SQL Release, you can move on to setting it up in Octopus Deploy. See Deploying to a single environment using Octopus Deploy

For details of each SQL Release cmdlet, see Cmdlet reference