VDI for SQL Server: backup without .bak files | Guide

# VDI Integration: Building MS SQL Backups Without .bak Files

Standard BACKUP DATABASE commands in MS SQL Server are limited to local disk storage. For integration with external backup systems, VDI is required—an interface that allows intercepting the data stream directly from SQL Server. We break down the implementation of the VDI client in the "Beresta" project, which provides a full backup and restore cycle without intermediate .bak files.

Why VDI is Needed in Modern Backup Systems

Stock SQL Server backup mechanisms via TO DISK don't address the needs of external data protection systems. When job lifecycle management, data routing, parallelism, or integration with object storage is required, the need for an intermediate layer arises. VDI (Virtual Device Interface) serves as such a bridge: SQL Server continues executing standard BACKUP and RESTORE commands, but instead of physical files, it works with virtual devices managed by an external application.

Key advantages of the VDI approach:

• Full control over the data stream—direct writing to cloud storage or media server
• Independence from SQL Server's file system
• Support for striped operations to boost throughput
• Single point for error handling and logging
• Compliance with enterprise backup system requirements for storage policy management

Unlike simplified solutions, the "Beresta" project implements VDI as a full-fledged transport adapter between SQL Server and the storage backend, maintaining compatibility with versions from 2008 to 2022.

VDI Client Architecture: From Preparation to Execution

Implementation is divided into two logical stages. In the preparation stage, parameters are validated, connection to SQL Server occurs via ODBC, and a task set is formed. Critically important is that the project supports multiple ODBC drivers (including current Driver 17/18), which boosts compatibility in heterogeneous environments.

During execution, the VDI stream is activated:

• COM initialization via CoInitializeEx
• Creation of the IClientVirtualDeviceSet2 object
• GUID generation for virtual devices
• VDConfig parameter setup (block size, timeout, buffer depth)
• Device publication via CreateEx
• Launch of the T-SQL command specifying VIRTUAL_DEVICE
• Data processing in worker threads

The central component is transferThreadProc, which implements the exchange logic with SQL Server:

while (true) {
    VDC_Command* command;
    device->GetCommand(&command);
    
    switch (command->dwCommandCode) {
        case VDC_Write:
            WriteToFile(command->pvBuffer, command->dwByteCount);
            break;
        case VDC_Read:
            ReadFromFile(command->pvBuffer, command->dwByteCount);
            break;
        case VDC_Flush:
            FlushBuffer();
            break;
    }
    device->CompleteCommand(command, S_OK);
}

This loop ensures synchronous interaction: SQL Server acts as the producer of block data, while the VDI client acts as the consumer, redirecting data to the target storage.

Parallelism and Performance Management

Single-threaded processing is a bottleneck for enterprise workloads. The project addresses this through striped backup/restore, where each stripe is handled by a separate thread. Specifying the --stripes=N parameter creates files like:

db_full_20260412_101530_s0.bak
db_full_20260412_101530_s1.bak
...

This approach offers triple benefits:

• Distribution of I/O load across disks or network channels
• Linear scalability of throughput
• Alignment with the architecture of industrial backup systems featuring multiple streams

Key parameters affecting performance:

• blockSize—optimal block size for the target storage
• maxTransferSize—maximum transferable packet size
• buffersPerStripe—buffer depth per thread
• vdiTimeoutSec—I/O operation timeout

Experiments with these parameters enable tailoring the solution to specific infrastructure characteristics—from local SSDs to slow network storage.

Restoring Chains: From Full to Log

The project supports the full restore cycle, including differentials and transaction logs. Correct chain handling is critically important:

• For differential backups, the last full backup with the same timestamp is automatically selected
• Full restore is performed with WITH NORECOVERY, followed by differential with WITH RECOVERY
• Log backups are allowed only for databases in FULL/BULK_LOGGED recovery models
• The master database is excluded from differential operations (SQL Server limitation)

The set discovery mechanism uses the naming convention:

{db}_{type}_{timestamp}_s{n}.bak

Directory scanning functions build restore chains based on timestamps. For full restore, the latest set is selected; for differential, only those with matching full and diff files. The stripe count is determined by the highest sN index in filenames.

Before restoration, the database is mandatorily switched to single-user mode:

ALTER DATABASE [db] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

This prevents conflicts with active connections. After successful restoration, the database is returned to MULTI_USER mode.

Key Takeaways

• VDI eliminates dependency on SQL Server's file system, redirecting the data stream to any target storage
• Support for full/diff/log scenarios with automatic restore chain construction
• Parallel processing via striped architecture ensures linear throughput scalability
• Compatibility with SQL Server 2008-2022 without binding to a specific ODBC driver
• Implementation accounts for SQL Server specifics (e.g., master database limitations)

The "Beresta" project shows how even a relatively compact VDI client implementation can fulfill key enterprise backup system requirements. Focus on the full operation cycle (including error handling and database state management) makes it production-ready. The architecture is open for integration with any storage backend—from local disks to cloud object storage—which is especially relevant in hybrid IT landscapes.

— Editorial Team