We at SQL Server support team, continue to invest our time in writing tools and utilities which can aid you in troubleshooting SQL Server issues. SQL Server Backup Simulator is one such tool which will help you while troubleshooting issues in taking backup of SQL Server using 3rd party utilities like IBM Tivoli, Symantec BackupExec, Quest Litespeed, Redgate SQL Backup etc., which use sqlvdi.dll to communicate to SQL Server.
Let me tell you the thought process of bringing this tool to you all. We have been shipping simple.exe along with SQL Server samples which you can use to test backup of SQL Server databases using VDI but there is no tool available for you to troubleshoot VDI backup/restore issues. So necessity of such a tool was mother of the need for coding SQL Server Backup Simulator.
This tool does nothing magical, all it does is perform validation of the SQLVDI infrastructure and takes a sample backup to verify if everything from a SQLVDI perspective is working correctly for a 3rd party backup application to perform a VDI backup of a SQL database.
Steps to perform validation:
Launch this application using the service account credentials of the 3rd Party backup restore application so that we can perform validation and test backup/restore using those security credentials.
Once you launch the application, it reads all local SQL Server instances and gives you a choice to select one to perform validation:
Once you select the instance and hit “Validate VDI installation” it performs these steps:
1. Read the Operating System architecture
2. Read the SQL Server installation architecture
3. Read SQL Server build
4. Verify whether the login connected to SQL Server has sysadmin privilege
5. Read sqlvdi.dll loaded into SQL Server memory
6. Read version information about sqlvdi.dll located in %PROGRAMFILES% & %PROGRAMFILES86%
7. Verify whether the build numbers of all these dll’s are matching
You might be wondering as why we do these checks. Based on the history of issues we have seen, there are many different errors occur during SQLVDI backup/restore but the root cause would be that sysadmin permission is missing or version of sqlvdi.dll loaded into SQL Server is different from what client application is using etc.,.
So this GUI tool will help users determine whether the SQLVDI.DLL is functioning correctly and where you need to concentrate your troubleshooting efforts in case of SQL backup/restore operations are failing due to VDI errors.
Steps to perform Backup:
Once you complete the validation, switch to the Simulate window and select Backup or Restore and then select the database from the pre-loaded list.
Select the folder location where SQL Server Backup Simulator can store/read the backup file “SQLBackupSim.bak” (This file name is hardcoded)
Before clicking Start, you can enable the checkbox “Use 64-bit Process” to use 64-bit client exe to connect to SQL Server which loads 64-bit sqlvdi.dll. If you don’t enable this checkbox, we connect to SQL Server using 32-bit client exe which will load 32-bit sqlvdi.dll.
Here is a screenshot of Backup/Restore simulation:
We don’t guarantee that all your sqlvdi issues can be resolved using this tool but what we can assure you is that this tool will provide a value-add in troubleshooting sqlvdi backup/restore failures because if backup/restore works fine with this tool, they you can focus on the backup application code which is reporting the failure.
1. Backup path should not contain blank spaces.
2. Application crashes when attempting to take a backup of a SQL instance running in WOW (32-bit SQL on 64-bit server) using the 32-bit version of SQLVDI.DLL
We are planning to provide compression features etc.., in the next release of this tool.
Please feel free to download this tool and post your comments/feedbacks/questions/issues at http://code.msdn.microsoft.com/sqlbackupsim
To know more about VDI, download 'SQL Server 2005 Virtual Backup Device Interface (VDI) Specification' from http://www.microsoft.com/downloads/details.aspx?familyid=416f8a51-65a3-4e8e-a4c8-adfe15e850fc&displaylang=en
Regards, Sakthivel Chidambaram SE, Microsoft SQL Server Support
Reviewed by, Amit Banerjee SEE, Microsoft SQL Server Support
Cumulative update for tool is now available in the v1.2 version:
Details at troubleshootingsql.com/.../sql-server-backup-simulator-cumulative-update