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.
-
Why is it trying to look for a log file at a specific path? Why not where the database file is?
-
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.
Best Answer
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 theFOR ATTACH
clause, it explicitly describes that: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: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.