asked on

Can't Attach SQL mdf After Moving It

I created a database here:
C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA
I detach it, then copy the .mdf and .ldf here: C:\FRCSW
Now when I try to attach the mdf at the new location, I get
"An error occurred when attaching the database(s). Click the hyperlink in the Message column for details". Then "Unable to open the physical file 'C:\FRCSW\FRCSW_log.ldf.' Operating system error 5: "5(Access is denied)". (Microsoft SQL Server, Error: 5120).

I'm not an advanced SQL guy. I can create the relational tables, write stored procedures, and develope them OK, but I can't seem to deploy them. I always get stuck on the security and permissions issues.

Thanks in advance

tigin44

you should modify the current file path of the database files while attaching them. Do you try to attach the db from management studio or by using commands?

Raja Jegan R

>> Unable to open the physical file 'C:\FRCSW\FRCSW_log.ldf.' Operating system error 5: "5(Access is denied)

To fix this, just try the steps below:

1. Right click your C:\FRCSW folder
2. Choose Properties and then Permissions..
3. You would be having some builtin SQL Server user groups named SQLServerMSSQLUser$ComputerName$MSSQLSERVER or SQLServerMSSQLUser$ComputerName$InstanceName which would be there along with SQL Server installation.
4. Just add these user groups to C:\FRCSW folder and grant full permissions to that folder for this user group.

This should help you fix it..

LarryZ

ASKER

I use Microsoft SQL Server Management Studio (Express). Right click 'Databases' in Object Explorer, choose 'Attach'. Then in the 'Attach Databases' dialog box, I click 'Add', then in the 'Locate Database Files' dialog box, I point to C:\FRCSW\FRCSW.mdf. I get the error when I click 'OK'

tigin44

so you should check the permissions on the folder.. follow the steps rrjegan17 described on his post

LarryZ

ASKER

I did what rrjegan17 suggested, and gave the new user Full Control. No luck. I then retarted SQLEXPRESS. Still no luck.

LarryZ

ASKER

If I look at the Properties for the file FRCSW_log.ldf, in the Security tab, I see that SQLServerMSSQLUser$mycomputernamehere$SQLEXPRESS appears in the Group or user name box, and it has Full Control

LarryZ

ASKER

Running Win 7 64-bit, if it matters

tigin44

do you have any other copy of the mdf and ldf files? If so pleace replace the files and try to attach the new files...

LarryZ

ASKER

No other copies of the mdf and ldf. But I can successfuly re-attach the database if I point to the original C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA location.

tigin44

you should modify the Current File Path sections to locate the new folder... do you do that?
attach.jpg

LarryZ

ASKER

Yes, the path for FRCSW.mdf is C:\FRCSW\FRCSW.mdf and the path for FRCSW_log.ldf is C:\FRCSW\FRCSW_log.ldf.

ASKER CERTIFIED SOLUTION

tigin44

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aneesh

it should be a Permission error... try login as "administrator" and attaching that... surely it is missing the permission to access the folder / file

LarryZ

ASKER

Thank you. I closed Microsoft SQL Server Management Studio, then right-clicked, selected "Run As Administrator", and I can easily attach the databse now. That worked. I'm a bit confused though. I am logged in to Windows 7 as a user with full administrator rights. And the instance of SQL I am connected to looks like this:
MyComputerName\SQLEXPRESS (SQL Server 10.0.2531 - MyComputerName\MyLoginUsername
The owner of the database is MyComputerName\MyLoginUsername
I need to find a SQL for Dummies book or something that can help me understand the deployment and management of MS SQL.

LarryZ

ASKER

sorry aneeshattingal. Your comment was not yet there when I began accepting tiggin44