Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2009
    Posts
    33

    Unanswered: Moving data to new server

    I have a server that will be going off-line in a couple of weeks, and in the meantime, I need to backup everything and move it to a new location. This involves a lot of data and databases.

    What I have is 33 databases in MS SQL Server 2005. The target machine is MS SQL Server 2008. Each of these databases has a myriad of stored procedures and compiled functions that will need to be moved as well. Essentially the task is to completely replicate the entire MS SQL environment.

    Is there a shortcut to making this happen or am I relegated to backing up each database individually and then restoring them individually?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    You will have to backup each database, and restore it on the new server. This will give you the best back out plan (just start up the 2005 server). When I did this some years ago, I mirrored the databases from the 2005 instance on the 2008 instance. This can be set up well in advance of the cutover (just don't set up a witness), but does require all of the databases to be in FULL recovery mode. When the cutover time comes, break all the mirrors, and issue
    Code:
    restore database ___ with recovery
    on the new 2008 instance, and they should all come up nice and clean. you will need to break the mirrors first, because if you don't, the old 2005 databases become rather horribly corrupt, as they don't know what to do with the SQL 2008 log entries.

  3. #3
    Join Date
    Feb 2009
    Posts
    33
    I shouldn't have to mirror the databases because the current data will be taken off-line as soon as the transfer begins.

    The key for me is to make sure I have the proper credentials for the users as well.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you can do an "in place upgrade" on the same hardware then this process is nearly trivial... Make sure you have good backups stored offline (i.e. on tape, DVD, etc.) and do the upgrade.

    If you need to move to new server hardware, you can script out the "create" scripts for nearly everything using the 2008 GUI (against the 2005 server) and play the newly created scripts into the 2008 instance.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Feb 2009
    Posts
    33
    An upgrade would be complicated by the fact that this is a remote server. Interestingly enough, I don't have to worry about the compatibility of the database with MS SQL Server 2008 because the original development was done using MS SQL Server 2008. The reason we didn't deploy it was because of a technicality in our server lease agreement. For a long time though, we did have the 2005 server mirrored on a 2008 installation. The other server went off-line though before I was in this position. Nothing has changed materially since that time.

    Initially I thought I would script each database, but I could never get it to work quite right. I ended up with lots of errors on every try, so I finally just resorted to backing up the database and restoring as needed. Only now, I have to do it on a different remote system that I cannot connect together remotely. i.e. I have to use a third system to operate as an intermediary during the transfer.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •