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

  • Article

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

Microsoft SQL Server 2005 provides high-performance backup and restore capability. Using the tools included with SQL Server 2005, you can prepare Microsoft Office SharePoint Server 2007 to recover quickly from several potential data-loss events:

  • Media failure.

  • User errors — for example, dropping a table by mistake.

  • Hardware failures — for example, a damaged disk drive or permanent loss of a server.

  • Natural disasters.

There are three different kinds of backup that can be performed using SQL Server Management Studio:

  • Full Creates a complete backup of the database. A full backup should be performed the first time you back up a database and periodically thereafter.

  • Differential Backs up only the changes to the database that have occurred since the last full backup.

  • Transaction Log Enables you to recover the database to the point of failure or to a specific point in time.

How you use these backup methods is determined by the recovery model you choose for this data. For more information about recovery models in SQL Server 2005, see Overview of Recovery Models (https://go.microsoft.com/fwlink/?LinkId=114396&clcid=0x409).

Benefits of using SQL Server 2005 to back up and restore your Office SharePoint Server 2007 farm include:

  • Existing SQL Server disaster recovery strategies can be re-used.

  • Full and differential backups can be configured with added verification.

  • A full-fidelity data backup is provided.

  • Can be faster than backups that are performed by using SharePoint Products and Technologies built-in tools.

For more information about how SQL Server 2005 compares with other tools for protecting data, see Choose backup and recovery tools (Office SharePoint Server).

Back up an Office SharePoint Server 2007 farm

Warning

Before you begin, be sure you understand the synchronization problems that can occur when restoring the configuration database and the steps you can take to avoid them. For more information, see Restore a farm after a configuration database problem (Office SharePoint Server).

For more information about using SQL Server 2005 to back up databases, see Backing Up and Restoring Databases in SQL Server (https://go.microsoft.com/fwlink/?LinkID=102629&clcid=0x409).

Important

Membership in the db_backupoperator fixed database role is the minimum required to complete these procedures.

Back up an Office SharePoint Server 2007 farm

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

  2. In Object Explorer, expand Databases.

  3. Right-click the configuration database (usually named SharePoint_Config), point to Tasks, and then click Back Up.

  4. In the Back Up Database dialog box, select the type of backup that you want to perform from the Backup type list. For more information about which backup type to use, see Overview of Recovery Models (https://go.microsoft.com/fwlink/?LinkId=114396&clcid=0x409) in the SQL Server 2005 Books Online.

  5. Under Backup component, select the Database option.

  6. In the Name text box, type a name or use the default.

  7. In the Description text box, type a description of the backup.

  8. Under Backup set will expire, specify how long the backup should be kept, or use the default. When the backup set expires, the backup set can be overwritten by any subsequent backups with the same name. The backup set is set to never expire (0 days) by default.

  9. In the Destination section, specify a location to store the backup set, or use the default.

  10. Click OK to back up the database.

  11. Repeat steps 3 through 10 for the following databases:

    • Content databases

    • Databases for Shared Services Providers (SSPs)

    • Search databases for the SSPs

    • Search databases

      Note

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

Schedule a delayed or recurring backup

  1. In the Backup database dialog box, after you have entered the database backup information (see the Backup a server farm procedure earlier in this article), click the arrow to show the Script menu, and then click Script Action to Job.

  2. In the New Job dialog box, in the Select a page pane, click Schedules, and then click New.

  3. In the New Job Schedule page, specify a name, type, frequency, and duration that you want for the scheduled backup, and then click OK.

  4. Click OK to accept the scheduled job changes.

  5. Click OK to save the job changes and start the database backup.

    For more information about how to schedule a database backup, see How to schedule a database backup operation by using SQL Server Management Studio in SQL Server 2005 (https://go.microsoft.com/fwlink/?LinkId=109568&clcid=0x409).

See Also

Concepts

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)
Restore 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)