My PowerShell scripting madness continues, with the need to automate the setup and teardown of a SQL Server database in prep for running automated functional tests. Here's what I came up with:

# Get the parameters passed to the script
Param($dbInstance, $dbName) 

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$dbServer = new-object Microsoft.SqlServer.Management.Smo.Server ($dbInstance)
$db = new-object Microsoft.SqlServer.Management.Smo.Database 

# Loop thru the db list to find the one we need. If found, set the local
# vars to avoid errors when trying to delete the db from within the loop.
$found = "false"
foreach ($_ in $dbServer.Databases)
{
    if ($_.Name -eq $dbName)
    {
        $db = $_
        $found = "true"
    }
} 

# Now that we're out of the loop we can kill the db
if ($found -eq "true")
{
    "Deleting database $dbName..."
    $dbServer.KillAllProcesses($db.Name)
    $dbServer.KillDatabase($db.Name)
} 

"Creating database $dbName..."
$db = new-object Microsoft.SqlServer.Management.Smo.Database ($dbServer, $dbName)
$db.Create()

So to use this, let's say this is in a script named C:\CreateDatabase.ps1 and you want to create a database named MyDatabase on the default instance of the local SQL Server. This is what it would look like:

The -dbInstance and -dbName are named parameters passed to the script, which are grabbed in the first line of the script. PowerShell didn't like "(local)" passed in, so I had to escape the double-quotes to get it to be recognized properly, although you could certainly use the server name instead; I just wanted to show using "(local)" as an example. But other than that, all works as expected without any issues.

There's several other properties you can set on the database when creating it, such as the size, growth type, and file name, but if all you need are the defaults, the above script will take care of it.

Similar Posts

1. NHibernateRepository
2. How to Suppress PowerShell Errors
3. Compare Assemblies with BitDiffer