SQL Server Snapshot backup and restores - Part 2

In my last blog entry,I gave you some insights on my project objective. Let’s call this project as SQLSNAP (SQL Snapshot). In  VDI application development, the actual work of writing the backup metadata and taking backups is done by the VDI application. SQL Server would be doing the work as instructed by VDI app. So we have to be careful in our code and logic.

Consider following sequence of logic for SQLSNAP. we will rectify as and when necessary in subsequent posts.

1. On startup, SQLSNAP does certain initializations (for VDI backups)

2. SQLSNAP connect to local SQL Server instance.

3. Just like any other backup, a header file is created (you could do a RESTORE HEADERONLY on it) by SQL Server, the responsibility to write the metadata (and taking actual disk snapshot) lies with SQLSNAP.

4. SQL puts database IO in freeze mode (via VDI calls made by SQLSNAP).

6. SQLSNAP makes the disk level snapshots. This is a storage level work and will not be touched here. We will simply call a batch file that does storage level work.  In my case, I am working on a Windows 7 laptop. so I will make use of the VSS to create “Previous versions” of my C: 

for large production database systems running on SANs, you should use tools specific to your SAN for making snapshots.

7. on completion of the snapshot, SQLSNAP informs SQL Server about backup completion. SQL Server resumes the IO on server.

And Our backup gets completed.

Sounds simple.  Lets get it coded !!

First, Download the SQL Server 2005 Virtual Backup Device Interface (VDI) Specification from Microsoft. Run the downloaded EXE to extract few files onto your development machines.  you will see few folders and a help file extracted. The vbackup.chm provides descriptions of each of the functions. The descriptions include the following information:

• Function purpose
• Function syntax
• Parameter list
• Return values
• Remarks

The other folders are sample code for you to study and develop your custom applications.

so lets open the snapshot sample in more details.

open the snapshot.cpp file in notepad or in your favorite C++ editor (such as Visual Studio). I am using VS2010 for this.  Firstly note the initial comments in the source code. The same are applicable on to the modifications done/shown on this blog. 

 // This source code is an intended supplement to the Microsoft SQL
// Server online references and related electronic documentation.
//
// This sample is for instructional purposes only.
// Code contained herein is not intended to be used "as is" in real applications.
// 
// snapshot.cpp
//
// This sample extends the "osimple.cpp" sample to demonstrate BACKUP WITH SNAPSHOT.
// It is not fully functional.  The ability to take/mount snapshots must be provided.
//
// This is a sample program used to demonstrate the Virtual Device Interface
// feature of Microsoft SQL Server together with an ODBC connection.
//
// The program will request backup or restore of a single database
// on some instance of sql server.

 
Let’s go over the rest comments inside the main function of the code.

     // Check the input parm

// Initialize COM Library
// Note: _WIN32_DCOM must be defined during the compile.

     // Get an interface to the device set.
    // Notice how we use a single IID for both the class and interface
    // identifiers.

     // Setup the VDI configuration we want to use.
    // This program doesn't use any fancy features, so the
    // only field to setup is the deviceCount.
    //
    // The server will treat the virtual device just like a pipe:
    // I/O will be strictly sequential with only the basic commands.

     // Create a GUID to use for a unique virtual device name

     // Create the virtual device set
    // Notice that we only support unicode interfaces

     // Send the SQL command, by starting a thread to handle the ODBC

     // Wait for the server to connect, completing the configuration.

     // Open the single device in the set.

 now Logic moves to performTransfer function. following comments from performTransfer function.

 // VDI data transfer handler.
//
// This routine reads commands from the server until a 'Close' status is received.
// It simply reads or writes a file 'superbak.dmp' in the current directory.

and few more inside a while loop. I am not pasting them here.

A typical database backup command has following syntax

 BACKUP DATABASE DBNAME TO DISK=’FILENAME’

Just doing a simple search on the file about word “DATABASE “, I found following line inside a function named  “SQLRoutine”

// Generate the command to execute
//
sprintf (sqlCommand, "%s DATABASE [%s] %s VIRTUAL_DEVICE='%s' WITH SNAPSHOT",
    parms->doBackup ? "BACKUP": "RESTORE",
    parms->pDbName,
    parms->doBackup ? "TO": "FROM",
    aVdsName);

So as we see, the majority of work that we intend to do is done in this 3 functions. main, performTransfer and SQLRoutine.  There are few other functions as well.

In my next blog post, I will go deeper into the sample code and modifications.