Update SQL 2005 32-bit to SQL 2008 R2 64-bit

SQL

I recently helped a customer with a complex transition of a SQL server running a very old 32 bit SQL 2005 to 2008 R2 64 bit. There was the requirement to ensure that all server information was maintained during the migration (Name, Security, Jobs etc). This also had to take place on the same box. The following was the procedure that I followed. I’m not providing full details so if your unsure about a step Google it and I’m sure you will find what you need.

Do this in a lab first! Clone the SQL server and test! I cannot stress this enough.

  1. Confirm the current version of the SQL Server. (mine was 9.5.00 SP4)
  2. Record the Names of the SQL server System Databases as well as its Data and Files.
  3. Download, Relevant service packs for the system and have install files ready. (SQL 64 install, SP4 64, SQL 2008 r2, SQL 2008 r2 CU or SPs)
  4. Make sure all users, applications, and services are totally off the system
  5. Make backups of ALL databases, Ensure Master, Model and MSDB are also completed
  6. Uninstall SQL 2005 32-Bit, to make the instance name available as the server name was the default instance.
  7. Install SQL 2005 64-bit
  8. Install SQL 2005 64-bit SP4
  9. Start the SQL server in Single user mode, add -m; to the start-up parameters
  10. Restore the Master
      Enter SQLCMD


      RESTORE DATABASE master FROM DISK = ‘Z:\SQLServerBackups\master.bak’ WITH REPLACE;

      GO
      (Server will stop, remove the -m from step 8 and start server)

  11. Now the tricky part, the previous master database had references to the 32-bit path program files(x86) for the system databases we need to fix this.
  12. Launch SQL service in recovery mode.(SQL\Bin\Sqlserv.exe –f –t3608
  13. Fix the path for mssqlsystemresource, MSDB, and Model Databases as the SQL service will not start looking to the old locations. (NOTE: You need to adjust this path for your system in the data below, and depending on the names you recorded in step one update this command for each database.)
  14. Enter SQL CMD and alter the paths.
      ALTER DATABASE msdb MODIFY FILE (NAME=data, FILENAME= 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\msdb.mdf');

      GO

      ALTER DATABASE msdb MODIFY FILE (NAME=LOG, FILENAME= 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\msdblog.ldf');

      GO

  15. Repeat these commands for the model and mssqlsystemresource databases.
  16. You should now be able to start the SQL server in normal mode. (control-c the cmd window and it will ask to shutdown the SQL server, start the SQL server from services. If you get errors check the event log > applications for errors, they will likely be databases you missed to update the path.)
  17. Attach you user databases
  18. Start the SQL Server 2008 R2 Install and follow the upgrade process.

Congratulations your now running SQL server 2008 R2. From here I applied some service packs and CU’s and completed by testing.

If you need to fall back, uninstall everything and install SQL 2005 32-bit, restore the backups for the master DB original in single user mode and then do the others, followed by attaching the databases.

One comment

Leave a comment