Database Deployment scripts in Powershell

To enable all database changes to be deployed to differenet environments;

firstly ensure each change is scripted in a seperate file.

create a build file and record the name of each change file within it.

use the following powershell script to read the build file and submit the change files one at a time.

$server_name = read-host -Prompt "Servername"
$db_name = read-host -Prompt "Database"

# connect to server
$server = new-object ("Microsoft.SqlServer.Management.Smo.Server") $server_name
if ($windows_login -eq $false)
{
# prompt for connection details
$server.ConnectionContext.LoginSecure=$false;
$credential = Get-Credential
$username = $credential.UserName -replace("\\","")
$server.ConnectionContext.set_Login($username)
$server.ConnectionContext.set_SecurePassword($credential.Password)
}
$db = $server.Databases[$db_name]

# read the build list
$build_list = (get-content C:\Projects\build-list.txt)

foreach ($filename in $build_list)
{
write-host "running " $filename
$stream = New-Object System.IO.StreamReader($filename)
$script = $stream.ReadToEnd()

$db.ExecuteNonQuery( $script, 1 )
}
write-host "build complete"