Announcement

Collapse
No announcement yet.

Move SQL Server 2005 installation from Server2003 to Server2008R2, help?

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Move SQL Server 2005 installation from Server2003 to Server2008R2, help?

    Hi everyone

    I've been assigned the job of installing Server 2008R2 Standard on a new server, then install SQL Server 2005(no license for SQL 200 and move all the databases/data from a server running Server2003 with SQL Server 2005.

    After sorting out the Prerequisites needed to install SQL2005 into an 2008R2 OS, all seems to have installed correctly. The question now is how do I go about backing up and restoring the databases on the new server?

    Do I backup the databases one by one and then then restore them on the new server?
    Will this backup all information as well as the database users?
    I've given the new SQL installation the same instance name but the server name is different. Do i need to run a command after the restore to inform the databases about the new server name?

    Thanks in advance!

  • #2
    Re: Move SQL Server 2005 installation from Server2003 to Server2008R2, help?

    It should just be a case of:
    Backup on Old Server
    Restore on New Server

    You will need to check that user accounts have been created but if you are using Windows Integrated Authentication I think they will appear without issues. If not, generate user names / permissions with a script
    Tom Jones
    MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
    PhD, MSc, FIAP, MIITT
    IT Trainer / Consultant
    Ossian Ltd
    Scotland

    ** Remember to give credit where credit is due and leave reputation points where appropriate **

    Comment


    • #3
      Re: Move SQL Server 2005 installation from Server2003 to Server2008R2, help?

      I am using the mixed authentication mode. Will that still be ok?

      How do I check if the user accounts have been created? Do I check under computer management?

      Comment


      • #4
        Re: Move SQL Server 2005 installation from Server2003 to Server2008R2, help?

        When comes to the computer name, I was thinking to initially backup all databases, then rename the new server as the old server and then restore the databases to the new server.
        Does that make sense?

        Comment


        • #5
          Re: Move SQL Server 2005 installation from Server2003 to Server2008R2, help?

          And last question, is there a way to change the location of the databases on the SQL Server? I would like to store all databases on D partition if that is possible.

          I couldn't find a way to change the installation directory during the set-up.

          Comment


          • #6
            Re: Move SQL Server 2005 installation from Server2003 to Server2008R2, help?

            Yes, but cannot remember exactly where it is -- is your friend
            Tom Jones
            MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
            PhD, MSc, FIAP, MIITT
            IT Trainer / Consultant
            Ossian Ltd
            Scotland

            ** Remember to give credit where credit is due and leave reputation points where appropriate **

            Comment


            • #7
              Re: Move SQL Server 2005 installation from Server2003 to Server2008R2, help?

              I tend to detach the databases, copy them across to the new location or to an external USB HDD, copy them into the required location and then attach. With regards to your way, after restoring, you could detach the databases, if not already detached, move them to the required location and then attach them.
              Last edited by Virtual; 16th August 2010, 13:02. Reason: them to then

              Comment


              • #8
                Re: Move SQL Server 2005 installation from Server2003 to Server2008R2, help?

                Originally posted by Virtual View Post
                I tend to detach the databases, copy them across to the new location or to an external USB HDD, copy them into the required location and then attach. With regards to your way, after restoring, you could detach the databases, if not already detached, move them to the required location and then attach them.
                OK thank you. Do I go by those instructions ?

                Comment


                • #9
                  Re: Move SQL Server 2005 installation from Server2003 to Server2008R2, help?

                  I personally would backup the current databases using the feature within the SQL Server Studio manager and do the same to detach/re-attach the databases. I must admit, I can't comment regarding the command line way of doing it though you could always test with databases in a test environment first-of-all.

                  Comment


                  • #10
                    Re: Move SQL Server 2005 installation from Server2003 to Server2008R2, help?

                    Originally posted by Virtual View Post
                    I personally would backup the current databases using the feature within the SQL Server Studio manager and do the same to detach/re-attach the databases. I must admit, I can't comment regarding the command line way of doing it though you could always test with databases in a test environment first-of-all.

                    So backup/restore is the same as detach/re-attach ?

                    Comment


                    • #11
                      Re: Move SQL Server 2005 installation from Server2003 to Server2008R2, help?

                      No -- backup makes a copy of a running database, restore replaces it
                      Detach removes the database from the server, attach adds it again
                      Tom Jones
                      MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
                      PhD, MSc, FIAP, MIITT
                      IT Trainer / Consultant
                      Ossian Ltd
                      Scotland

                      ** Remember to give credit where credit is due and leave reputation points where appropriate **

                      Comment


                      • #12
                        Re: Move SQL Server 2005 installation from Server2003 to Server2008R2, help?

                        Originally posted by Ossian View Post
                        No -- backup makes a copy of a running database, restore replaces it
                        Detach removes the database from the server, attach adds it again
                        Thank you, that helps a lot. Since I still need the databases to run on the old Server the way I see it is to backup and restore the databases on the new Server and once the new database Server is up and running, disconnect the old one from the network.

                        Comment


                        • #13
                          Re: Move SQL Server 2005 installation from Server2003 to Server2008R2, help?

                          Sounds very reasonable -- each server is separate so as long as you are careful which one you connect to, and are not worried about content getting out of synch, leave the old server online
                          Tom Jones
                          MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
                          PhD, MSc, FIAP, MIITT
                          IT Trainer / Consultant
                          Ossian Ltd
                          Scotland

                          ** Remember to give credit where credit is due and leave reputation points where appropriate **

                          Comment


                          • #14
                            Re: Move SQL Server 2005 installation from Server2003 to Server2008R2, help?

                            Originally posted by Ossian View Post
                            Sounds very reasonable -- each server is separate so as long as you are careful which one you connect to, and are not worried about content getting out of synch, leave the old server online
                            I forgot that the old server by then will have new information inside each databases. So in that case, before the old one gets disconnected, I should grab a last copy of the databases and import into the new one.

                            Was thinking that I will need to change the computer name of the new Server to match the old one so the clients will not need any extra configuration? In that case I might as well detach the databses completely I guess or just back them up in case I have to go back to old server for any reason?

                            Comment


                            • #15
                              Re: Move SQL Server 2005 installation from Server2003 to Server2008R2, help?

                              If you change the computer name on the new server and then reconnect the old server you WILL have issues. Better (IMHO) to have a new name and change the client settings, but of course I don't know how many clients or how they are connecting
                              Tom Jones
                              MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
                              PhD, MSc, FIAP, MIITT
                              IT Trainer / Consultant
                              Ossian Ltd
                              Scotland

                              ** Remember to give credit where credit is due and leave reputation points where appropriate **

                              Comment

                              Working...
                              X