Post reply

cannot attach database

  • February 8, 2012 at 5:25 pm

    #250450

    I detached some SQL server databases on a SQL 2005.

    Then I later tried to reattach it using management Studio, when I browse to the directory I can only see those mdf files for online databases, those detached ones are not showing.

    But it exists in the same path. I can browe using windows explorer, but not in ssms.

    I have sysadmin server role. How to fix?

    Thanks

  • February 8, 2012 at 7:05 pm

    #1444853

    Try this. see if you gets any error then post it

    USE [master]

    GO

    CREATE DATABASE [AdventureWorks] ON

    ( FILENAME = N’C:\Data\AdventureWorks_Data.mdf’ ),

    ( FILENAME = N’C:\Data\AdventureWorks_Log.ldf’ )

    FOR ATTACH

    GO

    java

  • February 9, 2012 at 1:52 am

    #1444927

    Check the file permissions in NTFS. Your windows account likely does not have permission to see them (and browsing via SSMS does not use the SQL Server account, it's using yours)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 9, 2012 at 10:04 am

    #1445180

    But I can browse from SSMS to see those online databases .mdf files, but just cannot see those detached databases' .mdf files.

    And I can see all of them are there by using windows explorer. And I also the administrator of the box.

  • February 9, 2012 at 10:29 am

    #1445192

    Check the *file* permissions and ownership, not the folder. The file permissions would have been changed when the database was detached, as documented http://msdn.microsoft.com/en-us/library/ms189128.aspx

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 9, 2012 at 10:44 am

    #1445197

    I've had a similar problem, that was solved by logging in as sa, not a user with sysadmin rights, but actually sa..

    I also remember that this solution helped another poster here on SSC..

    Very strange.

    CEWII

  • February 9, 2012 at 11:10 am

    #1445209

    GilaMonster (2/9/2012)

    Check the *file* permissions and ownership, not the folder. The file permissions would have been changed when the database was detached, as documented http://msdn.microsoft.com/en-us/library/ms189128.aspx

    Thanks all,

    Yes, this articel would be very useful, but a quick glance, I still quite understand it, I will spend more time to look into it.

    You are right, it changed the file permission when the database detached. but only those .mdf files that detached.

    Even though it shows local administrator has permission to the folder, also the file itself but I still cannot browse the file from SSMS, but after I tried to explicitly to add my login to the folder, all the files shown up. I can see now from SSMS.

    I will do the same thing to the log folder, so that I can attach databases.

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

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