Restore a farm by using SQL Server tools (Office SharePoint Server 2007)

Applies To: Office SharePoint Server 2007

This Office product will reach end of support on October 10, 2017. To stay supported, you will need to upgrade. For more information, see , Resources to help you upgrade your Office 2007 servers and clients.


Topic Last Modified: 2016-11-14

You can use Microsoft SQL Server 2005 tools to restore databases that are used by Microsoft Office SharePoint Server 2007.


You cannot use the Office SharePoint Server 2007 restore feature to restore the configuration database or the Central Administration content database. You cannot use the SQL Server 2005 backup and restore tools to restore your configuration database to a different farm or topology configuration.

You can restore the following databases:

  • Content databases

  • Databases for Shared Services Providers (SSPs)

  • Search databases for the SSPs

  • Search databases


    The search index is not stored in SQL Server. If you use SQL Server 2005 tools to back up and restore search, you must perform a full crawl upon restoring the database. For more information, see Protecting Search in Choose what to protect (Office SharePoint Server).

  • Central Administration content database

  • Configuration database


    The configuration database and the Central Administration content database contain computer-specific information. Therefore, you can restore them only to an environment that you configure to be precisely the same, including all software updates, server names, and numbers of servers. That is, you cannot back up the configuration database, change your topology or server roles, and then restore the configuration database.

For more information about using SQL Server to restore databases, see Backing Up and Restoring Databases in SQL Server (

Using SQL Server tools to recover a database


When protecting Office SharePoint Server 2007, we recommend that you configure a recovery farm — a second farm that is only used to restore data — for site and item recovery. The recovery farm is not intended to be a live farm. The recovery farm does not need to have the same hardware as your primary farm — we recommend that you use a single server installation or a virtual farm. For more information about creating a recovery farm, see Create a recovery farm (Office SharePoint Server 2007).

You should recover your database in the following order:

  1. If possible, back up the live transaction log of the current database to protect any changes that have been made since the last full backup.

  2. Restore the last full database backup.

  3. Restore the most recent differential database backup since the last full database backup.

  4. Restore all transaction log backups since the last full or differential database backup.


Recovery of the SharePoint configuration database cannot be performed by using this procedure. To restore the configuration database, see Restore a farm after a configuration database problem (Office SharePoint Server).


Membership in the dbcreator fixed server role is the minimum required to perform this procedure.

Restore an existing database

  1. If the Windows SharePoint Services Timer service is running, stop the service and wait for a few minutes for any currently running stored procedures to complete. Do not restart the service until you have restored all the databases that you need to restore.

  2. Start SQL Server Management Studio and connect to the database server.

  3. In Object Explorer, expand Databases.

  4. Right-click the database that you want to restore, point to Tasks, point to Restore, and then click Database.

    The database is automatically taken offline during the restore operation and cannot be accessed by other processes.

  5. In the Restore Database dialog box, specify the destination and the source, and then select the backup set or sets that you want to restore.

    The default values for destination and source typically suit most recovery scenarios.

  6. In the Select a page pane, click Options.

  7. In the Restore options section, select only Overwrite the existing database. Unless your environment or policies require otherwise, do not select the other options in this section.

  8. In the Recovery state section:

    • If you have included all the transaction logs that you must restore, select RESTORE WITH RECOVERY.

    • If you must restore additional transaction logs, select RESTORE WITH NORECOVERY.

    • The third option, RESTORE WITH STANDBY is not used in this scenario.


      For more information about these recovery options, see Restore Database (Options Page) (

  9. Click OK to complete the restore operation.

  10. Repeat steps 4 through 9 for each database that you are restoring.

  11. Start the Windows SharePoint Services Timer service.

See Also


Back up and restore an entire farm (Office SharePoint Server 2007)
Prepare to back up and restore a farm (Office SharePoint Server 2007)
Back up a farm by using built-in tools (Office SharePoint Server 2007)
Restore a farm by using built-in tools (Office SharePoint Server 2007)
Back up a farm by using SQL Server tools (Office SharePoint Server 2007)
Back up and restore a farm by using DPM (Office SharePoint Server 2007)
Restore a farm after a configuration database problem (Office SharePoint Server)