I have a new server. It acts as a web application server (asp.net) with an sql database back end.
The new server is 64-bit Windows Server 2008 R2 and I have installed 64-bit SQL Server 2008 R2. I need to migrate databases that currently reside on a 32-bit Windows Server 2003 box running 32-bit SQL server 2000. I plan on using the backup and restore method. It looks like (from what I have read so far) I shouldn't encounter any real problems.
I thought I would have to use transact SQL (specifying WITH MOVE) rather than a wizard because the data and log file paths on my new server will be different than on the old server - but it looks like the restore wizard on the 2008 box will move the files to the proper location rather than creating the old path from the old server. It looks to me that the "resore the database file as" option takes care of that. The defualt value there was:
C:\MyApps\SQL Server 2008 R2 64-bit\MSSQL10_50.MSSQLSERVER\MSSQL\Data\MyDatabaseName.MDF
Note that when I installed SQL server 2008 it wanted two paths - a 32-bit path (which defaulted to something like C:\Program Files\SQL Server (x86) ) and a 64-bit path (which defaulted to something like C:\Program Files\SQL Server)
It looks like the data files are being installed to the 64-bit path.
So Question 1 is: Can I use the GUI as above to restore the database? Does the "resore the database file as" location option take care of the "WITH MOVE" option (the WITH MOVE wording does not appear in the GUI Wizard as such)
Question 2 is: Also, when installing SQL server 2008 R2, it created TWO application paths - one for 32-bit and one for 64-bit. The server management Studio is in the 32-bit path... is there not a 64-bit server management studio?