Drop SQL Server database from powershell

By | January 12, 2018
Questions:

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?

Answers:

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

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

Questions:
Answers:

for command above you need:

import-module sqlps

Leave a Reply

Your email address will not be published. Required fields are marked *