SharePoint 2010 : The SharePoint Object Model (part 3) - Programmatically Using SQL Snapshots

Programmatically Using SQL Snapshots

The SPDatabase type in the Microsoft.SharePoint.Administration namespace represents a SQL Server database, and SharePoint uses SPDatabase and its derived types to read, write, and manipulate the contents of databases that are used by the farm. Particularly noteworthy among the SPDatabase-derived types is the SPContentDatabase type, which represents a content database housing site collections.

In SharePoint 2010, the SPDatabase type has been extended with a Snapshots property. As shown in Figure 4, the Snapshots property exposes a collection of type SPDatabaseSnapshotCollection. Through the Snapshots collection and each of its SPDatabaseSnapshot items, it is possible to create, delete, and manage SQL Server snapshots for the database in the underlying SQL Server instance.

Figure 4. Database snapshot support for SPDatabase and derived types.

When a snapshot is created, either programmatically through the SharePoint object model or directly through SQL Server, that snapshot is treated as if it were a completely different readonly database on SQL Server. Although snapshots are commonly created from live SharePoint content databases, they are not attached to the SharePoint farm. Because the snapshots aren’t affiliated with a SharePoint farm, you need to use the new unattached content database model (through the SPContentDatabase.CreateUnattachedContentDatabase method) to interact with them.

Listing 4 demonstrates the same site collection backup operation that was shown in Listing 3, but it shows how you can use a database snapshot to enhance the overall backup process. By executing the SPSiteCollection.Backup operation against a database snapshot instead of the live content database, there is no need to lock the live database while the backup is being performed. Users can continue to conduct read and write operations as they normally would, and the backup operation can proceed against the snapshot without worries of corruption or inconsistencies. When the backup operation is complete, the snapshot is deleted to free any resources it held on SQL Server.

Listing 4.

public static void ExecuteSiteCollectionBackupWithSnapshot()
{
  // As with the ExecuteSiteCollectionBackup method, these variable values
  // would normally be supplied to the method rather than set here.
  String backupFilename = @"E:\Backup\SampleSiteBackup.bak";
  String siteCollectionUrl = @"http://spdev:18380";
  Boolean overwriteIfExisting = true;

  // These two variables are set with properties from the SPSite object that
  // is created. To minimize the size of the using block that follows, the
  // variables are scoped here.
  SPContentDatabase housingDb;
  String rootRelativeUrl;

  // Obtain the critical property values that are needed from the SPSite.
  using (SPSite siteToBackup = new SPSite(siteCollectionUrl))
  {
    // The content database that houses the site collection is needed to
    // generate a snapshot. The relative site collection URL
    // is needed for the eventual unattached DB backup operation.
    housingDb = siteToBackup.ContentDatabase;
    rootRelativeUrl = siteToBackup.ServerRelativeUrl;
  }

  // Only Developer and Enterprise versions of SQL Server support snapshots. If
  // snapshots aren't supported, the process can't continue.
  if (!housingDb.Snapshots.IsSnapshotSupported)
  {
     throw new NotSupportedException("Snapshots not supported.");
  }

  // Each SPDatabase-derived type (including SPContentDatabase) has a Snapshots
  // collection that serves as the gateway to working with snapshots. Refresh
  // the collection and then use it to create a new snapshot.
  SPDatabaseSnapshotCollection allSnapshots = housingDb.Snapshots;
  allSnapshots.RefreshSnapshots();
  SPDatabaseSnapshot hostingDbSnapshot = housingDb.Snapshots.CreateSnapshot();

  // Once SQL Server creates the snapshot, it's just like any other database.
  // SharePoint's unattached content database functionality is used to attach to
  // the snapshot as if it were a read-only content database.
  SPContentDatabase snapshotDb = SPContentDatabase.CreateUnattachedContentDatabase
(hostingDbSnapshot.ConnectionString);

  // With a valid SPContentDatabase reference, the collection of site collections
  // in the snapshot can be referenced and used to perform the site collection backup.
  SPSiteCollection sitesInSnapshot = snapshotDb.Sites;
  sitesInSnapshot.Backup(rootRelativeUrl, backupFilename, overwriteIfExisting);

  // When the backup operation is over, delete the snapshot to instruct SQL Server
  // to release the resources associated with it.
  hostingDbSnapshot.Delete();
}


					  

A parting word of caution regarding the use of snapshots is warranted. Listing 4 includes a call to the RefreshSnapshots method to ensure that the collection of SPDatabaseSnaphot objects is current prior to any collection manipulation activities. This is done because there are regular processes within SharePoint Foundation, such as the Microsoft SharePoint Foundation Snapshot Management timer job, that can create and delete database snapshots. Any SPDatabaseSnapshotCollectionEnabledManagement property value of true is subject to regular snapshot maintenance by SharePoint, and this that has an maintenance can lead to the addition and deletion of snapshots during the execution of your code. For this reason, it is prudent to refresh the contents of the collection through the RefreshSnapshots method before attempting any manipulation of the collection within your code.