Creating Database Snapshots Via SMO

The SMO (Server Management Objects) API is the best way to programmatically interact with SQL Server. On a recent project I had a requirement to create database snapshots on a regular basis using this technique. I’ve created a static C# wrapper class to look after the implementation, as some of the sample code I found online for this was pretty average.

There’s a few things to validate before the snapshot can be created:

• Database name and snapshot name must be different
• Database must exist
• Snapshot must not exist
• The SQL Server Edition must be Enterprise or Developer. (Standard doesn’t support snapshots)

Once those conditions are satisfied, snap away! Here's my validation code:

private static void ValidateSnapshotParameters(Server server, string databaseToSnap, string snapshotName)
{
    //Server Edition must be Developer or Enterprise
    if(server.EngineEdition != Edition.EnterpriseOrDeveloper)
        throw new SnapshotException("Snapshots are not supported in this edition of SQL Server. Enterprise or Developer edition is required.");

    //Database must exist
    Database db = server.Databases[databaseToSnap];
    if(db == null)
        throw new SnapshotException(string.Format("Specified Database does not exist: {0}", databaseToSnap));

    //Snapshot must not exist
    Database snapshot = server.Databases[snapshotName];
    if (snapshot != null)
        throw new SnapshotException(string.Format("A snapshot or database named {0} already exists", snapshotName));

    //Database and snapshot cannot be the same name
    if(databaseToSnap == snapshotName)
        throw new SnapshotException("Snapshot name must be different from Database name");
}

Below is the code to perform the actual snapshot. I recommend putting the validation method and the TakeSnapshot() method in a static class. Note that the snapshot must have the same filegroups and data files as the database being snapped. The implementation below looks after this. I've used the .ss file extension here, but it's an arbitrary choice according to MSDN. The most important line of code is:

snapshot.DatabaseSnapshotBaseName = databaseToSnap;

If you don't specify the DatabaseSnapshotBaseName property, then really all you are doing is creating a blank database!

public static void TakeSnapshot(Server server, string databaseToSnap, string snapshotName)
{
    try
    {
        ValidateSnapshotParameters(server, databaseToSnap, snapshotName);

        Database sourceDatabase = server.Databases[databaseToSnap];
        Database snapshot = new Database(server, snapshotName);
        snapshot.DatabaseSnapshotBaseName = databaseToSnap;

        //Create the same filegroups on the snapshot
        foreach (FileGroup fg in sourceDatabase.FileGroups)
        {
            snapshot.FileGroups.Add(new FileGroup(snapshot, fg.Name));

            //Add all of the files to the new filegroup
            foreach (DataFile file in fg.Files)
            {
                snapshot.FileGroups[fg.Name].Files.Add(new DataFile(snapshot.FileGroups[fg.Name], file.Name, Path.Combine(sourceDatabase.PrimaryFilePath, string.Format("{0}_{1}.ss", snapshot.Name, file.Name))));
            }
        }

        //Create the snapshot
        snapshot.Create();
    }
    catch (FailedOperationException ex)
    {
        //TODO: Logging
        throw;
    }
}

The snapshot operation completes in O(1) time, that is to say the time to complete the operation is not related to the size of the database. Finally, my implementation of a SnapshotException, which is a simple derivation of ApplicationException.

public class SnapshotException : ApplicationException
{
    public SnapshotException(string message) : base(message){}
}