Moving SharePoint Databases to a different SQL server RRS feed

  • Question

  • Hello All,

    I have reviewed many of the threads, blogs and articles on this topic, but it appears that my situation does not quite fit the scenarios being described.

    I inherited a small MOSS 2007 Enterprise farm (only one site collection):

    • One SharePoint server named MOSS2007 (Win2003 32-bit and SQL 2005 installed on it)
    • One SQL 2008 server named SQL2008 (Win2003 32-bit)

    My goal is to have all of the DBs reside on the SQL2008 server.  Then I will uninstall SQL 2005 from the MOSS2007 server.

    Here is my problem:

    • Only two DBs currently live on the SQL2008 server:  (1) The content DB for the site collection, and (2) The WSS_Search DB
    • ALL of the remaining databases are located in SQL2005 (in the MOSS2007\OFFICESERVERS instance)

    Thus far my research has indicated that I can move the MOSS2007 DBs to a new SQL server with a new name ONLY if the Windows and SQL versions are identical to those on the new server (not the case for me) OR if the new SQL server has the same name as the old SQL server (i.e. old SQL server is being replaced by the new SQL server - also not the case for me).

    The following article is where I saw these restrictions:  http://technet.microsoft.com/en-us/library/cc512725(office.12).aspx#Section1

    I am considering attempting an in-place upgrade to SQL2005 on the MOSS2007 server to bring my configuration inline with one of the supported scenarios.  Would that work?  If so, would it be the best approach?

    I am looking for a clear path to follow but have not yet been able to find one.  All detailed suggestions are welcome.



    • Edited by Mike Walsh FIN Friday, January 21, 2011 6:55 AM (MOSS) 2007 not needed in Title. Defaul here. Already mentioned clearly in the text
    • Edited by Jeff Hepner Monday, January 31, 2011 6:17 PM Incorrect SECTION in the hyperlink URL. Changed SECTION2 to SECTION1.
    Thursday, January 20, 2011 10:38 PM


  • Since nobody confirmed the data migration path from SQL 2005 to SQL 2008 was viable I decided to upgrade SQL 2005 on the MOSS2007 server to SQL 2008.

    I will follow the steps listed above to move the databases.

    Jeff Hepner
    • Marked as answer by Jeff Hepner Monday, January 31, 2011 4:26 PM
    Monday, January 31, 2011 4:26 PM

All replies

  • The main content db is easiest - you can use SQL techniques to move the databases to the new 2008 server, and then drop the old content database from sharepoint Central Admin and add the new ones with stsadm -o addcontentdb. (Dont forget teh Central Admin content db!)

    Reseting the configuraiton databases is also doable - use:

    stsadm.exe -o deleteconfigdb


    stsadm.exe -o setconfigdb


               -databaseserver <database server>

               [-databaseuser <database user>]

               [-databasepassword <database user password>]

               [-databasename <database name>]






               [-addomain <Active Directory domain>]

               [-adou <Active Directory OU>]

    Then you can use SharePoint backup/restore to move the SSP-related databases (profiels, search etc.) to their new location.  Good luck!

    Chris McNulty MCSE/MCTS/MSA/MVTS http://www.kma-llc.net | blog http://blogs.kma-llc.net/microknowledge | twitter @cmcnulty2000
    Friday, January 21, 2011 12:03 PM
  • So, it's not necessary to upgrade SQL 2005 to SQL 2008 on the MOSS2007 server?  Please confirm.  I'm not a SQL guy so I need a bit of hand-holding.

    The reason I ask is because of the following snippet from the TechNet Article I referenced originally:

    Move all databases to a different database server

    “This is a procedure for moving all the databases in an Office SharePoint Server 2007 farm to a new database server that has a different name. The new database server must use the same version of Windows Server and SQL Server as the old database server.”

    I have never tried to do this before and I need to be very cautious about how I approach this activity.  This farm has been in production since May 2010. It has close to 200 sub-sites in the collection and about 17GB of content.  And, of course, I don't have access to a test or dev farm.

    I need to understand what, if any, reconfiguration or rebuilding will be necessary.  Since I will only be permitted to take the farm down over the weekend I have to be able to confirm that everything "is as it was" for the end users.

    Fortunately, both of these servers are virtual.  I will take snapshots before I begin and will be able to revert back if things go badly.

    These are the DBs that I need to move:

    ·         SharePoint_AdminContent

    ·         SharePoint_Config

    ·         SharedServices_Content

    ·         SharedServices1_DB

    ·         SharedServices1_Search_DB

    A summary of the steps outlined in the article are as follows:

    1.       Prepare the new database server.

    2.       Record which Web applications are associated with the SSPs. This information can be used to reassociate Web applications with the restored SSPs.

    3.       Back up the SSPs.

    4.       Remove the SSPs from the farm.

    5.       Stop the farm. Before you can move all the databases, you must stop the farm to ensure that no changes are made while the databases are moved.

    6.       Back up the following databases:

    ·         Content databases

    ·         Central Administration content database

    ·         Configuration database

    ·         Windows SharePoint Services Help Search database

    ·         The other databases in the farm are backed up and restored at the same time as the SSPs.

    7.       Copy or move the database backup files to the destination database server.

    8.       On the destination database server, restore the databases that you backed up.

    9.       Copy to the destination database server all the SQL Server logins, fixed server roles, fixed database roles, and permissions for these databases.

    10.   Redirect the farm to reference the new database server.

    11.   Restart the server that is running Central Administration to apply the changes and ensure that the services, Web sites, and application pools associated with Office SharePoint Server 2007 are started.

    12.   Restore the SSPs from the backup.

    Does anyone see any issues with following these steps if the source DB server is SQL 2005 and the target DB server is SQL 2008?  If someone can confirm that this will work then I will be able to proceed.

    Thanks everyone.

    Jeff Hepner
    Friday, January 21, 2011 4:56 PM
  • Since nobody confirmed the data migration path from SQL 2005 to SQL 2008 was viable I decided to upgrade SQL 2005 on the MOSS2007 server to SQL 2008.

    I will follow the steps listed above to move the databases.

    Jeff Hepner
    • Marked as answer by Jeff Hepner Monday, January 31, 2011 4:26 PM
    Monday, January 31, 2011 4:26 PM