How to move from SQL 2005 32bit to SQL 2005 64bit?
-
I currently have SQL 2005 Standard Edition 32bit running on Windows Server 2003. I want to move all the SQL stuff (databases, maintenance plans, jobs, users, DTS packages, …) to a new server which will be SQL 2005 Standard Edition 64bit running on Windows Server 2008.
SQL 2003 64bit data files are in a different location on the file system than the 32bit version. The user databases will be put back in the same location on the filesystem.
Can this be so easy as to copy the all the databases in “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data” to the new location? Do I need to copy tempdb and model?
If not, what is the procedure for moving my setup? I have not found anything in TechNet on how to accomplish this task.
-
Hi,
Tempdb - No. It gets re-created when you start the SQL Server service anyways.
Model - You only need to move this database if you have made any customization to the one in the 32bit environment.
-
Don't forget to move your logins, jobs, alerts and operators. You can do these either through scripts or using SSIS.
[font="Verdana"]Markus Bohse[/font]
-
for your user databases, yes. Either backup and restore to the 64bit server or detach\attach the database files.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs"
-
The structure of the data files are the same between 32 bit and 64 bit so they can be copied via backup/restore or attach/detach.
-
Apparently this is quite a bit harder than coping some files around!
I copied the system databases and my databases. I could see the databases from the management studio and my software would run. HOWEVER.....
None of the maintenance plans would work. And the agent won't start. I think it has something to do with the special user like:
APSAAPS2\SQLServer2005SQLAgentUser$APSAAPS2$MSSQLSERVER
I can get the agent to start if I change SQL configuration Manager to have the agent run as Local System instead of Local Service.
I cannot get the service plans to work. I read that the SIDs may have changed for the users, so I deleted and recreated the special users but I don't think that helped. Also, there are Windows security groups for SQL and somehow those should be updated but I do not know how.
Any ideas?
-
OK, maybe I didn't read the question properly. Normally you would install SQL Server on the new server and then copy only the USER databases across. The maintenance plans would be recreated. The users can be copied across using scripts. The jobs would be recreated or copied across.
See http://support.microsoft.com/kb/314546/en-us for more info
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply