7

I have received a database file and the instructions for loading it is to install SQL Server 2005 and then attach it using SQL Server Management Studio.

After installing everything, I tried to attach the MDF file but then it tells me:

the directory lookup for the file "D:{folderName}{filename}.LDF" failed with the operating system error 21 (error not found)

An LDF file did not come with the database, so presumably it should be generated automatically.

Now, D drive is where my CD drive is, so it's not going to find anything there. Nor is it going to have any luck trying to create anything there.

  1. Why is it trying to look for a log file at a specific path? Why not where the database file is?

  2. How can I attach this database?

I realized that when I select a database to attach, three entries appear under "database details". an MDF, NDF, and LDF. The LDF's "current file path" points to the D drive path above, so I removed it.

This time, when I hit "OK", I get a different error message:

Database cannot be upgraded because it is read-only or has read-only files.
Make the database or files writeable, and rerun recovery.

File activation Failure. The physical name D:{folder}{file}.LDF may be incorrect.
New log file "..." was created. (Microsoft SQL Server, Error: 3415)

So now it creates a new log file in the same folder as the database file, which is great, but it seems like there are security issues.

Additional information:

  • The instructions require me to use the login name "sa", which appears to be the sysadmin account. I am connected to my SQL Server instance using that login.

  • I have checked the file properties that it is not read-only. The directory is not read-only either. All ACLs are allowed.

  • I am unable to attach the database. When I try to attach it, it throws an error message with "attach database failed".

  • Closing SSMS and reopening it as an Administrator made no difference.

  • select SERVERPROPERTY('ProductVersion') returns 9.00.4035.00. There's another file that comes with the database called "dbdata.ini" which says "IsSql2000=1" so presumably it is meant to be loaded in SQL Server 2000. I'll see if I can get it working on 2000.

8

This isn't an access issue. The problem is that you are attaching the database to a higher version of SQL Server than the instance the .mdf was originally attached to and it's read only. SQL Server is trying to upgrade the database as part of the attach and can not because the db is in read-only mode.

If your database is in READ_ONLY (which sounds like it is the case based on your error messages), then you need all files (both log and data) to attach it per the Technet documentation. If you look under the FOR ATTACH clause, it explicitly describes that:

...for a read-only database, the log cannot be rebuilt because the primary file cannot be updated. Therefore, when you attach a read-only database whose log is unavailable, you must provide the log files or files in the FOR ATTACH clause.

You need the accompanying .ldf for the database. I would contact whoever provided the database and ask them to provide both the .mdf and .ldf files. Then, you can attach it with the following syntax:

CREATE DATABASE [foo]
ON (FILENAME='<<path to mdf>>')
LOG ON (FILENAME='<<path to ldf>>')
FOR ATTACH;

The READ_ONLY status cannot be removed from the database without attaching it because it is stored in the metadata of the database. To alter it, the database needs to be online and attached.

Another alternative is to ask for a backup of the database. You can work around some of these issues more easily if you're doing a database restore instead of an attach.

  • 1
    Using LOG ON with the matching ldf did not resolve the issue for me – Mugen Aug 13 '17 at 7:08
21

I just found a post that answered this question:

This may not work in all cases, but I was trying to install the Semantic Search Database and got the same error.

You have to run SQL Server Management Studio as an administrator, and it works.

8
  1. Open up my computer.
  2. Locate the main folder where you have kept the ".mdf" and ".ldf" file.
  3. Right click on the folder click on "Properties".
  4. In the properties window click on "Security"
  5. Click on "Edit" button
  6. Enable "Read and execute", "Read", "Write" for the listed users and apply the changes to the following folders till the database files.
  7. Try to attach the ".mdf" file
  • thanks ,my old windows gone and i think these files locked by that OS. but just need to change permission – saber tabatabaee yazdi Dec 28 '18 at 18:24
1

Right click the .mdf and .ldf files, one at a time and click 'properties', then at the bottom where it says ' this file came from another computer and might be blocked to help protect this computer ' click unblock button.

Then right click, -> properties, -> security tab -> edit button and select 'users' and give full control permission.

1

In my case the resolution was to login to SSMS with windows Auth mode instead of the sql user that has sysadmin role. just make sure the user that you used in windows auth mode have a proper permission(my case was sysadmin) My guess is sql user doesn't have a proper permission.(I encounter this issue when I migrate/copied mdf and ldf from different machine)

0

Not permissions in our case, it was the version of SSMS !

(it was 2012 locally where we got the error, but worked when we used SSMS 17+ from a remote box)

-1

I have also had this issue come up. I assisted my customer by restarting the SQL service, and was able to attach the MDF per normal procedure.

-2

1) Open Sql Server Configuration Manager

2) Select Sql Server Services(Left pane)

3) Doubleclick your SqlServer(right Pane)

4) Select Log On tab

5) Select built-in radio button and change local system or network service in combobox.

6) Click Ok.

7) Attach mdf file in sql server manager.

-2

Your SQL Server service is probably running under NT Service\MSSQLServer account. Changing it Local System will give it access.

Probably Best to create a user and give that user full permissions to the folder the files are in.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.