Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    4

    Unanswered: Detach database greyed out

    I'm trying to detach a database and reattach to another server. When trying to detach it using Enterprise Manager, the command is greyed out. I've tried taking the database offline first but the command is still not enabled. I'm logged in as sysadmin. What gives?

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Why not use SP_DETACHDB From query analyzer?
    And its better to use such admin. statements using QA than depending upon GUI>
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    Is it one on the default dbs you are trying to detach? If it is, I don't think you can! There are ways around it though(copy the data and log files to where you want the new db), not supported though I suspect.
    Regards
    Dbabren

  4. #4
    Join Date
    Feb 2004
    Posts
    4
    Originally posted by Satya
    Why not use SP_DETACHDB From query analyzer?
    And its better to use such admin. statements using QA than depending upon GUI>
    Satya,
    I've tried this (SQL 2000) and I get an error message:

    Database is in use. I can't figure out how to get it "out of use" ???

    The actual database is on SQL 7 though...

    ..no, it's not one of the system databases but thanks for the tip!
    Last edited by SpeedAdkt; 02-27-04 at 11:38.

  5. #5
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Then check the following :
    use master
    go
    sp_who
    ... and check what process are using this database.
    ANd kill those connections (SPID) using KILL statement and also keep the database in DBO use only just in case if any new connection/user will try to access.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  6. #6
    Join Date
    Feb 2004
    Posts
    4
    Originally posted by Satya
    Then check the following :
    use master
    go
    sp_who
    ... and check what process are using this database.
    ANd kill those connections (SPID) using KILL statement and also keep the database in DBO use only just in case if any new connection/user will try to access.

    DBO use only...you mean single user mode? SQL 7 doesn't support dbo only or, at least I can't find how to do that.

  7. #7
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Yes I was referring about DBO Use only.
    You can do it from Enterprise Manager select the database properties and goto options tab to set it.
    From Query analyzer you can set it using SP_DBOPTION, refer to books online for syntax and information.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  8. #8
    Join Date
    Feb 2004
    Posts
    4
    Originally posted by Satya
    Yes I was referring about DBO Use only.
    You can do it from Enterprise Manager select the database properties and goto options tab to set it.
    From Query analyzer you can set it using SP_DBOPTION, refer to books online for syntax and information.
    OK, the database is set to DBO use only. I'm working with the 2000 version of the database since the attach/detach doesn't seem to be supported at all in 7. Anyway, even with the DBO use only set, the cmd is still greyed out.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •