How to migrate a SQL 2012 x86 instance to x64

If the 32 bit (x86) version of SQL Server has been installed on a Windows x64 system, and you want to migrate the entire SQL Server up to the x64 edition, follow these steps.

All databases, and logins, passwords, and scheduled jobs are preserved.

Step-by-step guide

1. Disable SQL Agent service to prevent backups and other scheduled jobs from interrupting

2. Shrink databases + log

   Shrinking is optional, but reducing database sizes will make backups smaller and faster. Do this for each database of significant size or interest.

   DBCC SHRINKFILE (N'logicalfilenamefordata', 100) GO DBCC SHRINKFILE (N'logicalfilenameforlog', 100) GO

   
   
   

3. Backup all non-system databases

4. Backup msdb

5. Backup master

6. Backup model

7. Stash backups away for safety - eg: into a c:\backups folder

8. Stop SQL Services via services.msc

9. Uninstall SQL Server and associated components - Take note of installed features (so when install of x64 is done, we match features - eg: reporting services, et al). Note the databases in C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.<instance name>\MSSQL will stay where they are - the new x64 SQL will still use these.

10. Reboot

11. Install SQL x64 - Make sure to set mixed mode authentication and provide the sa password. Also make sure you match the features from step 9.

12. Reboot

13. Restore system databases

    1. Put server into single user mode

       1. Stop SQL server agent

       2. Stop the SQL Service using services.msc

       3. Using an elevated command prompt, start in single user mode 

          CD C:\Program Files\Microsoft SQL Server\MSSQL11.<instance name>\MSSQL\Binn sqlservr.exe -m -s<instance name>

          
          
          

    2. Restore Master database from backup by running SQLCMD.exe from an elevated command prompt

       CD C:\Program Files\Microsoft SQL Server\110\Tools\Binn SQLCMD.EXE -S .\<instance name> -U sa -P <sa password> 1>RESTORE DATABASE master FROM DISK = 'C:\backups\master.bak' WITH REPLACE 2>GO

       
       
       

    3. Copy model database + log (model.mdf and modellog.ldf) from new x64 folder back  to old x86 folder - ie: 
       FROM 
       C:\Program Files\Microsoft SQL Server\MSSQL11.<instance name>\MSSQL\DATA 
       TO 
       C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.<instance name>\MSSQL\DATA 

    4. Copy MSDB database + log (MSDBData.mdf and MSDBLog.ldf)  from x64 back to old x86 folder - ie: 
       FROM 
       C:\Program Files\Microsoft SQL Server\MSSQL11.<instance name>\MSSQL\DATA 
       TO 
       C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.<instance name>\MSSQL\DATA

    5. Start SQL Service from services.msc, make sure SQL Agent stopped

    6. Login using Management Studio

    7. Right click msdb, choose properties - Options and change "Restrict Access" option to "SINGLE_USER"

    8. Restore msdb using new query in management studio:

       
       
       

    9. msdb should automatically go back to "Restrict Access" option value of MULTI_USER when restored.

    10. Start SQL Agent using services.msc

14. Rebuild indexes and update statistics for all non system databases - see Improve database performance

15. Verify applications
    
    

    1. Should be able to login to jiwa databases using the Jiwa application with no issues

    2. Other applications using other database should also work

16. Verify SQL jobs (eg: backups) are present and enabled.

Related articles