Upgrade SQL Server 32 bit to 64 bit

This blog shows how to upgrade a SQL Server 32 bit database to a 64-bit database.

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.

1. Right click on the folder C:\Database\ModelingData and click on properties
2. Click on security tab
3. Click on Add button and add sql service account
4. Provide modify privilege and click ok
5. Verify both mdf and ldf have modify privilege
6. Attach the db!

That worked !!