Test database operations with PowerShell

Microsoft SQL Server comes pre-installed with with the SQLPS PowerShell module. Among its many CmdLets, you will find Invoke-SqlCmd. Using said CmdLet, you can both run queries and invoke scripts against new and existing databases.

Let's pretend that you want to set up a database - BananaRama on your local machine. Just Invoke-SqlCmd -ServerInstance "localhost" -Query "create database BananaRama"

Now, ponder that you have a base script that sets up a schema and a table for a given database. What you will find, is that Invoke-SqlCmd supports Variables that can be injected in your script!

Invoke-SqlCmd -ServerInstance "localhost" -Database "BananaRama" -InputFile "MyFancyScript.sql" -Variable "SchemaName=Customer1"

In your SQL script you use placeholders inside $(DollarParentheses), e.g. $(SchemaName):

CREATE SCHEMA [$(SchemaName)]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [$(SchemaName)].[Contacts](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Firstname] [nvarchar](50) NULL,
    [Lastname] [nvarchar](50) NULL,
 CONSTRAINT [PK_$(SchemaName)] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)...

Using the above knowledge, you can use the Pester ubiquitous test and mock framework for PowerShell to write a script that Creates your database environment, Runs your tests against the environment and then Cleans up after itself. Perhaps something like this: 

Describe "Fancypants testing" {
    BeforeAll {
        Invoke-SqlCmd ... drop and create database (see above) ...
    }
    Context "My app or scenario" {
        It "Should rock your socks off" {
            $result = Start-Process -FilePath $myApp -Wait -PassThru
            $result.ExitCode | Should -Be 1
        }
    }
    AfterAll {
        Invoke-SqlCmd ... drop the database (see above) ...
    }
}

What is more, is that you could generate a new database name every time you run the script too, using PowerShell's Get-Random: 

$databaseName = "my-database-$(Get-Random)"