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)

alroc · Answer 1 · 2017-05-22T17:49:21.190

1

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';
"@

edited May 22 '17 at 17:49

answered May 22 '17 at 13:02

alroc

27,574
6
51
97

thanks for the help, but unfortenatly if always fails with: (`Exception calling "ExecuteNonQuery" with "0" argument(s): "Option 'OFFLINE' cannot be set in database 'master'. Option 'OFFLINE' cannot be set in database 'tempdb'. Option 'OFFLINE' cannot be set in database 'model'. Option 'OFFLINE' cannot be set in database 'msdb'." At line:18 char:5 + $sqlCommand.ExecuteNonQuery() + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : SqlException`) – May 22 '17 at 17:01
Are you running this with the `if` statement wrapped around it? I'll amend my post to show the full `$commandtext` – alroc May 22 '17 at 17:48
ok i tested several Scenarios. It works somehow, it deletes the databases out of the management studio, but the files are still there phisically in the DATA folder, altough the error shows up. I think the SQL service still looks into any database. tried different approaches. – May 23 '17 at 09:36

score 0 · Accepted Answer · answered May 23 '17 at 10:00

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

score 0 · Answer 3 · answered May 24 '17 at 05:12

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);
}

How to delete SQL Server databases trough vNEXT build task properly

3 Answers3