locked
Steps to move all databases (Office SharePoint Server 2007) from SQL 2000 to SQL 2008 RRS feed

  • Question

  • Hi All,
    First i have researched this but I need some clarification on whether I need to rebuild the whole farm and migrate the databses or whether I can repoint SharePoint at the new SQL server.

    Senario - We're looking to move our SharePoint 2007 farm from SQL 2000 ( the default instance  - called
    server1 ) to SQL 2008 ( a new instance - called company1\sql2008 ). So basically we're both moving it to a different name and a different SQL version. Do I have to rebuild the farm and reattach the database or is there a supported way to migrate all database  (content + Config + service application) and repoint to the new SQL instance. I'm leaving the front end servers as is, just moving from the old SQL 2000 box.Thanks


    Update - the server is windows 2003 and the new instance will actually be on the same server - so existing server "server1" has SQL 2000, same server will get new install of SQL 2008 and installed as the instance name "company1\sql2008"
    • Edited by Fadious Friday, June 29, 2012 1:58 PM
    Friday, June 29, 2012 10:38 AM

Answers

All replies

  • Hi,

    From your description, my understanding is that you want upgrade your SQL 2000 to SQL 2008 and keep all the databases in the farm. If I am not correct, please let me know.

    Based on my research, you can use in-place upgrade. The SQL Server 2008 Setup program can directly upgrade a SQL Server 2000 instance to SQL Server 2008. The older SQL Server instance is replaced.

    Note: please back up all your databases before in-place upgrade.

    For more information about the process, please check out the following articles:

    http://blogs.msdn.com/b/mikewat/archive/2008/08/11/upgrading-sharepoint-sql-servers-to-sql-server-2008.aspx

    Overview: Upgrading from SQL Server 2000 to SQL Server 2008

    http://download.microsoft.com/download/2/0/B/20B90384-F3FE-4331-AA12-FD58E6AB66C2/SQL%20Server%202000%20to%202008%20Upgrade%20White%20Paper.docx

    Thanks,

    Rock Wang

    Forum Support

    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Rock Wang TechNet Community Support


    Monday, July 2, 2012 4:05 AM
  • Hi Rock,

    Thanks for the reply, actually the solution I was looking for was to move all SharePoint databases from the existing SQL 2000 instance running on server A to another SQL 2008 r2 instance running on server A (the same server but different instance).

    The reason for this is because we have moved all but the SharePoint 2007 databases to the new instance and all that is left are the SP 2007 databases. i don't want to have two intances running on the server as I think this will affect the performance.

    So based on the info above and your option to upgrade the SQL 2000 instance to SQL 2008. Which is the easier option:

    A) Move the SP2007 databases to the new sql 2008 instance and "re-point" the SharePoint config database to the new instance - if so do you know the best method to do this. e.g. re-point sharepoint 2007 at a new instance running on the same server.

    Or 

    B) Upgarde the current SQL 2000 instance to SQL 2008 and move the other "Non-SharePoint" databases back (have already moved these to new instance) to the SQL 2008 (now upgraded) instance. Moving the already databases back isn't such a big deal.

    Thanks in advance,

     

    Wednesday, July 4, 2012 10:06 AM
  • Hi,

    Based on my research, I know there are ways you can move a database between two instances. One is backup database and restore database, another option is to detach the database using sp_detach_db and then attach it using CREATE DATABASE.

    I prefer the first the option.

    In addition, I provide you a link about how to do that for your reference:

    http://www.karaszi.com/SQLServer/info_moving_database.asp

    Thanks,

    Rock Wang

    Forum Support

    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Rock Wang TechNet Community Support

    • Marked as answer by Pengyu Zhao Monday, July 9, 2012 2:23 AM
    Thursday, July 5, 2012 9:10 AM
  • Thanks Rock,

    For the help, for anyone out there with the same issue the solution is provide in the link here.

    http://social.technet.microsoft.com/Forums/en/sharepoint2010setup/thread/9e3d1c02-55d1-4ab4-a3bc-0f732ea15839

    Thanks,

    Fadious

    Thursday, July 12, 2012 5:36 PM