Error while Detaching / Attaching a Database

  • paulnamroud

    Old Hand

    Points: 302

    Hello,

    I want to change the Folder/Path of my database. I read about this process over the net and I tried different methods but it keeps returning the same error.

    First, I copied the files ".mdf" and ".ldf" from the old folder E:\Data to the new folder E:\MSSQL\Data. And i make sure that these files are NOT flagged as Read Only.

    Second, I tried manually to do the following actions:

    - Right click and detach the database.

    - Then, I attached the database by seclecting the ".mdf" from the new folder. The Database is Read Only!

    So I tried to run the following script to make the database as Read-Write, but it didn't work!

    USE master;

    GO

    ALTER DATABASE MY_DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

    GO

    ALTER DATABASE MY_DB SET READ_WRITE;

    GO

    ALTER DATABASE MY_DB SET MULTI_USER;

    GO

    It returned the following error messages:

    Msg 5120, Level 16, State 101, Line 1

    Unable to open the physical file "E:\MSSQL\Data\MY_DB.mdf". Operating system error 5: "5(failed to retrieve text for this error. Reason: 15105)".

    Msg 5120, Level 16, State 101, Line 1

    Unable to open the physical file "E:\MSSQL\Data\MY_DB_log.LDF". Operating system error 5: "5(failed to retrieve text for this error. Reason: 15105)".

    Msg 945, Level 14, State 2, Line 1

    Database 'MY_DB' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

    Msg 5069, Level 16, State 1, Line 1

    ALTER DATABASE statement failed.

    Third, I tried to run another script and I got the same error message!

    Script:

    USE master;

    GO

    EXEC sp_detach_db @dbname = N'MY_DB';

    GO

    USE master;

    GO

    CREATE DATABASE MY_DB

    ON (FILENAME = 'E:\MSSQL\Data\MY_DB_Data.mdf'),

    (FILENAME = 'E:\MSSQL\Data\MY_DB_Log.ldf')

    FOR ATTACH;

    GO

    Error:

    [red]Msg 5120, Level 16, State 101, Line 1

    Unable to open the physical file "E:\MSSQL\Data\MY_DB_Data.mdf". Operating system error 2: "2(failed to retrieve text for this error. Reason: 15105)".

    [/red]

    Note:

    - I have more than enough space on my disk (> 100GB) and my database size is around 40 MB.

    - I'm using SQL Server 2008 SP2 Standard Edition (64-bit)

    Can anyone help me ?

    Thank you

    Paul

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    This error can mean 2 things, either the server doesn't have read / write access to that folder. Or the file already exists.

  • paulnamroud

    Old Hand

    Points: 302

    I fixed one error!

    The Database is Read-Only doing Detach/Attach. So when i try to make it Read-Write, it keeps returning the following error:

    Msg 5120, Level 16, State 101, Line 1

    Unable to open the physical file "E:\MSSQL\Data\MY_DB.mdf". Operating system error 5: "5(failed to retrieve text for this error. Reason: 15105)".

    Msg 5120, Level 16, State 101, Line 1

    Unable to open the physical file "E:\MSSQL\Data\MY_DB_log.LDF". Operating system error 5: "5(failed to retrieve text for this error. Reason: 15105)".

    Msg 945, Level 14, State 2, Line 1

    Database 'MY_DB' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

    Msg 5069, Level 16, State 1, Line 1

    ALTER DATABASE statement failed.

    any clue ?

    Thank you

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    If you can reattach to the original server, then it means the db files are fine... and that it's a permission issue.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714080

    To follow on from the Ninja, the SQL Server database engine service account must have permissions to read/write in the new folder. Your account permissions do not matter. When you do this in SSMS, you are asking the SQL Server service account to actually take a lock on the MDF file and open it for read/write.

  • Perry Whittle

    SSC Guru

    Points: 233678

    paulnamroud (3/28/2011)


    First, I copied the files ".mdf" and ".ldf" from the old folder E:\Data to the new folder E:\MSSQL\Data. And i make sure that these files are NOT flagged as Read Only.

    Check the NTFS permissions ACLs on E:\Data and replicate them on E:\MSSQL\Data

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • paulnamroud

    Old Hand

    Points: 302

    Thank you Perry

    I give it a right to the user name SQLServerMSSQLUser$xxx and now It works fine!

  • Perry Whittle

    SSC Guru

    Points: 233678

    paulnamroud (3/28/2011)


    Thank you Perry

    I give it a right to the user name SQLServerMSSQLUser$xxx and now It works fine!

    That looks like one of the local groups created during the SQL server install, no matter though as the service account will have membership of that group.

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

Viewing 8 posts - 1 through 8 (of 8 total)

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