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.
- Confirm the current version of the SQL Server. (mine was 9.5.00 SP4)
- Record the Names of the SQL server System Databases as well as its Data and Files.
- 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)
- Make sure all users, applications, and services are totally off the system
- Make backups of ALL databases, Ensure Master, Model and MSDB are also completed
- Uninstall SQL 2005 32-Bit, to make the instance name available as the server name was the default instance.
- Install SQL 2005 64-bit
- Install SQL 2005 64-bit SP4
- Start the SQL server in Single user mode, add -m; to the start-up parameters
- Restore the Master
RESTORE DATABASE master FROM DISK = ‘Z:\SQLServerBackups\master.bak’ WITH REPLACE;
(Server will stop, remove the -m from step 8 and start server)
ALTER DATABASE msdb MODIFY FILE (NAME=data, FILENAME= 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\msdb.mdf');
ALTER DATABASE msdb MODIFY FILE (NAME=LOG, FILENAME= 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\msdblog.ldf');
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.