2

I'm trying to attach a .mdf file in SQL Server 2005 and am getting this error:

Unable to open the physical file "C:\server\db\main.mdf", Operating system error 5: "5(error not found)", (Microsoft SQL server, Error: 5120)

What could be causing this error?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
jn025
  • 2,755
  • 6
  • 38
  • 73
  • Is this SQL Server on your local machine, or on a remote one? Are you **sure** the `main.mdf` file is present in that location **on the server machine** (on the **remote** machine, if your SQL Server isn't running on your own PC) – marc_s Jan 04 '14 at 09:20

1 Answers1

0

Run below query on "new query window" and get the service account name.

declare @sqlser varchar(20)
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
@value_name='objectname', @value=@sqlser OUTPUT
PRINT 'Account Starting SQL Server Service:' +convert(varchar(30),@sqlser)

After getting the service-account (S/A) try the below steps to provide full privilege to files.

Step 1: Right click on the folder "" where the mdf and ldf files located and click on properties.

Step 2: Click on security tab.

Step 3: Click on Add button and add sql service account (S/A).

Step 4: Provide "Full control" privilege and click ok.

Step 5: Verify both mdf and ldf have modify privilege.

Step 6: Attach the db!

source

Goto RUN
Type services.msc & press enter
In service control manager find the below service w.r.t to your edition.

For SQL Server 2000

MSSQLSERVER -- for default instance

MSSQL$instancename -- for named instance

For SQL Server 2005 & SQL Server 2008

SQL Server (MSSQLSERVER) -- for default instance

SQL Server (instancename) -- for named instance

Check the "Log On as" for the SQL service and provide modify privilege to that ID in the folder.

Vadzim
  • 24,954
  • 11
  • 143
  • 151
Nagaraj S
  • 13,316
  • 6
  • 32
  • 53
  • the query isn't working - getting this error: RegOpenKeyEx() returned error 2, 'The system cannot find the file specified.' Msg 22001, Level 1, State 1 – jn025 Jan 04 '14 at 04:33
  • declare @sqlser varchar(20) EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SYSTEM\CurrentControlSet\Services\MSSQLSERVER', @value_name='objectname', @value=@sqlser OUTPUT PRINT 'Account Starting SQL Server Service:' +convert(varchar(30),@sqlser) – jn025 Jan 04 '14 at 04:35
  • hm wierd. I tried to attach the mdf by query and am still getting the same error: Unable to open the physical file "C:\MuServer\DB\MuOnline.mdf". Operating system error 5: "5(error not found)". – jn025 Jan 04 '14 at 04:41
  • I was able to run .bak from this folder before but now i cant run .mdf/ldf – jn025 Jan 04 '14 at 04:56
  • wierd, getting this error now: The database 'main' cannot be opened because it is version 661. This server supports version 611 and earlier. A downgrade path is not supported. – jn025 Jan 04 '14 at 05:00
  • trying it with 2008 R2 instead – jn025 Jan 04 '14 at 05:02
  • http://stackoverflow.com/questions/4257684/sql-server-attach-incorrect-version-661 – Nagaraj S Jan 04 '14 at 05:03