SQLBackupRestore.com - Installation checklist

This document describes the common things to check to ensure that your installation/upgrade of the SQL Backup server components has been performed correctly.

SQL Backup server components

The SQL Backup server components consists of the SQL Backup service application and the SQL Backup extended stored procedure library.

SQL Backup service application

The service application file is named SQBCoreService.exe, and the default installation folder is a subfolder beneath the root SQL Backup installation folder. The name of the subfolder is the associated SQL Server instances' name. For the default instance, the subfolder is named (LOCAL).

install_01

Each instance of SQL Server that runs SQL Backup requires its own service. Note that a single license of SQL Backup allows you to install SQL Backup on all instances on the same physical machine.

SQBCoreService.exe uses the zlib compression algorithm contained in the file named zlib1.dll. This file needs to be present in the same folder where SQBCoreService.dll is located.

install_02

During the server components installation, the installer would have installed the related SQL Backup Agent service with the Windows Service Manager. Open the Windows Service Manager and verify this.

install_03

If the service is installed for a named instance, ensure that the service is started up with the -I parameter together with the instance name.

install_04

For a cluster, the -I parameter is not required for the default instance e.g.

install_05

A named cluster instance requires the -I parameter e.g.

install_06

If the -I parameter is missing for a named instance, you will need to add it manually. To do this, first stop the service. Then open the registry, locate the services' entry in HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services, and add the -I parameter to the ImagePath value, together with the instance name. Then start the service.

install_07

If the SQL Backup Agent service fails to start, first check the Event Viewer application log. Common errors are logged there.

install_08

If no errors are logged, the failure to start could then be due to many reasons. In these situations, we will require a trace file in order to identify the exact cause of the error. You will need to start the SQL Backup Agent service with a trace flag in order to generate a trace file. You can do this by adding the -sqbdebug parameter to the services' ImagePath registry value.

install_09

The service should now generate a trace file named SQBCoreService_log.txt in the folder where the service application is installed. You should send us this file when contacting us regarding service startup failures. You should remove this flag if you are not generating a trace file, as the file can get very large.

SQL Backup extended stored procedure library

The SQL Backup extended stored procedure library is named xp_sqlbackup.dll, and is installed in the associated SQL Servers instances' Binn folder.

install_10

SQL Backup registers the following extended stored procedures, which are contained in this library:

Name	Function
sqlbackup	The main extended stored procedure used to run backup and restore commands.
sqbstatus	Displays the progress of all active SQL Backup backup and restore processes, ran via the service. Command line processes are not displayed. Used by the SQL Backup GUI.
sqbmemory	Displays a snapshot of the SQL Server memory space. Primarily used to detect low contiguous memory conditions.
sqbdir	Used to browse local and network directory structures. Used by the SQL Backup GUI.
sqbutility	Used to run various functions for the SQL Backup GUI.
sqbdata	Used to retrieve data from the SQL Backup data store. Again used primarily by the SQL Backup GUI.
sqbtest	Used to run a compression comparison test. Used primarily by the Compression Analyzer.
sqbteststatus	Displays the progress of the compression comparison test. Used primarily by the Compression Analyzer.
sqbtestcancel	Used to cancel the compression comparison test. Used primarily by the Compression Analyzer.

A common issue during upgrading is that the xp_sqlbackup.dll file has not been upgraded. This is more common with SQL Server 2000 instances'. In these cases, SQL Server could not free the library from its memory space, and the installer then cannot replace the file. In these cases, you should confirm that this is the case by first trying to free the library manually using the following command:

DBCC xp_sqlbackup(FREE)

and copying the xp_sqlbackup.dll file from the SQL Backup installation folder to the SQL Server instances' Binn folder. During installation, the installer places a copy of xp_sqlbackup.dll in a subfolder beneath the services' folder. The name of the subfolder depends on the processor architecture that the SQL Server instance is running on, as follows:

Processor architecture	Subfolder name
32-bit	Win32
64-bit	x64
Itanium	Itanium

If you are unable to replace the old copy of xp_sqlbackup.dll, this means that you would need to stop the SQL Server instances' service, replace the file, and restart the SQL Server service. If this is not a feasible option the workaround is to redirect the extended stored procedures to another file, until such time that you can replace xp_sqlbackup.dll.

To perform the redirection, first make a copy of the new xp_sqlbackup.dll, rename it (e.g. xp_sqlbackup_new.dll) and place it in the SQL Server instances' Binn folder. Then unregister the extended stored procedures from the existing file e.g.

EXEC master..sp_dropextendedproc sqlbackup
EXEC master..sp_dropextendedproc sqbstatus
EXEC master..sp_dropextendedproc sqbmemory
EXEC master..sp_dropextendedproc sqbdir
EXEC master..sp_dropextendedproc sqbutility
EXEC master..sp_dropextendedproc sqbdata
EXEC master..sp_dropextendedproc sqbtest
EXEC master..sp_dropextendedproc sqbteststatus
EXEC master..sp_dropextendedproc sqbtestcancel

and register the extended stored procedures using the newly renamed file e.g.

EXEC master..sp_addextendedproc sqlbackup, 'xp_sqlbackup_new.dll'
EXEC master..sp_addextendedproc sqbstatus, 'xp_sqlbackup_new.dll'
EXEC master..sp_addextendedproc sqbmemory, 'xp_sqlbackup_new.dll'
EXEC master..sp_addextendedproc sqbdir, 'xp_sqlbackup_new.dll'
EXEC master..sp_addextendedproc sqbutility, 'xp_sqlbackup_new.dll'
EXEC master..sp_addextendedproc sqbdata, 'xp_sqlbackup_new.dll'
EXEC master..sp_addextendedproc sqbtest, 'xp_sqlbackup_new.dll'
EXEC master..sp_addextendedproc sqbteststatus, 'xp_sqlbackup_new.dll'
EXEC master..sp_addextendedproc sqbtestcancel, 'xp_sqlbackup_new.dll'

The SQL Backup extended stored procedures will now use the functions located in the renamed DLL. If you do use this workaround, do remember to replace the older xp_sqlbackup.dll when you next bounce the SQL Server instance.

Checking the file versions

The easiest way to check the file versions is to view the properties of the files themselves, SQBCoreService.exe and xp_sqlbackup.dll.

install_12

You can also check the file versions remotely. When you run the SQL Backup extended stored procedure 'sqlbackup', the result set column header provides the version number of the xp_sqlbackup.dll file that is in use:

install_13

To check the version of the SQL Backup Agent service that is active, run the following command:

DECLARE @value nvarchar(64)
EXEC master..sqbutility 1030, @value OUTPUT
SELECT @value

install_14

If you are running multiple instances of SQL Server, note that you must log in to each instance, and check each instances' SQL Backup server components file versions separately.

SQL Backup uses the SQL Server Virtual Device Interface (VDI) to communicate with SQL Server. You may also need to ensure that the VDI library file (sqlvdi.dll) has been correctly installed on your server. See this document for further details.

Document history

12/19/2008	Added notes on vdi file.
12/3/2008	Initial release.