Big Data, Data Analytics and Data Science | BI-BIGDATA.com

This article will help you to understand how we can drop existing SQL Server Database with the help of SMO and PowerShell V3.0. If you do not have you sample data base created then follow the steps to create your database with PowerShell V3.0.

#import SQL Server module
Import-Module SQLPS -DisableNameChecking

#your SQL Server Instance Name
$SQLInstanceName = "SQL2012"
$Server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $SQLInstanceName

#provide your database name where you want to change database properties
$DatabaseName = "PSDB"

#create SMO handle to your database
$DBObject = $Server.Databases[$DatabaseName]

#check database exists on server
if ($DBObject)
{
#instead of drop we will use KillDatabase
#KillDatabase drops all active connections before dropping the database.
$Server.KillDatabase($DatabaseName)
}

To drop an SMO server or database object, we can simply invoke the Drop method. But, if you have ever tried dropping a database before, you might have already experienced being blocked by active connections to that database. For this reason, we chose the KillDatabase method, which will kill active connections before dropping the database.

This option is also available in SSMS when you tried to drop a database from Object Explorer. Right-click on a database, the Delete Object window will appear. At the bottom of the window you will find a checkbox called Close existing connections, selection of this check box will fore to kill all active connections before dropping the database.

Hope this helps

Sandip