PowerShell 2 and SQL 2008 RT - Dropping a Database

I have a database stored locally on some computers. It's a 2008 R2 SQL database, and all of the machines are 32  bit Win 7 with PowerShell 2.0

I want to delete the database. I don't care about the data in it (it will be rebuilt by another script that I know is working). This is the code so far:
Add-Type -Path 'C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll'

$srv = new-Object Microsoft.SqlServer.Management.Smo.Server("(local)")
$db = New-Object Microsoft.SqlServer.Management.Smo.Database($srv, "Database")

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $srv; Database = $db; Integrated Security = False; UID=sa; Password = Password" 

$srv.KillAllProcesses("Database")

$db.Create()
Write-Host $db.CreateDate
$db.Drop()

Open in new window


I know there are several problems with the code, but the first one I'm trying to tackle is the KillAllProcesses. This is the error I get when executing it:


PS C:\> $error[0]|format-list -force


Exception             : System.Management.Automation.MethodInvocationException: Exception calling "KillAllProcesses" with "1" argument(s): "Drop all active database connections failed for Server 'Computername'. " ---> Microsoft.SqlServer.Management.Smo.Faile
                        dOperationException: Drop all active database connections failed for Server 'Computername'.  ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch
                        . ---> System.Data.SqlClient.SqlException: The user does not have permission to perform this action.
                           at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
                           at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand)
                           --- End of inner exception stack trace ---
                           at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand)
                           at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteWithResults(String query)
                           at Microsoft.SqlServer.Management.Smo.Server.KillAllProcesses(String databaseName)
                           --- End of inner exception stack trace ---
                           at Microsoft.SqlServer.Management.Smo.Server.KillAllProcesses(String databaseName)
                           at KillAllProcesses(Object , Object[] )
                           at System.Management.Automation.MethodInformation.Invoke(Object target, Object[] arguments)
                           at System.Management.Automation.DotNetAdapter.AuxiliaryMethodInvoke(Object target, Object[] arguments, MethodInformation methodInformation, Object[] originalArguments)
                           --- End of inner exception stack trace ---
                           at System.Management.Automation.DotNetAdapter.AuxiliaryMethodInvoke(Object target, Object[] arguments, MethodInformation methodInformation, Object[] originalArguments)
                           at System.Management.Automation.ParserOps.CallMethod(Token token, Object target, String methodName, Object[] paramArray, Boolean callStatic, Object valueToSet)
                           at System.Management.Automation.MethodCallNode.InvokeMethod(Object target, Object[] arguments, Object value)
                           at System.Management.Automation.MethodCallNode.Execute(Array input, Pipe outputPipe, ExecutionContext context)
                           at System.Management.Automation.ParseTreeNode.Execute(Array input, Pipe outputPipe, ArrayList& resultList, ExecutionContext context)
                           at System.Management.Automation.StatementListNode.ExecuteStatement(ParseTreeNode statement, Array input, Pipe outputPipe, ArrayList& resultList, ExecutionContext context)
TargetObject          :
CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
FullyQualifiedErrorId : DotNetMethodException
ErrorDetails          :
InvocationInfo        : System.Management.Automation.InvocationInfo
PipelineIterationInfo : {}
PSMessageDetails      :


Any help would be amazing. I'm very new to PowerShell. Also, this script will potentially run on 2,000 computers, so upgrading the SQL database, or PowerShell, isn't a viable option at this point.
darantaresAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kanti PrasadCommented:
Hi

Check that that user has admin permissions and in your above code replace  

$srv.KillAllProcesses("Database") with

$srv.KillDatabase("Database")
0
darantaresAuthor Commented:
I'm running PowerShell in Administrative mode, and I have domain access to the network. I can make myself a local admin if necessary, but this script will have to run for any users (it will eventually run via a Scheduled Task)

After changing to $srv.KillDatabase("Database") this is what I get:


System.Management.Automation.MethodInvocationException: Exception calling "KillDatabase" with "1" argument(s): "Kill database failed for Server 'Computername'. " ---> Microsoft.SqlServer.Management.Smo.FailedOperationException: Kill d
                        atabase failed for Server 'Computername'.  ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: User do
                        es not have permission to alter database 'Database', the database does not exist, or the database is not in a state that allows access checks.
0
Kanti PrasadCommented:
Hi

try this

invoke-sqlcmd -ServerInstance "(local)" -U "sa" -P "Password" -Query "Drop database Database;"
0
Hey MSSPs! What's your total cost of ownership?

WEBINAR: Managed security service providers often deploy & manage products from a variety of solution vendors. But is this really the best approach when it comes to saving time AND money? Join us on Aug. 15th to learn how you can improve your total cost of ownership today!

darantaresAuthor Commented:
So I tried this:

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

invoke-sqlcmd -ServerInstance "(local)" -U "sa" -P "Password" -Query "Drop database Database;"

Open in new window


And I get this error:


Invoke-Sqlcmd : Cannot drop the database 'Database', because it does not exist or you do not have permission.
0
Deepak ChauhanSQL Server DBACommented:
Error message saying you dont have permissions to delete this database.

You should have sysadmin permission to drop a database.
0
darantaresAuthor Commented:
The sa account should have that permission, no? If I log into SQL Server Management Studio with the SA account I can right click on the database and delete it just fine.

Am I not authenticating into the database correctly? Is there a way I can check that with the script?
0
Deepak ChauhanSQL Server DBACommented:
Yes, SA account has full access in sql server.

Did you changed the database name while trying the command in query parameter.

"Drop database <databaseName>"

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

invoke-sqlcmd -ServerInstance "(local)" -U "sa" -P "Password" -Query "Drop database Database;"
0
darantaresAuthor Commented:
So the above worked on one database (Thank you!). I duplicated the line invoke-sqlcmd -ServerInstance "(local)" -U "sa" -P "Password" -Query "Drop database Database; and put a new database name, but now I'm getting an error:

Invoke-Sqlcmd : Cannot drop database "Database2" because it is currently in use.

How can I force close that database?
0
Kanti PrasadCommented:
Hi

If there are some active \ pool of connections to the database you cannot delete it. Try to set it to single user and then run the command
0
darantaresAuthor Commented:
I figured out there weren't any active connections, but if I do the following, and then run the PowerShell script to drop the database, it works
 
use master
go
alter database Database
set offline with rollback immediate

Open in new window


What I'm trying to do is get PowerShell to send those commands to SQL prior to dropping the database. I've tried this:
 
invoke-sqlcmd -ServerInstance "(local)" -Database "Database" -U "sa" -P "Password" -Query "use master;" go; alter database Database; set offline with rollback immediate;

Open in new window


But it's not functioning properly. What would be the correct way to send those 4 SQL lines via PowerShell?
0
Deepak ChauhanSQL Server DBACommented:
In this case here is your command. Change the <DatabaseName>

invoke-sqlcmd -ServerInstance "(local)" -Database "master" -U "sa" -P "Password" -Query "alter database <DatabaseName> set offline with rollback immediate; DROP DATABASE <DatabaseName>"

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.