locked
Sharepoint SQL migration using restore from central admin backup RRS feed

  • Question

  • I have a SharePoint 2007 farm which consists of a single application server and a separate SQL box.

    The DB is SQL 2000. The objective is to upgrade to SQL 2008.

    I have read countless articles on how to migrate the DB server from 2000/2005 to 2008. There does not seem to be a consensus on which approach is best.  One approach I do not see much written about is one I tried and successfully implemented.

    What I did was simply take advantage of the built in backup feature in the central administration site. I performed a full backup of the site and copied the back up to a development server in a different domain.

    I then created a new farm in the development environment and pointed to a 2008 SQl database. The only databases in the farm after I created the farm were the config_Db and central administration DB.

     Once the farm was created; I went to the central administration site and kicked off the restore from backup process. I went through all the steps in the wizard until I got to the part where it asks you to select same or new configuration. I had to select new because I was in a different domain and the drives were not the same for the SQL as were in production. Additionally, I had to change the database server name and log in credentials to ensure that the restore be successful. Once I made sure everything was correct, I kicked off the restore and successfully was able to access all the sites. The restore process attached the databases for my web applications to the new server and the Central Administration server page showed it was pointing to the new database server.

    I guess I am surprised how easy this was and that no errors are logging in the UL file. Having said that, I am wondering if anyone can provide me with any reason why this approach is not viable. I have read all the articles including the Microsoft move all database articles which require a lot more hands on configuration to attain the same objective I was able to accomplish. Is there any reason why restoring from back an entire farm to migrate your database server is not a good idea? Please let me know what I may be missing.




    Saturday, May 21, 2011 3:17 PM

Answers

  • Hi Bazin,

    The approach you used is fine too. When backup a SharePoint farm using Central Administrator(CA), SharePoint backups the farm(include the config databases and content databases )into files. While restoring, the databases will be restored to SQL Server using T-SQL, and the web applications will be restored too. All looks fine.

    "Attach databases and upgrade to SharePoint Server 2010" is another approach, and is recommended as it enables your to speed up the upgrade process by detaching and attaching databases to upgrade multiple databases at the same time(Attaching is faster than Restoring ).

    I don't think we need to compare these approaches. But just select the approach that is suitable for you. Don't forget to backup the database before any upgrade.

    Thanks,
    Jinchun chen


    Jin Chen - MSFT
    Thursday, May 26, 2011 8:44 AM
    Moderator
  • An even easier method is to restore the Dbs to the new SQL SErver and create a SQL Alias using

    1. SQL Server Configuration Manager
    2. SQL Server Client Network Utility
    3. or without any SQL tools installed cliconfg.exe

     

    -Ivan


    Ivan Sanders My LinkedIn Profile, My Blog, @iasanders.
    Friday, May 27, 2011 3:21 PM

All replies

  • Hi Bazin,

    The approach you used is fine too. When backup a SharePoint farm using Central Administrator(CA), SharePoint backups the farm(include the config databases and content databases )into files. While restoring, the databases will be restored to SQL Server using T-SQL, and the web applications will be restored too. All looks fine.

    "Attach databases and upgrade to SharePoint Server 2010" is another approach, and is recommended as it enables your to speed up the upgrade process by detaching and attaching databases to upgrade multiple databases at the same time(Attaching is faster than Restoring ).

    I don't think we need to compare these approaches. But just select the approach that is suitable for you. Don't forget to backup the database before any upgrade.

    Thanks,
    Jinchun chen


    Jin Chen - MSFT
    Thursday, May 26, 2011 8:44 AM
    Moderator
  • An even easier method is to restore the Dbs to the new SQL SErver and create a SQL Alias using

    1. SQL Server Configuration Manager
    2. SQL Server Client Network Utility
    3. or without any SQL tools installed cliconfg.exe

     

    -Ivan


    Ivan Sanders My LinkedIn Profile, My Blog, @iasanders.
    Friday, May 27, 2011 3:21 PM