Repair SharePoint Foundation 2010 after moving the databases

  • Article

Published: March 10, 2011

Applies To: Windows Small Business Server 2011 Standard

Problem   You successfully moved the SharePoint Foundation 2010 databases, but now the internal website is not responding, or you are experiencing other issues, such as problems with the router configuration or with Internet Information Services (IIS).

Solution   Manually move the SharePoint Foundation 2010 databases.

To manually move the Windows SharePoint Services databases

  1. Connect to Microsoft SQL Server:

    1. Click Start, click All Programs, then click Microsoft SQL Server 2008 R2.

    2. Right-click SQL Server Management Studio, and then click Run as administrator.

    3. On the User Account Control page, click Continue.

    4. In the Connect to Server dialog box, do the following:

      1. In Server type, click Database Engine.

      2. In Server name, type np:\\.\pipe\MSSQL$Microsoft##SSEE\sql\query.

      3. In Authentication, click Windows Authentication.

    5. Click Connect.

  2. In Microsoft SQL Server Management Studio Express, browse to <root>\Databases, and then expand it. The following databases are listed:

    • ShareWebDb   Database for the internal website.

    • SharePoint_AdminContent_{GUID}, SharePoint_Config_{GUID}, WSS_Content, and WSS_Search_Win****   Databases that the SharePoint Foundation 2010 engine uses.

    • System Databases and SUSDB   Databases that should not be changed.

    • If there are any additional databases, they are also listed.

    You need to move all databases except the System Databases and SUSDB.

  3. Locate the database files:

    1. The default location of the files is C:\Windows\SYSMSI\SSEE\MSSQL.2005\MSSQL\DATA\. In that folder, there should be a file named <name>.mdf and a file named <name>_log.ldf, where <name> is the name of the target database.

    2. If the files are not in the default location, in the Microsoft SQL Server Management Studio Express window, click New Query, type Select filename from sysdatabases where name = ‘<database name>’, where <database name> is the name of the database listed in Step 2, and then click Execute. The location of the database file is displayed.

  4. Verify that there is enough space on the target drive, and then complete Steps 5-8 for each database listed in Step 2 (except System Databases and SUSDB, which should not be moved).

  5. If the database is for the SharePoint Foundation 2010 engine, skip this step. If the database is for the internal website or for another site that was created after Setup, set the database to Read Only:

    1. Click Start, click Administrative Tools, and then click SharePoint 3.0 Central Administration.

    2. On the User Account Control page, click Continue.

    3. Click Application Management, click SharePoint Site Management, click Site collection quotas and locks.

    4. In Site Collection, click the site that you want to lock.

    5. In Site Lock Information, click Read-only (blocks additions, updated, and deletions), and then in Additional lock information, type a reason for locking the site.

    6. Click OK.

  6. Move the database:

Note

If any of these steps fail, roll back to the previous setting by following the instructions in Step 7. 

1.  Run the following SQL command to set the database offline: `alter database [db1] set OFFLINE with ROLLBACK IMMEDIATE`, where *db1* is the name of the database that you are moving. You must include the brackets.

2.  Run the following SQL command to detach the database: `sp_detach_db [db1]`, where *db1* is the name of the database that you are moving. You must include the brackets.

3.  Copy the physical database files (MDF and LDF) to the destination location in Windows Explorer.

4.  Run the following SQL command to attach the new database: `sp_attach_db @dbname = N‘db1’, @filename1=’Datafile_Path’, @filename2=’Logfile_Path’`, where:
    
    1.  *db1* is the name of the database that you are moving  
          
    2.  *Datafile\_Path* is the path to the MDF file  
          
    3.  *Logfile\_Path* is the path to the LDF log file  
          
    
    You must enclose the parameters in single quote marks.

5.  Run the following SQL command to bring the new database online: `alter database [db1] set ONLINE`, where *db1* is the name of the database that you are moving. You must include the brackets.

6.  If Steps 6a-6e are successful, delete the original database files.

  1. If Step 6 is not successful, do the following:

    1. If Step 6a fails, ignore it and proceed to Step 8.

    2. If Step 6b fails, complete Step 6e to roll back to the original state.

    3. If Step 6c or Step 6d fails, run the following SQL command to roll back: sp_attach_db @dbname = N‘db1’, @filename1=’Original_Datafile_Path’, @filename2=’Original_Logfile_Path’, where:

      • db1 is the name of the database you are moving, Original Datafile Path is the path to the MDF file, and Original Logfile Path is the path to the LDF log file

      You must enclose the parameters in single quote marks. After the command has been performed, complete Step 6e.

    4. If Step 6e fails, complete Step 6b, and then complete Step 7c.

  2. Unlock the database for the internal website:

    1. Click Start, click Administrative Tools, and then click SharePoint 3.0 Central Admininstration.

    2. On the User Account Control page, click Continue.

    3. In SharePoint Site Management, click Site collection quotas and locks.

    4. In Site Collection, select the site that you want to unlock.

    5. In Site Lock Information, click Not locked.

    6. Click OK.