PowerShell and SqlServer: dropping a set of databases

I have a bunch of databases which follow a prefix pattern.

For resetting my tests, I want to drop them, using PowerShell. Assumption: no concern about anyone/thing accessing those databases at that time.

This does that:

PS C:\PowerShell> Drop-Database -Server .\DWSQL -DatabasePrefix "TestSet" -Verbose

Setdb03

Setdb02

<#
.Synopsis
   Drop set of databases with a given prefix, using a trusted connection.
   Ensure that you do not specify Masters or any system database.
   If no databases are found matching the prefix, it reports and returns.
   Else each found database is dropped, silently.
   If a SQL error happens, then that is reported to the screen.
.Example
   Drop-Database -Server .\DWSQL -DatabasePrefix "TestSet" -Verbose
#>
function Drop-Database
{
    [CmdletBinding(SupportsShouldProcess=$true)]
    Param (
        [Parameter(Mandatory=$true)]
        $Server,
        [Parameter(Mandatory=$true)]
        $DatabasePrefix
    )
    Begin {
    }
    Process {
        $sqlCommand = "set nocount on; select name from sys.databases where name like '$DatabasePrefix%'"
        Write-Verbose $sqlCommand
        $DbNames = @(sqlcmd.exe -S $Server -Q $sqlCommand)
        if ($DbNames.Count -le 2) {
            Write-Host "No database prefix [$DatabasePrefix] found"
            return
        }
        $dropCommand = $DbNames | select -Skip 2 | % { "drop database $_ "}
        $dropCommand | % { Write-Verbose " $_ "}
        $dropCommand | % { & sqlcmd.exe -S $Server -Q "$_"}
    }
    End {
    }
}

cd C:\PowerShell

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s