Today I bought a new machine which is 64 bit and my previous machine was 32 bit. I have some SQL Server databases that were created on a 32 bit machine. When I tried to attach those 32 bit .MDF, .LDF in 64 machine, I get an error message.
This blog shows how to fix this problem.
First of all, I started local system's SQL Server services using this query.
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)
Now put this query.
/*Database name*/
EXEC sp_attach_db @dbname = N'ModelingCorner',
/* .MDF file path */
@filename1 = N'C:\Database\ModelingData\data.mdf',
/* .LDF file path */
@filename2 = N'C:\Database\ModelingData\data_log.ldf';
and executed query still I got this error message.
Unable to open physical file - Operating system error 5: 5(error not found) Microsoft SQL Server: Error 5120
Now finally I followed these following steps.
- Right click on the folder C:\Database\ModelingData and click on properties
- Click on security tab
- Click on Add button and add sql service account
- Provide modify privilege and click ok
- Verify both mdf and ldf have modify privilege
- Attach the db!
That worked !!