Drop SQL database using powershell

PowerShell Help
1

Hi,

I’m trying to check if the database exists, if exists drop the database.

Please see the code below and let me know for any changes…code is working but database is not being dropped.

 

Thanks in advance…

Param

(

$DestServer,

$targetdatabase

)

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

$DataSet = New-Object System.Data.DataSet

 

$SqlConnection.ConnectionString = "Server = $DestServer; Integrated Security = True"

 

$SqlCmd.CommandText = "select name From sys.databases where name = "targetdatabase'"

 

$SqlCmd.Connection = $SqlConnection

$SqlAdapter.SelectCommand = $SqlCmd

$SqlAdapter.Fill($DataSet)|out-null

 

$rowCount_ = $Dataset.Tables[0].Rows.Count

 

if($rowCount_ -eq 1)

{

echo "Database exists $targetdatabase"

#Going to proceed with dropping database.

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

$DataSet = New-Object System.Data.DataSet

$SqlConnection.ConnectionString = "Server = $DestServer; Database = '$targetdatabase'; Integrated Security = True"

 

$SqlCmd.CommandText = "ALTER Database "$targetdatabase" SET Restricted_User WITH ROLLBACK IMMEDIATE

DROP Database "$targetdatabase""

 

#$SqlCmd.CommandText = "Drop Database $targetdatabase"

 

 

#$SqlCmd.Connection = $SqlConnection

#$SqlAdapter.SelectCommand = $SqlCmd

#$SqlAdapter.Fill($DataSet)|out-null

$SqlCmd.ExecuteNonQuery()

echo "Successfully dropped database $targetdatabase"

}

else

{

echo "Database Does not exits"

}
2

I’d use SMO

import-module sqlps
$server = New-Object Microsoft.SqlServer.Management.Smo.Server(“w12scorc”)
$server.Databases
$server.databases[“mydb”]
$server.databases[“mydb”].Drop()
$server.databases

3

Thanks for the response…

yes we can use SMO…but this is just a part of my project…i have to get the database file names, i need to modify the file names…to much coding needs to be done using T SQL…so using the above methodology…

btw can i use SMO in above code ?