Home

Installation checklist

Prev Page Next Page

Introduction
Recovery models
Main backup types
Backing up the database files by copying
The transaction log
Transaction log restore sequence
Log sequence numbers
Truncating and shrinking the transaction log
Backing up the tail
Inside the transaction log
So, what's in a backup file?
Test: A full backup does not contain deleted data
Verifying backup files
Verifying backup files on a budget
Why you shouldn't rely on RESTORE VERIFYONLY
Cumulative backups
Recovering individual tables
Backup and restore history details
Backup reads and writes
Speeding up backups
Backup speed details
Speeding up restores
Restore state affects speed too
Backup and restore rights
Log shipping
Log shipping in SQL Server 2000
Setting up log shipping using Enterprise Manager
Checking the set up
Failover
Log shipping in SQL Server 2005
Setting up log shipping using Management Studio
Checking the set up
Log shipping status report
Failover
Log shipping in SQL Backup
Using the CopyTool utility
Failover
3rd party backup applications
VDI
VDI versions
VDI errors
SQL Backup - beyond compression
Restoring a chain of transaction log backups
Restoring to the latest possible state
Backing up multiple databases
Backup retention
Making a copy of the backup file
Backup file naming conventions
Restoring the latest backup set
Network resilience
Encryption
Integrated database verification
Database file relocation
Improved backup retention
RESTORE HELP
Common SQL Backup issues
Installation checklist
Setting up rights
Configuring service rights
Backup data
Hanging issues
Common backup and restore errors
Error 3201 - when performing a backup to a network share
Full database backup file is larger than database size
Error 3205 - Too many backup devices specified for backup or restore
Error 4305 - an earlier transaction log backup is required
Bringing a database that is in recovery or read-only mode online
Using bulk-logged recovery model but transaction log backup is still large
Error 14274 - unable to delete SQL Server Agent job
Error messages when restoring from different versions of SQL Server.



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.



Discuss or comment on this article on our Facebook group.

 

 
Copyright 2008 - 2013 Yohz Ventures Sdn Bhd. All rights reserved.
All product and company names are trademarks or registered trademarks of their respective owners.