How to delete SQL Server databases trough vNEXT build task properly

Question

We have a PowerShell cleanup script for our test machines:

$sqlConnection = new-object system.data.SqlClient.SqlConnection("Data Source=.\SQLExpress;Integrated Security=SSPI;Initial Catalog=master")

try {

    $sqlConnection.Open()

    $commandText = @"
        exec sp_msforeachdb 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
    BEGIN
       drop database [?]
    END'
"@  

    $sqlCommand = New-Object System.Data.SqlClient.SqlCommand
    $sqlCommand.CommandText = $commandText
    $sqlCommand.Connection = $sqlConnection
    $SQLCommand.CommandTimeout = 0
    $sqlCommand.ExecuteNonQuery()
}
finally{
    $sqlConnection.Close()
}

Normally it works, but sometimes it cannot delete databases, since there seem to be some open connections and the build task fails to delete the databases as they are in use.

This also seems to occur at "some point" or "random".

Any advice to enhance the script?

(using lates TFS 2017 on prem and SQL Server 2014)

Answer 1

if found a script here:

Drop all databases from server

-- drops all user databases
DECLARE @command nvarchar(max)
SET @command = ''

SELECT  @command = @command
+ 'ALTER DATABASE [' + [name] + ']  SET single_user with rollback immediate;'+CHAR(13)+CHAR(10)
+ 'DROP DATABASE [' + [name] +'];'+CHAR(13)+CHAR(10)
FROM  [master].[sys].[databases] 
 where [name] not in ( 'master', 'model', 'msdb', 'tempdb');

SELECT @command
EXECUTE sp_executesql @command

it works as intended, still thanks for your help

Answer 2

If you need to cut off all users with no warning, set the database offline before dropping it.

$commandText = @"
 exec sp_msforeachdb 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
    BEGIN
        alter database [?] set offline with rollback immediate;drop database [?];
    END';
"@

Answer 3

Might I suggest using SMO?

push-location;
import-module sqlps -disablenamechecking;
pop-location

$serverName = '.';
$server = new-object microsoft.sqlserver.management.smo.server $servername;
foreach ($db in $server.Databases | where {$_.IsSystemObject -eq $false}) {
   $server.killDatabase($db.Name);
}