Is SQLVDI.DLL functioning properly?

Every once in a while I see VDI backups failing and questions being asked as to whether the sqlvdi.dll (which exposes the Interfaces to perform the backup) or the Software being used to take the backup is responsible or is it an issue with the Environment?

In case you need information on how VSS and VDI backups work, refer one of the earlier posts on this blog.

One or more of the following error messages may be logged in the SQL Server error log when taking backups using VDI:

Error message 1
2007-06-18 11:21:00.83 spid820 BackupVirtualDeviceFile::ClearError: failure on backup device 'VDI_ DeviceID '. Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.).
Error message 2
2007-06-18 11:21:00.83 spid820 Error: 18210, Severity: 16, State: 1.
2007-06-18 11:21:00.83 spid820 BackupMedium::ReportIoError: write failure on backup
device 'VDI_ DeviceID '. Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.)
Error message 3
2007-06-18 11:21:00.87 spid820 Error: 18210, Severity: 16, State: 1.
2007-06-18 11:21:00.87 spid820 BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup device 'VDI_ DeviceID . Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.)

The first thing that you need to check is if the fix for SQLVDI.DLL is already applied on the server.
RELATED KB ARTICLES:934396 FIX: Error messages and an event may be logged when you use the Virtual

Device Interface in SQL Server 2005 or in SQL Server 2000
https://support.microsoft.com/default.aspx?scid=kb;EN-US;934396
935465 An updated version of Sqlvdi.dll is now available for SQL Server 2000
https://support.microsoft.com/default.aspx?scid=kb;EN-US;935465

Well, the first can be addressed in a fairly simple manner. SQL Server 2000 installation media has SQL Server 2000 has sample applications for the same purpose. The samples folder under the SQL 2000 installation CD: devtools\samples\backup has a zip file called unzip_backup.exe. If you extract this, you will find two folders with cpp files in them called "simple" (for taking VDI backups) "osimple" (for taking VDI backups using ODBC connection. Further documentation about the VDI interfaces can be found in the vbackup.chm file which is also present in the zip file.

The sample application is designed to create a backup using SQLVDI.DLL for the PUBS database. This is a sample database that gets installed if you install Sample databases for SQL Server 2000. If the backup/restore operation using this file is successful, then the SQLVDI.DLL is functioning properly. If the SQLVDI dll is functioning properly and VDI backups are still failing, then the system/application event logs & SQL Server ERRORLOGs inspection should give us an idea regarding the error codes and the possible reasons of failure.

The VDI error codes can be found in the VDIERROR.H file. The simple.cpp file available in the samples can be modified as per your needs to customize the testing in your environment. You would need three header files to build the simple.exe:

· VDIERROR.H

· VDIGUID.H

· VDI.H

These three header files are available under devtools\include folder of the SQL Server 2000 installation media. The Virtual Backup Device Specification in SQL Server 2000 SP2 contains updated information on the VDC_PrepareToFreeze command. An updated version of the Virtual Device Interface header file (vdi.h) can be found at \devtools\include in the SQL Server 2000 Service Pack 4 setup directory.

I’m going to demonstrate how to modify the simple.cpp file to do the following:

1. Take in a SQL Server name and the database name as command line parameters

2. Modify the CreateEx function call to account for non default instances

3. Factor in the possibility that only SQL Server 2000 or SQL Server 2005 client tools might be available

First modify the main function to account for the extra parameters and also update the usage information to account for the change in the parameter list:

if (argc == 4)

    {

        if (toupper(argv[1][0]) == 'B')

        {

          doBackup = TRUE;

            badParm = FALSE;

        }

        else if (toupper(argv[1][0]) == 'R')

        {

            doBackup = FALSE;

            badParm = FALSE;

        }

                        servername = "";

                        printf ("Connecting to Server: %s\n",argv[2]);

                        dbname = "";

                        printf ("Backing up database: %s\n",argv[3]);

    }

if (badParm)

{

        printf ("usage: simple {B|R} <server name> <database name>\n"

 "Demonstrate a Backup or Restore using the Virtual Device Interface\n");

        exit (1);

}

Now we need to modify the CreateEx function. Replace the existing CreateEx code with the code below:

//Adding code to account for a box which doesn't have a Default instance of SQL Server

           

            char* pShortInstance = strchr (argv[2], L'\\');

           

            if (pShortInstance)

            {

                        pShortInstance++;

                        printf ("\nTrying to backup database from a named instance");

                        WCHAR wInstanceName [128];

                        int rc;

                        rc = MultiByteToWideChar (CP_ACP, 0,pShortInstance, strlen (pShortInstance),wInstanceName, 127);

                        wInstanceName [rc] = 0;

                        hr = vds->CreateEx (wInstanceName, L"SUPERBAK", &config);

                        if (!SUCCEEDED (hr))

                        {

                                    printf ("VDS::Create fails: x%X", hr);

                                    goto exit;

                        }

            }

            else

            {

                        printf ("\nTrying to backup database from a default instance");

                        hr = vds->CreateEx (NULL, L"SUPERBAK", &config);

                        if (!SUCCEEDED (hr))

                        {

                                    printf ("VDS::Create fails: x%X", hr);

                                    goto exit;

                        }

            }

If we do not account for a named instance and if you use the existing code to backup a database on a server which has no default instance and the first parameter passed to CreateEx function is NULL, then you would get an error as follows:

VDS::Create fails: x80770007

0x80770007 (VD_E_INSTANCE_NAME VD_ERROR) signifies failed to recognize the SQL
Server instance name. The documentation for CreateEx mentions the following:

The CreateEx method should be called only once per BACKUP or RESTORE operation. After invoking the Close method, the client can reuse the interface to create another virtual device set.

The instance name must identify the instance to which the T-SQL is issued. NULL identifies the default instance. No "machineName\" prefix is accepted.

The SIMPLE.CPP file can be modified as per your needs. This file takes a backup
using SA account for the PUBS database for a default instance of SQL. You will have
to modify the function that is taking the backup to take in the server name and database name as
follows:

int execSQL (int doBackup,char* servername,char* dbname)

The call to the execSQL function would also have to be modified as below:

hProcess = execSQL (doBackup,argv[2],argv[3]);

Now we need to add code in the execSQL function for the Backup and Restore commands.

            //BUILD the BACKUP command

            char str_b[100] = "";

            strcat (str_b,"-Q\"BACKUP DATABASE ");

            strcat(str_b, dbname);

            strcat(str_b," TO VIRTUAL_DEVICE='SUPERBAK'\"");

           

            //Build the RESTORE command

            char str_r[100] = "";

            strcat (str_r,"-Q\"RESTORE DATABASE ");

            strcat (str_r, dbname);

            strcat (str_r," FROM VIRTUAL_DEVICE='SUPERBAK'\"");

Also, if you want to use Windows Authentication, then you would have to modify the
ISQL/OSQL/SQLCMD string being passed in the code as follows:

rc = _spawnlp( _P_NOWAIT, "osql", "osql", "-E", "-b","-S",servername,

        (doBackup) ? str_b : str_r, NULL);

The second and third parameters would depend on the following:

a. If you have OSQL command line utility on the server, you would want to use osql.

b. If you have SQL Query Analyzer on the server, you would want to use isql.

c. If you have SQLCMD command line utility on the server, you would want to use sqlcmd.

If you do not have a client component installed on the server that you are passing as the command for spawning off the utility, then you would end with an error as follows:

Sending the SQL...
Spawn failed with error: 2
execSQL failed.

In the application log, you will see the following error:
Event Type: Error
Event Source: SQLVDI
Description:
SQLVDI: Loc=CVDS::Close. Desc=Abnormal termination state. ErrorCode=. Process=4644.
Thread=4540. Client. Instance=. VD=Global\SUPERBAK_SQLVDIMemoryName_0.

If you want to use SQL Authentication, then you need to use –U and –P parameters instead of –E above line.

This utility would create a backup on the same directory where the simple.exe resides with the name superbak.dmp.

Amit Banerjee
Technical Lead, Microsoft Sql Server