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?

Have you checked your SQL logs for failed login attempts? Failing that, store `$error[0]` into a local variable right after running this and get the Exception and InnerException until you reach the root cause. — Ben Thul, Jun 10 '14 at 21:07

score 22 · Accepted Answer · answered Jun 11 '14 at 19:55

22

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

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

answered Jun 11 '14 at 19:55

user952342

2,602
7
34
54

2

this would fail if database not exists – liang Aug 15 '16 at 10:52
4

@liang This would also fail if server doesn't exists, or the user doesn't exist, or if the password isn't correct, if there's no SQL Server on the server, if it is run against SQL Server v1.0, or if it would be executed on a machine without powershell, or executed on a commodore 64... The point of his answer is about fixing the original problem, not about the decoration around the problem. – P-L Sep 09 '20 at 15:10

score 6 · Answer 2 · answered Apr 20 '20 at 18:02

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

Thanks - if using Windows authentication, you can remove `@Auth` from the above. — robyaw, Jun 26 '20 at 11:20

score 1 · Answer 3 · edited Mar 19 '19 at 13:22

1

I use the following:

$sqlserver = '<enter sql server here>'
$database = '<enter db name here>'

Try{
    invoke-sqlcmd -ServerInstance "$sqlserver" -Query "Drop database $database;"
}Catch{
      Write-Output 'Failed to delete database'
}

That way you can turn it in to a function if needed, with variables, and if it fails it tells you but moves on with the rest of the script it is in.

edited Mar 19 '19 at 13:22

Dwhitz

1,250
7
26
38

answered Mar 19 '19 at 12:53

Adrian Coles

11
1

score 0 · Answer 4 · edited Apr 11 '18 at 13:36

With all the preparation you have, you seem to forget to actually connect to the server. Note that the below example calls ConnectionContext.Connect().

$dbServer = New-Object Microsoft.SqlServer.Management.Smo.Server $server
$smoSecurePassword = $Password  | ConvertTo-SecureString -asPlainText -Force
$dbServer.ConnectionContext.LoginSecure = $false
$dbServer.ConnectionContext.set_Login($User)            
$dbServer.ConnectionContext.set_SecurePassword($smoSecurePassword)
$dbServer.ConnectionContext.Connect() 
$db = $dbServer.Databases

If you want to check whether your database exists (like liang pointed out), you can change the last line with:

$db = $dbServer.Databases[$database]

and verify whether $db has a value.

Drop SQL Server database from powershell

4 Answers4