Repair Windows SharePoint Services after moving the databases

  • Article

Updated: March 3, 2009

Applies To: Windows SBS 2008

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

Solution   Manually move the Windows SharePoint Services databases.

To manually move the Windows SharePoint Services databases

  1. Connect to Microsoft SQL Server by doing the following:

    1. Click Start, click All Programs, click Microsoft SQL Server 2005, right-click Microsoft SQL Management Studio Express, and then click Run as Administrator.

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

    3. 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.

    4. Click Connect.

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

    • ShareWebDb.   The database for the internal Web site.

    • SharePoint_AdminContent_{GUID}, SharePoint_Config_{GUID}, WSS_Content, and WSS_Search_Win****.   Databases that the Windows SharePoint Services 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 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 Windows SharePoint Services engine, skip this step. If the database is for the internal Web site or for another site that was created after Setup, set the database to Read Only by doing the following:

    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, and then, in SharePoint Site Management, click Site collection quotas and locks.

    4. In Site Collection, click the site 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 by doing the following:

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 *db1* is the name of the database that you are moving, *Datafile\_Path* is the path to the MDF file, and *Logfile\_Path* is the path to the LDF log file. You must include the single quotation 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 include the single quotation marks. When you are done, complete Step 6e.

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

  2. Unlock the database for the internal Web site by doing the following:

    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.

To read the most recent version of this topic, see the Microsoft Web site (https://go.microsoft.com/FWLink/?LinkID=147040). The most recent version might contain additional information that was not available when Windows SBS 2008 was released.