This example shows you how use the DLM Automation cmdlets and TeamCity's web interface to validate, test and sync your development schema.

The cmdlets that we'll use will perform the following tasks:

1. Validate: Validate the schema in the source control scripts folder by checking the database can be built from scratch successfully. 
2. Test: Run tSQLt tests on the database schema.
3. Sync: Update a test database to match the database schema.
4. Package and publish: Add the development schema to a database package and publish this for later use in your deployment process.

Before you start

1. Install DLM Automation on your TeamCity agent. For more information, see Installing.
   
2. Get a copy of your database scripts folder, you can either:
   1. checkout a copy from your database source controlled using SQL Source Control
   2. generate a folder from an existing database using SQL Compare
   3. download and unzip WidgetShop.zip
3. Place that folder into source control.
4. Create a blank database that we can update.
   
5. Create a TeamCity configuration to perform your database continous integration.

Performing Continuous Integration tasks

1. Link your TeamCity configuration to your source control from earlier. Configuring Version Control System (TeamCity Documentation).
   

2. Add a PowerShell Build Step.
   
3. Change Script to Source Code.

4. In the Script Source enter:

   Validating the scripts folder

   $errorActionPreference = "stop"
    
   # Validate the scripts folder
   $scriptsFolder = "%teamcity.build.checkoutDir%" # The scripts folder to validate, test and sync
   $validatedScriptsFolder = Invoke-DlmDatabaseSchemaValidation $scriptsFolder

5. This will check that your scripts folder is valid and can be used to produce a database.

6. Add the following to the end of the script:

   Test the scripts folder

   # Run tSQLt tests
   $testResults = Invoke-DlmDatabaseTests $validatedScriptsFolder
   $resultsFile = "%teamcity.build.checkoutDir%\results.junit.xml"
   Export-DlmDatabaseTestResults $testResults -OutputFile $resultsFile
   Write-Host "##teamcity[importData type='junit' path='$resultsFile']"

7. This will run any tests in your database and publish the results to TeamCity.

8. Add these line to the end of your script:

   Syncing to a test database

   # Sync a test database
   $testdb = New-DlmDatabaseConnection -ServerInstance ".\sql2012" -Database "TestDatabase" # Update this to use the blank database you created earlier
   Test-DlmDatabaseConnection $testdb
   Sync-DlmDatabaseSchema -Source $validatedScriptsFolder -Target $testdb

9. Update the connection details to the blank database you created earlier. If using SQL Server Authentication, add the desired credentials using the -Username and -Password parameters.

10. This will update the test database to match your scripts folder.

11. Finally add this to the end of the script:

    Creating a package

    # Create a Package and Publish to TeamCity
    $packageName = "WidgetShop"
    New-DlmDatabasePackage $validatedScriptsFolder -PackageId $packageName -PackageVersion "%build.number%" | Export-DlmDatabasePackage -Path "%system.teamcity.build.tempDir%"
    Write-Host "##teamcity[publishArtifacts '%system.teamcity.build.tempDir%\*.nupkg'"

12. This will publish a package containing your database schema to TeamCity. If you have enabled TeamCity's NuGet server it will appear there, otherwise you can use a NuGet Publish step to upload to an external feed.
13. The full script will look like:
    
14. Click Save. You can now trigger a build and review your results.