Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 5 Current »

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:

      USE master
GO
RESTORE DATABASE [msdb]
FROM DISK = N'C:\backups\msdb.bak'
WITH REPLACE
GO
    9. msdb should automatically go back to "Restrict Access" option value of MULTI_USER when restored.
    10. Start SQL Agent using services.msc
  14. 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
  15. Verify SQL jobs (eg: backups) are present and enabled.
NOTE: the folders used for databases and backups will return to be the x86 folder because that information is all stored in master / msdb, so C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.<instance name>\MSSQL needs to be kept - don't delete this! It would be a good idea to move everything out of the x86 folder at some point to avoid confusion and possible mistakes in the future. Be aware, however some SQL Jobs may refer to physical file paths (such as backup jobs) and may need to be modified.



  • No labels