SQL Server Detach database

  • 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

  • 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

  • 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

  • You need to use $false in place of FALSE.

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • Thanks Gary. I always forget Powershell is special 🙂

    - Tony Sweet

  • Thanks you so much! That did it.

  • You are most welcome. (Of course, Tony did the heavy lifting!!!)

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply