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.
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.
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.
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.
Let’s re-run the build. We now see this has failed with an error, and the file is the one I edited:
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.
Filed under: Blog Tagged: continuous integration, DLM, powershell, Redgate, syndicated