Backup Content Database

Use SQL Server tools to back up a content database. You can use SQL Server tools to back up a content database.

To back up a content database by using SQL Server tools. Verify that the user account that is performing this procedure is a member of the SQL Server db_backupoperator fixed database role on the database server where each database is stored.

  • Open SQL Server Management Studio and connect to the database server.
  • In Object Explorer, expand Databases.
  • Right-click the WSS_Content database that you want to back up, point to Tasks, and then click Back Up.
  • In the Back Up Database dialog box, in the Source area, select the kind 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 (http://go.microsoft.com/fwlink/p/?LinkId=114396) in SQL Server Books Online.
  • In the Backup component area, click Database.
  • Either use the default name provided or specify a name for the backup set in the Name text box.
  • Specify the expiration date for the backup set. This date determines how long, or when, the backup set can be overwritten by any later backups that have the same name. By default, the backup set is set to never expire (0 days).
  • In the Destination area, specify where you want to store the backup.

Click OK to back up the database

Restore Content Database

Use SQL Server tools to restore a content database. You can use SQL Server tools to restore a content database by following these steps:

  • If possible, back up the live transaction log of the content database to protect any changes that were made after the last full backup.
  • Restore the last full database backup.
  • Restore the most recent differential database backup that occurred after the most recent full database backup.
  • Restore all transaction log backups that occurred after the most recent full or differential database backup.

To restore a content database by using SQL Server tools. Verify that the user account performing this procedure is a member of the sysadmin fixed server role.

  • If the Windows SharePoint Services Timer service is running, stop the service and wait for several minutes for any currently running stored procedures to finish. Do not restart the service until after you restore the content databases.
  • Start SQL Server Management Studio and connect to the database server.
  • In Object Explorer, expand Databases.
  • Right-click the WSS_Content database that you want to restore, point to Tasks, point to Restore, and then click Database.

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

  • 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 are appropriate for most recovery scenarios.

  • In the Select a page pane, click Options.
  • In the Restore options section, select only Overwrite the existing database. Unless the environment or policies require otherwise, do not select the other options in this section.
  • In the Recovery state section:
    • If you have included all the transaction logs that you must restore, select RECOVER WITH RECOVERY.
    • If you must restore additional transaction logs, select RECOVER WITH NORECOVERY.
    • The third option, RECOVER WITH STANDBY is not used in this scenario.

Note:

For more information about these recovery options, see Restore Database (Options Page) (http://go.microsoft.com/fwlink/p/?LinkId=114420).

  • Click OK to complete the recovery operation.

Start the Windows SharePoint Services Timer service.