Programmatic Administration of Database Snapshots

Article
10/20/2016

Applies to: SharePoint Foundation 2010

This topic explains how you can incorporate database snapshots into your custom backup and data recovery solutions.

Database Snapshot Management

If the Microsoft SharePoint Foundation server farm uses either the Enterprise or Developer editions of Microsoft SQL Server, farm administrators can take advantage of the snapshot feature of those editions. The snapshots can be managed in SQL Server, but the SharePoint Foundation API also provides a set of classes that can be used, in conjunction with timer jobs (SPTimerService, and SPJobDefinition), to manage the snapshots. Your solution can programmatically determine the frequency with which snapshots are created, how long each one lives before it is automatically deleted, and how many snapshots are maintained at any one time.

Note

An SQL Server snapshot is not an actual copy of an entire database. When initially created, a snapshot is empty. Each time data is written to the original database, the previous value is written to the snapshot. When a snapshot is accessed, SQL Server treats the snapshot and the unchanged data in the original database as though they were a single database. Thus, the snapshot, in conjunction with the unchanged data in the original database, is effectively a record of the database as it was at the time the snapshot was created. For more information, see Database Snapshots.

Making your Custom Databases Restorable from a Snapshot

If your company’s SharePoint Foundation solution includes a supplemental database that is hosted by the Enterprise or Developer editions of SQL Server, you can enlist the database into the SharePoint Foundation infrastructure by designing the class that represents it to implement the IDatabaseSnapshotRestore interface. For details about how to do this, see How to: Create a Database Class That Can Be Restored From a Snapshot.

Snapshot Management APIs

There are two critical classes in the SharePoint Foundation object model used for snapshot management. They are discussed in the next two subsections.

SPDatabaseSnapshot

Objects of this type represent snapshots. They exist only as members of a SPDatabaseSnapshotCollection collection which itself exists only as the value of the SPDatabase.Snapshots property. The critical members of the SPDatabaseSnapshot class are summarized here. See the reference topics for each (and for the other members of the class) for more information.

Delete() – As the name indicates, this method deletes the snapshot. It can be called on an ad hoc basis, but usually it is called by a timer job that will check at intervals to see if the snapshot has reached its maximum allowed age (MaximumRetention), or if the maximum allowed number of snapshots (SnapshotLimit) has been exceeded and the snapshot is the oldest snapshot for the database.
Restore() – This method overwrites the database with the snapshot and then deletes all existing snapshots for the database. One overload of the method gives calling code the option of forcing an overwrite, even if the database does not implement IDatabaseSnapshotRestore.

SPDatabaseSnapshotCollection

The critical members of the SPDatabaseSnapshotCollection class are summarized here. See the reference topics for each (and for the other members of the class) for more information.

CreateSnapshot() – This method causes SQL Server to create a snapshot. With each subsequent write operation to the database, the database server writes the original value to the snapshot. The method also sets EnabledManagement to true.
DeleteSnapshots()- This method deletes all the existing snapshots for the database.
RefreshSnapshots() – This method synchronizes the SharePoint Foundation content database and the configuration database with respect to snapshots, to ensure that all and only the snapshots that actually exist for the content database in SQL Server are registered in the configuration database. As a general rule, you should call this method just before your code accesses a snapshot for any reason, such as with an indexer, "sfdb.Snapshots[0]", where sfdb is an SPDatabase object. However, the CreateSnapshot() and DeleteSnapshots() methods each call RefreshSnapshots() themselves.
EnabledManagement – This property, when true, indicates that SharePoint Foundation built-in timer service jobs that manage snapshots should include the parent database in their management; for example, the job would typically create and delete snapshots for the database at specified intervals. When the property is false, such jobs ignore the database. If your SharePoint Foundation solution includes your own snapshot management timer jobs for certain databases, you must set EnabledManagement to false for those databases or two sets of timer jobs will be creating and deleting snapshots of the same database in conflict with each other. The CreateSnapshot() method sets this property to true.
CreationInterval – This property specifies how frequently (in hours) a new snapshot of the parent database is created.
MaximumRetention- This property specifies how long (in hours) a snapshot lives until it is deleted.
SnapshotLimit – This property specifies the maximum number of snapshots for the parent database that are allowed to exist at any one time.

Configure a Database’s Snapshots for Management

To set up a database to be managed by the built-in snapshot management timer job that ships with SharePoint Foundation, run code that does the following.

Set the EnabledManagement property to true.
Set the values of the CreationInterval, MaximumRetention, and SnapshotLimit properties.
Call the UpdateParent() method of the SPDatabaseSnapshotCollection object.

To set up a database to be managed by a custom snapshot management timer job, you take the same steps except that you set the EnabledManagement property to false.

Restore from a Snapshot

The major steps to restore a database from a snapshot programmatically are as follows:

Get a reference to the SPDatabase object that represents the database.
Call RefreshSnapshots() to make sure you are working with the current set of snapshots for the database. For example, "sfdb.Snapshots.RefreshSnapshots();" where sfdb is a reference to the SPDatabase object.

Get a reference to the snapshot that you want to use for the restoration. For example, to get a reference to the most recent snapshot, you could use the following lines

Int32 numberOfSnapshots = sfdb.Snapshots.Count;
SPDatabaseSnapshot spDBSnapshot = sfdb.Snapshots[numberOfSnapshots-1];

Dim numberOfSnapshots As Int32 = sfdb.Snapshots.Count
Dim spDBSnapshot As SPDatabaseSnapshot = sfdb.Snapshots(numberOfSnapshots-1)

Call one of the Restore() methods for the snapshot. The following is an example.
```
spDBSnapshot.Restore();
```
```
spDBSnapshot.Restore()
```

Implement Your Own Snapshot Management Timer Job

If you want to develop a custom snapshot management application with the SharePoint Foundation object model, you can derive your own class from the SPJobDefinition class. The main task is to create an override of the Execute(Guid) method. In general, you want this method to do the following:

Get a reference to the SPDatabaseServiceInstance object that hosts the databases.
Iterate through all the members of the Databases property of the object and do the following:
- Check the value of sfdb.Snapshots.EnabledManagement, where sfdb is a reference to the SPDatabase object, and do nothing if the value is true. (If it is true, then the built-in snapshot management timer jobs of SharePoint Foundation are going to manage the snapshots for the database.)
- For each of the other databases, refresh its list of snapshots with a call to RefreshSnapshots() and then delete any whose Age is greater than the value of the MaximumRetention property.
- Make another call of RefreshSnapshots() and then compare the age of the most recent snapshot (sfdb.Snapshots[sfdb.Snapshots.Count-1].Age) with the value of CreationInterval. If the former is equal to or greater than the latter, then create a new snapshot with a call to CreateSnapshot().
- Make another call of RefreshSnapshots() and then check to see if the total number of snapshots is greater than SnapshotLimit. If it is, then delete enough snapshots to bring the total down to the limit. Delete the oldest snapshot first (sfdb.Snapshots[0]), then the next oldest (sfdb.Snapshots[1]), etc.