Share via


Back up a farm by using SQL Server Tools (Windows SharePoint Services 3.0)

Applies To: Windows SharePoint Services 3.0

 

Topic Last Modified: 2009-04-15

Microsoft SQL Server 2005 provides high-performance backup and restore capability. Using the tools included with SQL Server 2005, you can prepare Windows SharePoint Services 3.0 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 Windows SharePoint Services 3.0 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 (Windows SharePoint Services).

Back up a Windows SharePoint Services 3.0 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 (Windows SharePoint Services).

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 a Windows SharePoint Services 3.0 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

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 the entire farm (Windows SharePoint Services 3.0 technology)
Prepare to back up and restore a farm (Windows SharePoint Services 3.0)
Back up a farm by using built-in tools (Windows SharePoint Services 3.0)
Restore a farm by using built-in tools (Windows SharePoint Services 3.0)
Restore a farm by using SQL Server Tools (Windows SharePoint Services 3.0)
Back up and restore a farm by using DPM (Windows SharePoint Services 3.0)
Restore a farm after a configuration database problem (Windows SharePoint Services)