Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Special Announcement: SQL Server Performance Tuning Tips and Tricks Webinar
 

Unattached Content Database Recovery in SharePoint 2010


By:   |   Read Comments   |   Related Tips: > Sharepoint

Problem

As a SharePoint Administrator, there might be a time when you would need to restore only some but not all content within a content database. How would you do that in SharePoint 2010? Read on to find out...

Solution

In earlier versions of SharePoint, to restore or recover content from a backup file, we had to restore the backed up file to a database server and had to attach that restored database to a another SharePoint farm. Then we needed to export the required content from this new temporary farm and then migrate it to the original farm where we wanted to recover it. This whole process required a huge time investment and rigorous planning.

SharePoint 2010 removes much of the administrative overhead with the inclusion of a Granular Backup and Restore feature. In the above mentioned scenario, we don't need to setup another temporary farm just to restore some content from the backup. We can simply restore a content database backup on any SQL Server instance (or can create database snapshot of the current database before making changes to the web application) and then use the unattached content database recovery feature from the Central Administration to browse the content of the backup. Then we can export it to a file and import it whenever we want.

So in nutshell there are two stages for such types of recoveries:

  • Backing up the site collection or exporting the site/library/list objects from the unattached content database. You use SharePoint 2010 Central Administration UI to perform this operation.
  • Restoring the site collection or importing the site/library/list objects from the backed up/exported file. This operation cannot be performed using the SharePoint 2010 Central Administration UI and hence you need to use erShell cmdlets or STSADM tool (discussed in detail below).

In In this tip we will discuss using the unattached content database recovery feature.

Browsing and Exporting the content of an unattached database from the Central Administration

In this example, I have taken the content database backup of a web application and restored it on the database server. Next I will be browsing this database , and then export the required content and import it back wherever it is required. (Please note this database is not attached to any SharePoint farm)

Go to START -> All Programs -> Microsoft SharePoint 2010 Products -> SharePoint 2010 Central Administration, on the left side click on the Backup and Restore link as shown below, then in the detail pane click on Recover data from an unattached content database under Granular Backup as shown below:

central administration

On the Unattached Content Database Data screen you need to specify the database server and database name which you have restored from the backup along with the authentication type and credentials to connect to that database. Next specify the type of action you want to perform.

sharepoint

On the Browse Content page select the site collection, site and then library/list to export. You can specify the type of operation you want to perform and depending on your selection, the next screen will vary.

browse content

On the Browse Content page I selected Backup site collection and here is my next screen. In the Site collection backup screen we need to select the name of the site collection which we want to backup, specify the location and name of the backup file and whether we want to overwrite a file if it exists with same name on the specified location.

change site collection

On the Granular Backup Job Status screen, you can see the status of the site collection backup operation. This page gets refreshed automatically in every 30 seconds though you can refresh it on-demand by clicking on the Refresh link as shown below (You need to make sure SharePoint 2010 Timer windows service is running before taking site collection backup):

central administration

Instead of taking a backup of the complete site collection, you can choose to export any specific site/library/list. Select Export site or list on the Browse content screen as shown below:

browse content

On the Site or List Export screen, select the site collection which contains the required site, then select the required site and then the library/list along with the location and the name of the file to be created after the export. You can choose to export the associated security with the site which includes users, authors of the documents, and when the document was created and modified, etc. Next you can specify what versions of the documents or list items you want to export, whether to export current versions, last major versions, last major or minor versions or all versions:

site or list export

On the Granular Backup Job Status screen, you can see the status of the content export operation.

backup job status

Restoring the site collection from the backed up file using PowerShell and STSADM tool

PowerShell: Starting with SharePoint 2010 we can use PowerShell cmdlets for almost every administrative task in SharePoint. To launch the SharePoint 2010 PowerShell console, go to:

START -> All Programs -> Microsoft SharePoint 2010 Products -> SharePoint 2010 Management Shell and click on it. To restore the site collection backup we need to use the Restore-SPSite command and specify the location where the backup is available, and the URL of the site collection. The Force parameter is used to overwrite a site collection with the same name, if it exists, during restore operation:

Restore-SPSite -Identity http://mktaraliw2k8r2:8081 
-Path \\MKTARALIW2K8R2\SharePointBackupFolder\SiteCollectionForUCR.bak 
-Force
management shell

STSADM Tool: STSADM is a SharePoint administrative tool which generally resides in the "C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN" folder in a SharePoint 2007 environment and in the "C:\Program Files\Common Files\Microsoft Shared\web server extensions\14\BIN" folder for a SharePoint 2010 environment. This tool is to be run SharePoint server, by someone who is a member of the Administrators group. Execute the command (below) to restore the backed up site collection to the SharePoint web application. The Overwrite switch is required to overwrite a site collection if it is already existing with the same name. To learn about the parameters of this command, click here.

stsadm -o Restore -URL http://mktaraliw2k8r2:8081 -Filename \\MKTARALIW2K8R2\SharePointBackupFolder\SiteCollectionForUCR.bak -Overwrite
command prompt

Restoring the site/library/list from the exported file using PowerShell and STSADM tool

PowerShell: To import the site/library/list from the exported file we need to use the Import-SPWeb command and specify the location where the exported file is available and the URL of the site/library/list to restore. The Force parameter is used to overwrite a content object with the same name, if it exists, during the import operation:

Import-SPWeb -Identity http://mktaraliw2k8r2:8081/hr -Path \\MKTARALIW2K8R2\SharePointBackupFolder\SiteExportForUCR.cmp 
-Force
management shell

STSADM Tool: To import the site/library/list from the exported file we need to use the STSADM -O Import command and specify the location where the exported file is available and the URL of the site/library/list to restore. The Quiet switch is used to suppress the detailed messages generated on the screen during the command execution:

stsadm -o Import -URL http://mktaraliw2k8r2:8081/hr -Filename \\MKTARALIW2K8R2\SharePointBackupFolder\SiteExportForUCR.cmp 
-Quiet
command prompt

Notes

  • Please note that you can still use old STSADM (SharePoint Administrative) tool to perform backup/restore and export/import operations but it is recommended not to use this tool (unless you are forced to use it for backward compatibility) as this tool has been deprecated and provided in SharePoint 2010 only for backward compatibility. Instead use PowerShell cmdlets. To learn more about mapping between STSADM and PowerShell cmdlets, click here.
  • You need to make sure the SharePoint 2010 Timer windows service is running before taking any site collection backup or exporting a site/library/list using Central Administration.
  • Workflows are not included when we export/import sites, libraries, or lists.
Next Steps


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Arshad Ali Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools