Backing Up the Supportworks Server

Status:	Published
Version:	1.3
Authors:	HTL QA
Applies to:	Supportworks ESP Version 7.x

Backing Up the Supportworks Server

Introduction

There are up to three distinct elements of a Supportworks server installation that you may wish to manually back up as and when required (if you ignore the data in any third-party database system such as Microsoft SQL Server). They are as follows:

• The data in the SwSQL server database - whether or not a third-party database is used
• Some files stored in a Microsoft logical volume on the Supportworks application server
• Windows registry keys recording any HTTP server aliases you may have modified or added

Different methods are used for backing up these elements, and you may wish to perform the backups of separate elements at different times and/or in different situations.

With database data specifically, you have a choice of backup methods, depending on whether or not Supportworks down time can be scheduled when you want the backups to be taken.

Scope

The information within this document covers backup of the Supportworks server. The parts of the document that cover database backup specifically are valid only for the databases that relate to the supplied SwSQL server. Note that, as SwSQL is a derivative of MySQL, the general characteristics of the former will be the same as that of the latter. Therefore, all documentation on MySQL usage and configuration will be equally valid for SwSQL, and may be obtained from http://dev.mysql.com/.

If the Supportworks server is utilising any database system other than SwSQL as the main database (for example, Microsoft SQL Server), provision to back up that database server should be made independently of the guidelines given in this document, as such backups would normally be the responsibility of your organisation's database administrators.

Throughout this document, it is assumed that none of the data you are backing up is corrupt in the first place. There are various strategies available to ensure that this remains so, and you are advised to follow the strategy employed by your organisation as a whole.

The Backup Options

File Backup

File backups are performed essentially by copying the files comprising the Supportworks server and Core Services (or sub-sets of these) directly from the location at which they reside to a backup destination of your choice. The root location of any default installation of Supportworks and its databases is:

C:\Program Files\Hornbill\

If a file backup is to include database files, server down time would be required. This is because Supportworks services depend on the core SwSqlServer service running, but this service keeps the database files open and constantly unlocked while it runs (just like the equivalent MySQL service), so it has to be shut down to avoid data corruption, which means that all the other services need to be shut down as well. Therefore, in such a case, file backup must be achieved by first stopping all those services, then copying the files from the hard drive volume that they reside on, and finally starting the services again. It is important to note that the SwSqlServer service must always be the last service to be shut down and the first service to be restarted. Note also that, in order to avoid problems with access from SelfService, the SwHttpServer service should always be restarted a second time at the end after all the other service starts.

On the other hand, if database files are not to be copied as part of a backup, you would not need to set aside any server down time. In that case, you would just copy the files.

You can copy the files by using the Windows xcopy command, and you can stop and start the services by means of the net stop and net start commands, respectively.

If you have to stop and start services, running an appropriate batch file would be an efficient way of backing up the system. For example, you could have a batch file as follows:

net stop SwAdminService

net stop SwCalendarService

net stop SwFileService

net stop SwIndexServer

net stop SwLogService

net stop SwMailSchedule

net stop SwMailService

net stop SwMessengerService

net stop SwSchedulerService

net stop SwServerService

net stop SwHttpServer

net stop SwSqlServer

xcopy C:\Program Files\Hornbill\*.* \\backup-server\backup-folder /d /e /v /c /i /q /h /r /k /y

net start SwSqlServer

net start SwHttpServer

net start SwServerService

net start SwSchedulerService

net start SwMessengerService

net start SwMailService

net start SwMailSchedule

net start SwLogService

net start SwIndexServer 

net start SwFileService

net start SwCalendarService

net start SwAdminService

net stop SwHttpServer

net start SwHttpServer

where backup-server would be the name of the server on to which the files are to be backed up, and backup-folder would be the name of the folder on that machine into which the files are to be backed up.

The example batch file above would copy all files in your Hornbill installation to the destination folder. You can be more selective in what you back up by having a separate xcopy command line for each sub-folder you are interested in. For instance, if you wish to exclude Core Services (and hence the database files) from a backup, the script need contain only a single line as follows:

xcopy C:\Program Files\Hornbill\Supportworks Server \\backup-server\backup-folder /d /e /v /c /i /q /h /r /k /y

Scheduling Considerations for Backups that Include Database Files

Any file backup that includes database files will involve stopping the Supportworks Scheduler service. Therefore, for such a backup, you should not use that scheduler to schedule the batch file. Instead, you could use the Windows Task Scheduler, for example.

SwSQL Server Database Backup by Export

You can back up the SwSQL server databases only, without shutting down any services, by means of the export utility swsqldump.exe, which is provided as part of Core Services. Running this command-line-driven utility (which has, in fact, the same syntax as mysqldump.exe) will export the contents of the specified Supportworks database into the specified file - conventionally with extension .sql - which may be located on the server or any other accessible place on the network.

The resulting file contains basically the information that is held in the files within the database's data folder (whose path on a default installation would be C:\Program Files\Hornbill\Core Services\SwSqlServer\data).

On a default installation, the pathname of the export utility would be:

C:\Program Files\Hornbill\Core Services\SwSqlServer\bin\swsqldump.exe

Because each swsqldump command line will export the contents of just one database, you will need several of these to export the data from all the Supportworks databases. It would therefore make sense to run these commands as a batch file. For example, to back up each of the Supportworks databases to a corresponding .sql file of the same name, the necessary batch file would be as follows:

c:

cd\Program Files\Hornbill\Core Services\SwSqlServer\bin

swsqldump -u root --quick --add-drop-table --extended-insert -B sw_knowledgebase >  \\backup-server\DB-backup-folder\sw_knowledgebase_export.sql

swsqldump -u root --quick --add-drop-table --extended-insert -B sw_messagestore >  \\backup-server\DB-backup-folder\sw_messagestore_export.sql

swsqldump -u root --quick --add-drop-table --extended-insert -B sw_systemdb >  \\backup-server\DB-backup-folder\sw_systemdb_export.sql

swsqldump -u root --quick --add-drop-table --extended-insert -B swdata >  \\backup-server\DB-backup-folder\swdata_export.sql

swsqldump -u root --quick --add-drop-table --extended-insert -B sw_config >  \\backup-server\DB-backup-folder\sw_config_export.sql

swsqldump -u root --quick --add-drop-table --extended-insert -B sw_messenger >  \\backup-server\DB-backup-folder\sw_messenger_export.sql

swsqldump -u root --quick --add-drop-table --extended-insert -B sw_calendar>  \\backup-server\DB-backup-folder\sw_calendar_export.sql

swsqldump -u root --quick --add-drop-table --extended-insert -B sw_tempdb >  \\backup-server\DB-backup-folder\sw_tempdb_export.sql

swsqldump -u root --quick --add-drop-table --extended-insert -B mysql >  \\backup-server\DB-backup-folder\mysql_export.sql

where backup-server would be the name of the server to which the database data is to be exported, and DB-backup-folder would be the name of the folder on that machine in which the actual .sql files containing the exported database data are to be created.

Alternatively, if the account in whose context the batch file would be run does not have rights to access the backup folder, you could specify a local target folder on the Supportworks server in which to create the .sql files. These could then be copied from that location to the backup folder by means of a separate xcopy command run in a more appropriate context.

Scheduling Considerations for Database Export

Since this technique for exporting database data does not require the stopping of Supportworks services, the embedded Supportworks scheduler could be used to schedule a batch file containing the export commands.

Alternatively, any other suitable utility, such as the Windows Task Scheduler, could be used for scheduling.

In all cases, it is important to ensure that the scheduler runs the batch file in an appropriate context.

Importing the Data Back into the Databases

If you have backed up Supportworks database data using the export utility swsqldump.exe, you may be called upon to restore this data back into the databases at some point, or you may wish to transfer the data between test and live systems. You can do this by means of the import utility swsql.exe, which is provided as part of Core Services (and which actually has the same syntax as mysql.exe). The Supportworks services (apart from SwSqlServer, which controls the SwSQL database system) will need to be stopped while the data import is conducted.

On a default installation, the pathname of the import utility would be:

C:\Program Files\Hornbill\Core Services\SwSqlServer\bin\swsql.exe

One swsql command line will be necessary for each Supportworks database into which you wish to import data from the relevant .sql file. Therefore, if the server and the folder on that server from which you will be importing are represented by backup-server and DB-backup-folder, respectively, then the entire set of command lines needed to perform the data import would be as follows:

c:

cd\Program Files\Hornbill\Core Services\SwSqlServer\bin

net stop SwAdminService

net stop SwCalendarService

net stop SwFileService

net stop SwIndexServer  

net stop SwLogService 

net stop SwMailSchedule

net stop SwMailService

net stop SwMessengerService 

net stop SwSchedulerService 

net stop SwServerService

net stop SwHttpServer

swsql -u root sw_knowledgebase < \\''backup-server''\''DB-backup-folder''\sw_knowledgebase_export.sql

swsql -u root sw_messagestore < \\''backup-server''\''DB-backup-folder''\sw_messagestore_export.sql

swsql -u root sw_systemdb < \\''backup-server''\''DB-backup-folder''\sw_systemdb_export.sql

swsql -u root swdata < \\''backup-server''\''DB-backup-folder''\swdata_export.sql

swsql -u root sw_config < \\''backup-server''\''DB-backup-folder''\sw_config_export.sql

swsql -u root sw_messenger < \\''backup-server''\''DB-backup-folder''\sw_messenger_export.sql

swsql -u root sw_calendar < \\''backup-server''\''DB-backup-folder''\sw_calendar_export.sql

swsql -u root sw_tempdb < \\''backup-server''\''DB-backup-folder''\sw_tempdb_export.sql

swsql -u root mysql < \\''backup-server''\''DB-backup-folder''\mysql_export.sql

swsql -h 127.0.0.1 -u root -e "use swdata; drop table swsystag;"

net start SwHttpServer

net start SwServerService

net start SwSchedulerService

net start SwMessengerService

net start SwMailService

net start SwMailSchedule

net start SwLogService

net start SwIndexServer

net start SwFileService

net start SwCalendarService

net start SwAdminService

net stop SwHttpServer

net start SwHttpServer

Note that the swsystag table has a single record containing the ID of the Supportworks server to which the SwSQL database system belongs. If you are restoring data after a database corruption incident, this ID may have been corrupted as well, so it is advisable to drop the table before restarting SwServerService as shown above, since the restart will recreate the table with the correct ID.

Again, note that, in order to avoid problems with access from SelfService, the SwHttpServer service should always be restarted a second time at the end after all the other service starts.

Backup of Registry Keys

The Supportworks Web server (SwHttpServer) makes use of a number of HTTP aliases for sub-folders under \Supportworks Server and \Core Services. These may include custom aliases created after Supportworks installation.

The only way you can back up HTTP aliases is by manually exporting (as .reg files) either or both of the following registry keys from the Supportworks server to the backup server:

HKEY_LOCAL_MACHINE\Software\Hornbill\Core Services\Applications\_Global\HttpAliases

HKEY_LOCAL_MACHINE\Software\Hornbill\Core Services\Applications\Supportworks Server\HttpAliases

To perform such an export, use the File > Export command in the Windows Registry Editor with the relevant key selected.

After the initial backup of these registry keys, you will only need to back them up again when new aliases have been added, which would normally be quite a rare occurrence. Therefore, it is not necessary to include registry-key backups as part of any regular full-backup policy.

Full Backups with No Server Down Time

As you can glean from the information given in the section The Backup Options above, it is possible to perform full backups of files and database data without shutting down any Supportworks or core services in the process. To summarise, the simplest strategy for achieving such a backup would be as follows:

• Run a file copy of the \Supportworks Server folder only, thus leaving out the database files resident in \Core Services.
• Export the database data using swsqldump.
• You can combine both of these backup elements in a single batch file - it is immaterial which of them is executed first.
• You can use any scheduler tool.

Dynamic Supportworks Replication

Replication of a Supportworks server installation entails the automatic backup of its crucial elements, on an ongoing basis, to an additional standby (or "slave") Supportworks server located on another machine, so as to provide quick and convenient disaster recovery. The elements to be replicated are basically the same as for manual backup, but the techniques employed are somewhat different for the most part.

See the FAQ entitled Supportworks Replication, where a number of methods are discussed.

Main Causes of Data Corruption

It is important to avoid backing up database data that is already corrupt. There are two common ways in which such data can become corrupt:

• Copying database files while they are being held open by the SwSQL server.

You should therefore ensure that such files are never being copied at any time the SwSQLServer service is running.

• Virus scanning software locking files that are required by the SwSQL server.

To prevent such a situation from arising, you should ensure that the database's data folder (whose path on a default installation would be C:\Program Files\Hornbill\Core Services\SwSqlServer\data) is excluded from scanning while the SwSQLServer service is running.