Drop SQL Server database from powershell

Question

I have a SQL Server instance on my local computer called .\SC. I want to drop a database from that instance using a PowerShell script. I need to login with the sa user for my database.

This is the code I have so far, but it doesn't work:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$srv = new-object Microsoft.SqlServer.Management.Smo.Server(".\SC")
$conContext = $srv.ConnectionContext
$conContext.LoginSecure = $FALSE
$conContext.Login = "sa"
$conContext.Password = "MyPlainTextPass"
$srv2 = new-object Microsoft.SqlServer.Management.Smo.Server($conContext)
$srv2.Databases

That last line is supposed to list the databases in my SQL instance... but it gives me this error:

The following exception occurred while trying to enumerate the collection: "Failed to connect to server .\SC.". At line:1 char:1 + $srv2.Databases + ~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], ExtendedTypeSystemException + FullyQualifiedErrorId : ExceptionInGetEnumerator

What am I doing wrong?

Answer 1

The only way to make it work for me was to force any other connections to the database to close, through the following command:

Invoke-SqlCmd -ServerInstance $Server @Auth `
    -Query "IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name ='MyDBName') `
                BEGIN `
                    ALTER DATABASE [MyDBName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; `
                    DROP DATABASE [MyDBName]; `
                END;" `
    -Verbose

The backticks (`) are necessary, otherwise this command would have to be inlined

Answer 2

I found a different command to do this. It was simply:

invoke-sqlcmd -ServerInstance ".\SC" -U "sa" -P "MyPlainTextPass" -Query "Drop database MyDatabase;"