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



   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.
   Drop-Database -Server .\DWSQL -DatabasePrefix "TestSet" -Verbose
function Drop-Database
    Param (
    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"
        $dropCommand = $DbNames | select -Skip 2 | % { "drop database $_ "}
        $dropCommand | % { Write-Verbose " $_ "}
        $dropCommand | % { & sqlcmd.exe -S $Server -Q "$_"}
    End {

cd C:\PowerShell


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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s