How do I use Powershell to detach a SQL Server database?

Question

I am sure that this can be achieved by loading the SQL Server Management Objects assemblies but I am curious as to whether the 'Start Powershell' item in the context menu within SQL Server Management Studio 2008 will give me a headstart.

Chad Miller · Accepted Answer · 2009-12-29 18:02:45Z

1

The SMO Server class has a DetachDatabase method that you can use from Start PowerShell. From the server level in sqlps you can use something like this:

PS SQLSERVER:\SQL\Z002>get-childitem | foreach{$_.DetachDatabase("pubs",$false)}

See the MSDN docs for detailed info on DetachDatabase:

answered Dec 29, 2009 at 18:02

Chad Miller

42.4k4 gold badges33 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Scott Munro Over a year ago

Your answer tells me that the SQL Server is implemented as a PSDrive which pretty much answers my question. I would however be interested in understanding why you needed a 'foreach' here.

Chad Miller Over a year ago

Get-childitem when run at the machine level will return all the SQL instances on the server. If you have multiple instances foreach would be neeed. Probably doesn't make sense in this case though. But I have multiple on the machine I ran command on. If just one instance you could either assign the Server object to a variable or do something like this: (get-childitem ).DetachDatabase("pubs",$false) OR assign server object to variable $server = get-childitem $server.DetachDatabase("pubs",$false)

Collectives™ on Stack Overflow

How do I use Powershell to detach a SQL Server database?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related