Validating a Set of Database Scripts using DLM Automation

The basis of all the DLM Automation from Redgate is a series of PowerShell cmdlets. They might look intimidating or confusing, but they aren’t. This is part of a series of posts that examine how you use each one.

Previously I looked at New-DatabaseConnection. In this post, I’ll go through Invoke-DlmDatabaseSchemaValidation. This is the cmdlet that one uses to check if your set of scripts will actually produce a database. This is equivalent to the “build” plugin that exists for a few platforms.

The way this works is that the location of the database scripts is passed to this object through a pipe. This will then validate the scripts on LocalDB with a build of the database and the static data scripts. If this works, then an output object is returned.

A Quick Build

Let’s see how this works. I have a valid database folder on my computer. This has all my object code in subfolders, including static data in the data folder. I want to validate this folder.

2016-11-22 13_56_30-ScriptFolder

I can do that with this code. I’ll pass the location of the scripts into the cmdlet.

$output = “e:\Documents\GitHub\SimpleTalk_Devlopment\ScriptFolder” | Invoke-DlmDatabaseSchemaValidation

When I do this, a LocalDB instance is created and the code validated. I get a message to that effect. The output variable has the confirmation message.

2016-11-22 14_06_39-powershell

This means the code is valid. However, does this really work? Let’s edit some code and see. I’ll change the code for a procedure. Here’s the original GetCountryCodes.sql.

2016-11-22 14_08_29-dbo.GetCountryCodes.sql - Notepad

Let’s change this to top 100 and add an ALTER, but I’ll get an extra comma in there. This is no longer valid SQL.

2016-11-22 14_10_45-dbo.GetCountryCodes.sql - Notepad

Let’s re-run the build. We now see this has failed with an error, and the file is the one I edited:

2016-11-22 14_11_41-powershell

This is a quick look at builds, but there is more that can be done. You can specify the server and database to be used, combining this with the New-DlmDatabaseConnection I previously wrote about.

I urge you to experiment with this cmdlet if you want to perform your own builds.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , , . Bookmark the permalink.