Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server Detach database Expand / Collapse
Author
Message
Posted Tuesday, April 22, 2014 9:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 4, 2014 1:52 AM
Points: 26, Visits: 183
Hi

I am a SQL Server DBA and have never used Powershell before. I'm trying to detach a database using Powershell, but not getting very far.
Have read up a bit and most ppl do it like below. I tried to run this, but getting an error that I'm not finding a solution for, so I'm pretty sure I'm doing something very wrong or possibly not doing something that I need to add to the script. Below is what I'm running and the error I'm getting.
Please if anyone has any suggestions, please let me know.


PS SQLSERVER:\> [void] [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
PS SQLSERVER:\> $server = new-object Microsoft.SqlServer.Management.Smo.Server('SERVERNAME')
PS SQLSERVER:\> $server.DetachDatabase("databasename")
Cannot find an overload for "DetachDatabase" and the argument count: "1".
At line:1 char:1
+ $server.DetachDatabase("databasename")
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodException
+ FullyQualifiedErrorId : MethodCountCouldNotFindBest


Thanks!
TDP
Post #1563913
Posted Tuesday, April 22, 2014 9:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 6:38 AM
Points: 33, Visits: 65
Looks like you are missing a parameter in the DetachDatabase syntax.

See this: http://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.server.detachdatabase.aspx

You need to add a Boolean for the update statistics parameter. Assuming you don't want to do this, it would look like

$server.DetachDatabase("databasename", FALSE)



- Tony Sweet
Post #1563932
Posted Tuesday, April 22, 2014 9:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 4, 2014 1:52 AM
Points: 26, Visits: 183
Thanks
Tried that, but got the below error:


PS SQLSERVER:\> $server.DetachDatabase("dbPSXTest", FALSE)
At line:1 char:36
+ $server.DetachDatabase("dbPSXTest", FALSE)
+ ~
Missing expression after ','.
At line:1 char:37
+ $server.DetachDatabase("dbPSXTest", FALSE)
+ ~~~~~
Unexpected token 'FALSE' in expression or statement.
At line:1 char:42
+ $server.DetachDatabase("dbPSXTest", FALSE)
+ ~
Unexpected token ')' in expression or statement.
+ CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : MissingExpressionAfterToken

TDP
Post #1563937
Posted Tuesday, April 22, 2014 10:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:27 AM
Points: 5,420, Visits: 3,151
You need to use $false in place of FALSE.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
Post #1563943
Posted Tuesday, April 22, 2014 10:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 6:38 AM
Points: 33, Visits: 65
Thanks Gary. I always forget Powershell is special :)

- Tony Sweet
Post #1563970
Posted Wednesday, April 23, 2014 12:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 4, 2014 1:52 AM
Points: 26, Visits: 183
Thanks you so much! That did it.
Post #1564111
Posted Wednesday, April 23, 2014 1:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:27 AM
Points: 5,420, Visits: 3,151
You are most welcome. (Of course, Tony did the heavy lifting!!!)

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
Post #1564121
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse