Last updated on December 6th, 2016 at 11:32 am

It’s always a good idea to test your SharePoint backups to ensure they will actually work in case you ever need them. You are doing backups, right?

This article focuses on restoring a production content database backup created using SQL Server. The goal is to restore the database to a recovery portal.

NOTE: This example is based on SharePoint 2007 and SQL Server 2008. Results may vary with different versions of SharePoint and/or SQL Server.

For this example, the following information will be used:

  • SQL Database Backup: MOSS_Content_Sales.bak
  • Content Database: MOSS_Content_Sales
  • Recovery Portal URL: http://recoveryportal
  • Recovery Portal Managed Path: /sales

Prepare the Recovery Portal

Create Managed Path

  • NOTE: This step can be skipped if a managed path has already been created for the content database that will be restored.  This may be the case if scheduled database restore tests are in place.
  • Open the Central Administration web site
  • Select Application Management -> SharePoint Web Application Management -> Define Managed Paths
  • For Path, enter sales
  • For Type, select Explicit Inclusion
  • Click OK to return to the Application Management page

Create New Content Database

  • NOTE: This step can be skipped if the content database that will be restored already exists.  This may be the case if scheduled database restore tests are in place.
  • Select Content databases under SharePoint Web Application Management
  • Click Add a content database
  • Enter MOSS_Content_Sales for Database Name
  • Click OK

Create a new Site Collection

  • NOTE: This step can be skipped if a site collection has already been created for the content database that will be restored.  This may be the case if scheduled database restore tests are in place.
  • Return to the Application Management tab
  • Select SharePoint Site Management -> Create site collection
  • Verify the selected Web Application is the correct application. If not, click the drop down, click Change Web Application and choose the appropriate web application.
  • Enter Sales for the Title
  • For URL, ensure the drop down shows /sales. If not, click the drop down and select the /sales managed path.
  • Select Blank Site for Template Selection
  • Enter appropriate user accounts for Primary and Secondary Site Collection Administrator fields.
  • Click OK

Remove Content Database

  • Return to the Application Management tab
  • Select SharePoint Web Application Management -> Content databases
  • Click on the MOSS_Content_Sales content database.
  • Check the Remove content database option
  • Click OK
  • Confirm the delete content database dialog

Restore the latest FULL SQL backup

  • Locate the most current FULL backup of the content database to restore and copy it to the recovery portal
  • Launch SQL Server Management Studio on the recovery portal
  • Double click Databases in the left hand pane
  • NOTE: If scheduled database restore tests are in place, the content database may already exist in SQL Server.  Review the existing databases in the left hand pane, and if the content database already exists:
    • Click the existing content database
    • Right click and select Delete
    • This will present the Delete Object dialog.  Click to select the Close existing connections option
    • Click OK
  • Right click Databases and select Restore Database
  • Under Destination for restore, enter MOSS_Content_Sales
  • Under Source for restore, select From device
  • Click the button to open the Specify Backup dialog
  • Click Add
  • Browse for and select the backup file that was copied from the production portal
  • Click OK to close the Locate Backup File dialog
  • Click OK to close the Specify Backup dialog
  • Under Source for restore, click the Restore check box for the file selected
  • If a newer differential backup will also be restored:
    • Click Options in the left hand pane
    • For Recovery State, select the second option RESTORE WITH NORECOVERY
  • Click OK to perform the restore
  • If not restoring a newer differential backup, close SQL Management Studio

Restore the latest DIFFERENTIAL SQL Backup

  • NOTE: Skip this step if the full backup was the latest backup
  • Locate the most current DIFFERENTIAL backup of the content database to restore and copy it to the recovery portal
  • Return to the SQL Server Management Studio
  • In the left pane, locate and click on the content database that was restored from the previous step
  • Right click on the content database and select Tools -> Restore -> Database
  • Under Source for restore, select From device
  • Click the  button to open the Specify Backup dialog
  • Click Add
  • Browse for and select the differential backup file that was copied from the production portal
  • Click OK to close the Locate Backup File dialog
  • Click OK to close the Specify Backup dialog
  • Under Source for restore, click the Restore check box for the file selected
  • Click OK to perform the restore
  • Close SQL Management Studio

Attach Content Database

  • Open a command prompt
  • Navigate to C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\Bin
  • Run the following command to attach the content database to the site collection: stsadm.exe -o addcontentdb -url http://recoveryportal/sales -databasename MOSS_Content_Sales.

The restored site collection should now be available on the recovery portal. Browse to http://recoveryportal/sales and verify the site is available.