Migrating SQL servers

December 29, 2010July 30, 2019 Ed Goad

In the process of our SCOM rollout we used a temporary server for our SQL databases. Finally the new hardware came in, but now we have to migrate the databases with minimal downtime. This is compounded by the fact that the new server is 64bit OS and SQL, where the original was 32bit. For posterity, this is what I did to migrate the server to new hardware:

Bring up the new server with 64bit OS, named as server1a (instead of server1)
Setup the drives in the standard partitioning scheme (C: OS, D: SQL install, F: SQL data, G: SQL backup, L: SQL logs, T: SQL Temp)
Install SQL 2005 64bit on D:
Move the temp database to T:
Copy the SCOM databases from the old server to the new
Shutdown the original server1
Rename Windows from server1a to server1
Rename SQL from server1a to server1

Moving the tempDB

Using the informaiton from the article at http://www.databasejournal.com/features/mssql/article.php/3379901 I executed the following SQL script

use master
go
Alter database tempdb modify file (name = tempdev, filename = ‘t:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAtempdb.mdf’)
go
Alter database tempdb modify file (name = templog, filename = ‘t:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAtemplog.ldf’)
Go

I then stopped SQL and copied the tempdb.mdf and templog.mdf from their default locations to the new location on T.

Copying the databases

Stop SQL on the originating server
Use robocopy to copy the data and log files to the new server (robocopy \server1f$ f: /mir /r:0 /w:0)
In the new server, attach the databases

Renaming the server

Once the system was up and running with the databases attached, I then ran the following TSQL to rename the SQL instance to the new server

Finally I renamed the windows server (powering off the original server first) and rebooted. Voila, everything works!