This is part 2 of granular backup/restore blog in which I’ll cover the Recover data from an unattached content database option in Central Administration. This is located in backup/restore section under granular backup/restore. SharePoint 2010 utilizes SQL snapshots both to create and to restore from. This blog will also cover the overall uses of snapshots since they can be used in multiple ways. You can be has granular as possible in choosing what you want to restore from snapshots whether it’s the entire site collection or a specific document library\list.

clip_image002

 

Snapshot Basics

Before going though the steps it’s important to know the basics of SQL snapshots. SQL snapshots were introduced in SQL 2005 timeframe and only available to Enterprise and Developer editions of SQL. SQL 2008 maintains this rule in that snapshots are only available to Enterprise and Developer editions of SQL. SQL snapshots are a read-only copy of a data base as it existed at snapshot creation. Snapshots operate at the DB page level so when a page is about to be modified for the first time in the source DB, the page is first copied to the snapshot thus preserving the data record. If the page already exists and has been modified post snapshot, it’s not updated or copied to the snapshot. At snapshot creation, all pages from source db are considered new and are copied to the snapshot. Snapshots are linked to the source database where they originated. If the source database goes offline for any reason, then the snapshot is unavailable. This is why snapshots are great for granular backup/restore operations but this shouldn’t be your main backup/restore method. Keep in mind that snapshots are limited to content databases only. To learn more about snapshots check out the following:

http://msdn.microsoft.com/en-us/library/ms175158.aspx

 

What can SharePoint 2010 Administrators do with snapshots?

SharePoint 2010 Administrator can now do common administrative tasks when it comes to snapshots. The following lists some common things which can be performed on a SharePoint 2010 server using PowerShell.

· Create snapshots

· Delete snapshots

· Restore snapshot

· Use snapshots for granular recovery operations <- Can also use Central Administration

Note: You cannot create, delete, or restore snapshots on SharePoint 2010 standalone server due to the fact it runs on SQL 2008 express edition. Snapshot operations are not supported against SQL server express edition.

 

Creating Snapshots

Two common methods exist for creating snapshots of SharePoint 2010 data.

 

Method 1: Using SQL Server

Snapshots are created via TSQL commands within SQL Management Studio query window. SQL Management Studio doesn’t expose any UI for creating snapshots. If my content database is named "WSS_Content" then I would run the following query from SQL Management Studio:

CREATE DATABASE ContentSnapshot on (NAME = "WSS_Content", Filename = 'c:\snapshot\contentsnap.ss') as SNAPSHOT OF "WSS_Content";

Once this is completed, the snapshot resides in the snapshots folder.

clip_image004

 

Method 2: Using PowerShell aka “SharePoint Management Console”

SharePoint Management Console can also be used to create snapshots. Yes, this is a fancy way of saying powershell but get used to hearing it since it’s listed that way on the start menu. You can now create SQL snapshots from a SharePoint 2010 server. Using SharePoint Management Console you can input something like the following:

$ContentDB = get-spcontentdatabase WSS_Content

$ContentDB.Snapshots.CreateSnapshot()

Below, a new snapshot is created within SQL Management Studio:

clip_image006

 

 

Recover Data from an unattached content database

The recover data from an unattached content database option is used for pulling data out of a snapshot or detached content database and exporting to a file. The exported file can be imported back into production using the sp-import cmdlet via powershell. Both Central Administrator and Powershell can be used to pull data out of a snapshot. Three specific operations exist depending on what specifically you want to pull out of the snapshot. Selecting the recover data from an unattached content data base option exposes the following operations.

clip_image008

Browse Content – Provides ability to browse for a specific site collection, Site, or list. Once a selection is made, the same operations are available which is backup site collection or Export site or list.

Backup Site collection – Provides the ability to backup a site collection from a snapshot which can then be restored into production

Export Site or List – Provides the ability to export a specific site, list, or document library from a snapshot

 

 

Walkthrough

Using Granular backup/restore to pull a document library from a snapshot and import into production

 

Using Central Administrator

This example demonstrates exporting a document library named hr out of a snapshot named “contentsnapshot2”:

1.) Within Central Administration, Select backup/restore, Recover Data from an unattached content database

2.) The following screen is where the SQL server name is populated and the corresponding snapshot name:

clip_image010

After filling out required fields and selecting Export site or list click next.

3.) After clicking next, drill down to specified list as well as includes options for exporting security, version, and specify export path.

clip_image012

Select the start export button to pull the data from the snapshot into the export.cmp.

4.) Finally, run the import-spweb cmdlet via powershell to import the data back into production. For these steps, see the previous nugget.

 

Using Powershell

This example demonstrates exporting a document library named hr out of a snapshot named “contentsnapshot”:

1.) Get the snapshot database and assign it to variable named $snappy:

$snappy = get-spcontentdatabase -ConnectAsUnattachedDatabase -DatabaseServer contososql -DatabaseName contentsnapshot

2.) Pipe $snappy with export-spweb cmdlet to pull hr list out of the snapshot:

$snappy | export-spweb http://contosoweb –usesqlsnapshot –itemurl /hr –path \\contososql\bu\mybackup.cmp

3.) Finally, run the import-spweb cmdlet via PowerShell to import the data back into production. For these steps, see the part 1 of granular backup/restore blog.