I am trying to figure out the best method for transferring SQL 2005 databases from 2 different physical servers to a new server running windows server 2008 64bit.

I own a fully registered copy of Microsoft SQL 2005 32bit Standard Edition. However all of my SQL databases together are only 4GB in size thereby making 2008 SQL Express an option.

Here is the breakdown of the servers:

  1. (Old Server1) Windows 2003 Server 32bit - SQL Express 2005 32bit
  2. (Old Server2) Windows 2003 Server 32bit - SQL Server 2005 32bit Standard
  3. (New Server) Windows 2008 Server 64bit - (Not sure which version to install)

The objective is to migrate 2 SQL databases from Old Server1 and 1 SQL database from Old Server 2 to the New Server. What version should I install on the new server??? Do I go with 2008 express or install the 2005 Standard Edition?

Pending your response to which version to install, what are the correct steps to migrate (which I have never done before) the databases from both of these servers to the new server?

I have researched this online and I think this particular situation is unique in that I am transferring from both SQL Express and Standard from 2 different physical servers to a 2008 server that is 64bit. I cannot find anything online that I feel confident in trying. Please help me, as I have this migration deadline of this weekend. Any help is appreciated. Thanks.

  • 1
    What's your initial plan?
    – GregL
    Mar 25 '15 at 12:16
  • Greg, my initial plan is migrating these databases to the new server. I am stuck on the method as well as the version to install on the new server. I have tried the backup/restore method and it did not work. Mar 25 '15 at 12:34
  • @DixieAdmin What exactly did you backup / restore? You are only migrating user databases, correct? No system databases (master, tempdb, model, msdb).
    – Reaces
    Mar 25 '15 at 12:36
  • Just the databases. I didn't realize that there are other things to backup and restore such as the logins, agents, etc etc. I was given bad information beforehand from my previous admin on how to carry out this procedure. The only thing I did was to use the backup under "tasks" then did the restore on the new server which I have 2008 express installed. My admin recommended maybe we should get rid of 2008 and install the fully licensed copy of 2005. Mar 25 '15 at 12:39
  • Just so you know...I am a complete noob at SQL. Very important you understand that if you haven't figured that out already. Mar 25 '15 at 12:40
  1. Make a list of all the databases you will need to move from the each server and their versions. Backup current databases.
  2. List what application/users will be impacted by this and what you will need to change at the application end.[connection strings info or others]
  3. On the new server make sure you have appropriate ports open, database server/ip information for the new connection strings for adding/changing on application side.
  4. Database version decision: It is easier to go from 2005 to 2008 then the other way around. Full version often have extra capabilities like jobs, tasks, backups more capacity etc than the express edition but all depends how your current system is setup and what will be required with the future database server/software.
  5. After you perform backup/restore or move/attach[depends on version] you can configure one application at a time. Communicate with possible users about possible interruptions in services or do it in after hours.
  6. Document all the steps and changes so it will be easy to go back if anything do not work as planned.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.