SOS G5 Backup and Recovery

Most SOS G5 organizations will be using Microsoft SQL Server to store their data. This database engine is available in a wide range of versions. Most SOS users probably will be using the free SQL Server Express edition, SQL Server Standard, or perhaps, SQL Server Enterprise. All three of these editions share the same core functionality and may be managed using Microsoft’s user interface known as SQL Server Management Studio (SSMS). Assuming that you have the rights to do so, you will be able to use SSMS to manage the database, including backing up and restoring the SOS G5 database. Again assuming that you have the necessary rights, if you are on a network, you will be able use SMSS to do these tasks from any workstation.

Typically, when you install SQL Server on your computer or server, you will also install a copy of SSMS. If you cannot locate a copy, want a copy on another computer, or want to update a copy you previously installed, you can download the installer for the current SSMS software at this Microsoft page: Download SQL Server Management Studio (SSMS).

Using SSMS to create a full backup of your database manually

Once you have installed your SOS G5 software and you have begun to enter data or have imported data from the previous version of SOS, you should backup that data as soon as you have the opportunity. As a start, let’s use SSMS to create a manual backup. We will then make a copy on external media as well.

  • Start by opening SQL Server Management Studio (SSMS).

Once you have logged in, click the plus to the left of “Databases” to expand that node. Right-click the SOSSuite database, then click “Tasks”, then click “Back Up…”.

The dialog for the backup task includes three panels. Go from one to the other using the navigation panel on the left:

Automating database backups using SQLCMD and Windows Scheduled Tasks

Thankfully, it is not necessary to manually go through the steps above every day (or more often). Instead, you can use Windows’ Task Scheduler to automatically run commands to create backups of the database and the transaction log in any desired location on the computer running SQL Server. For example, you could put the following two command lines in a file named BACKUP_SOSSUITE.CMD, and configure Task Scheduler to run this CMD file every night at 1:00 AM. Here are the two commands. The portions that would have to be changed to match your particular installation are in bold.



In this example, .SQLEXPRESS is the name of the SQL Server instance that is running the SOSSuite database. After DISK= is the desired full path and filename of the database or transaction log backup file, and after “>” is the name of the file that is used to capture the results of each of the commands so that you can check to be sure that the backups ran successfully.

Creation of these BAK files IS NOT A SUFFICIENT BACKUP. By definition, these files will be created on a local drive, perhaps even the same drive as your database is running. In addition, every time the commands are run, these files will be replaced by the latest ones. That means that an equipment failure, fire, or theft could wipe out both your data and your only backup!

For that reason it is essential that you include these BAK files in your daily computer or server backups to external media, using third-party backup software such as NovaStor’s NovaBackup or Cloudberry Lab’s Cloudberry Backup products, among many others. High-end server backup software often includes the ability to backup SQL Server databases directly, without the intermediate steps described above, which would certainly be an excellent option. Your backup software will have options to encrypt your backups, which is absolutely essential, and to compress them to minimize the amount of space consumed on your backup media.

See also:

Moving SOS 2010 through 2016 to a New Server

System requirements and recommendations

Moving SOS is not difficult, not even when migrating to a new server. Before committing to new hardware, however, you might want to review the system requirements and recommendations for the current generation of SOS (Releases 2010 through 2015), as well as what we anticipate going forward to the next generation of SOS.

The current software should run fine on most any current Windows desktop or laptop PC used as a network workstation or as a standalone SOS implementation for a small practice. Scaling would normally be just a matter of adding more RAM. Server requirements will vary depending on the roles running on the system, the size of the SOS database, and the number of simultaneous users. For specific details and recommendations, please see the following document:

Next, you might want to consider future requirements, as SOS 2016 is expected to be the final version using the current technology. It will be followed by completely re-written, modern software running on the Microsoft .Net platform. You will be able to deploy this new generation of SOS as a local standalone application, a local client-server installation (similar to the current software), a web-based application running on a web server on your own local network, or alternatively, running on a cloud infrastructure service such as Microsoft Azure or Amazon EC2. You will also be able to run a mix of locally-installed Windows software and users accessing the web-based software. It is expected that virtually all multi-user deployments will be as web-based software because of the relative simplicity of deployment and management, and the potential to run the software in a modern web browser on most any computer or tablet. For more information, please see:


Moving the current software to a new computer

Turning to the matter of moving the current software to new hardware, here is a step by step guide:

  1. Install the SOS software on the new computer, using your most recent CD, or using a complete installer (not a patch/update) downloaded from the SOS web site’s Download Files page.  It should be the same version or newer than the one you are currently using on the old computer.  (There is no need to install any earlier versions or CD’s.) If you have a current support contract, the installer for the current release is usually available on the SOS web site. If you must go from your current version to a newer version on the new computer (such as SOS 2009 on the old computer to SOS 2010 or later on the new computer, you must also upgrade the version of the database engine, making the process more complex. Contact SOS support for guidance.

  2. Ideally, you should then back up the entire SOS folder on the old computer, including all sub-folders. Backing up the entire SOS folder will assure that you will be moving the entire set of files, including any updates or patches that you have downloaded and installed, custom reports, claim files, and any other personal files you may have created and stored there.

  3. Restore the backup on the new computer. Depending on how you did your backup, your restore will be done differently. If you just copied the files to a DVD or USB drive using Windows Explorer, restoring the files is a simple matter of copying from the DVD or USB drive back to the matching folders on the new computer. If you did a backup using backup software like NovaBackup, then your target for the restore should be the appropriate drive letter on the new computer. The folder information is stored in the backup, and when you restore from your tape or other media, the files will be put back in their original folder locations. IMPORTANT: If you copy files from a DVD or CD using Windows Explorer, the copies on the new computer may all be set to “Read-Only” status. When you have finished copying the files, you should highlight all the files in each of the folders and reset the properties to uncheck the Read-Only setting: Highlight the files, right-click for the context menu, then left-click on Properties. Uncheck the Read-Only box under Attributes.
  4. Go into SOS on the new computer to make sure that the data has transferred correctly.  Only after you are certain that the software runs on the new computer and the data is intact should you remove the program from the old computer!

If you are moving a standalone installation or a database server installation, and you are putting the SOS folder in a different location (such as on a different drive letter, or placing it within a different folder on the new system), you may have to reset the transaction log file name embedded in the database during a previous database rebuild operation. If you fail to do so, the database will not start. The steps to reset the log file name appear in the box below:

    1. Open a command prompt window (Start > Run, then enter CMD or COMMAND and click OK).

    2. Change to the \SOS\DATA folder on the appropriate drive. (Type the drive letter followed by a colon and press <enter>, then type CD \SOS\DATA and press <enter>.) The command prompt should now show the correct drive and path, for example:


    3. Enter this command (SOS 2010 or later):


    4. This command removes the hard-coded path from the filename so the database will use the SOSDATA.LOG file in the same directory as the database files. You should see something like this:

Uninstalling the SOS Software from the old computer

Once you have confirmed that your new system is working fine and that the data is intact, you must completely remove the SOS applications from your old computer. If you want to backup the entire SOS folder just in case, that is fine, but you cannot leave a run-able copy on the old system. In order to completely remove SOS Office Manager for Windows from your computer, use the Windows Uninstall procedure, remove any SOS folder that remains, and empty the Recycle Bin.  Here are the steps:

  1. In Windows XP: Click on Start > Settings >  Control Panel > Add/Remove Programs.
    In Windows Vista or Windows 7: Click Start >  Control Panel > Programs and Features.
    In Windows 8 - 10: Press the Windows START key on your keyboard,  type control panel then click
    "Programs and Features" (if in Icon view) or click the "Uninstall a program" link under "Programs" 
    (if in Category view).
  2. Scroll down in the box listing your software programs until you find  “SOS Applications”.

  3. Highlight and click on the Remove or Uninstall button (or Right-Click SOS Applications, then select Uninstall).

  4. When you are asked if you are sure you want to completely remove _____ and all its components” click “Yes to All”.  If you are asked for any more verifications of your intention to remove all parts of the program, indicate “Yes”.  When you have finished removing all the SOS applications, close the list window and close Control Panel.

Configuration of the new computer

Database Servers

Edit the SERVER.PRM File in the SOS folder
For readability, we suggest you type each parameter on a separate line. Here is an example:

The recommended network packet size for SQL Anywhere 11 (the database engine provided with SOS 2010 through 2016) is 7300 bytes.  Make sure that is specified in your SERVER.PRM by the parameter:

-p 7300

In addition, to close a potential security threat, SOS strongly recommends that you add the parameter “TDS=NO”, in parentheses, after “-x tcpip”. Here is an example:

-p tcpip(TDS=NO;PORT=2638)

The -c, -cl, and -ch relate to the cache size available on the server  to run the software.  The -c parameter sets the amount of cache requested when the database starts up. The -cl parameter sets the lower size limit for the cache. Finally, the -ch parameter sets the maximum amount of RAM the database will use for cache, even if substantially more is available on the host computer. It is acceptable to omit all three settings, allowing the database engine to dynamically adjust the cache size as it sees best, but unless the server is dedicated to just running the database, you may want to set realistic size limits so that other processes won’t have to compete for RAM.

The best and simplest option for getting optimal performance from a large database is to run it on a 64 bit Windows system with an abundance of RAM. Ideally, you would have enough RAM installed in your system so that the entire database can be cached. In that case, allowing a larger cache is simply a matter of increasing the value of the –ch parameter in the SERVER.PRM file located in the SOS folder on your SOS database server computer. If you have, for example, 12 GB of RAM in your 64 bit system, you could allocate 9 GB of that RAM for potential use as database cache by including:

-ch 9g

in the SERVER.PRM file in the SOS folder. Remember, that parameter just sets an upper limit, it does not mean that 9 GB of RAM will be immediately reserved for use by the database. The amount actually used will go up and down in accordance with the amount of unused RAM in the system and the amount the database would like at any given time. In general, an upper limit size similar to the size of all the DB files (*.DB) in the \SOS\DATA folder will give you the best possible performance. Alternatively, in a 64 bit environment you can simply remove the -ch parameter entirely, which allows the database engine to dynamically size the cache based on resources available.

Windows 32 bit platforms are fine for smaller databases, but because they normally limit you to 1.8 GB for database cache, regardless of the amount of physical RAM in the computer, those with larger databases should really be using a 64 bit server.

The -tq switch shuts down the SOS database at a specified time. (Restarting the database once daily is recommended, and you should do your backup while the database is down.) The time is entered as military time.  For example, if you want to shutdown the database at 10:00 PM you would enter…

-tq 22:00

in the SERVER.PRM file.

Delete and Re-create the Database Service

You may have been running your database on your old system as a Windows background service, or perhaps you want to start doing so. The main advantage to running as a service is that the database will start and run whether or not anyone is logged into the server computer’s console. Services created in versions prior to SOS 2010 appear in the Windows Service Manager in the form:

Adaptive Server Anywhere – mysos

where “mysos” is the name you gave to the service when you created it. You must first remove the existing service, if there is one, then create a new one. The new one will appear in the list of services with a name in the form:

SQL Anywhere – mysos

To delete an existing SOS database service:

  1. Open a command window, being careful to use the “Run as administrator”. (That is, type CMD in the Start menu Search field, then right-click CMD.EXE in the search results and select “Run as administrator”.)

  2. Even if you plan to run the 64 bit database engine, change to the \SOS\SA\BIN32 directory:

  3. CD \SOS\SA\BIN32 <enter>

  4. Assuming that the name of your existing service is “mysos”, delete it with this command:

  5. dbsvc –y –d mysos 

To create the new service:

  1. If you are not already in a command window running with Administrator rights, follow steps one and two above.

  2. Now create the new service with the command below.
    The options in this example will set the service to run:

    • under the system account (-sa),
    • as a network service (-t network),
    • to start automatically (-s auto),
    • and to be named “mysos” (-w mysos)

    It will appear in the Windows Services Manager as “SQL Anywhere – mysos”

    If you are running the database in 64-bit Windows, using the 64-bit option is recommended, but either version of the engine will work. The 64-bit engine often provides better performance, especially for larger databases. If you are running in 32-bit Windows, you MUST use the 32-bit command. These commands would be typed on a single line, of course.

    Note: Service configuration commands are case sensitive. Type your options exactly as shown (eg: “automatic” will fail but “Automatic” will work). If you still have trouble getting the service created, leave out the “-s Automatic”. You can change the property to “automatic” from the Services applet after the service has been created.

    32-bit Windows (all on one line):

    dbsvc -as -t Network -s Automatic -w mysos c:\sos\sa\bin32\dbsrv11.exe @c:\sos\server.prm c:\sos\data\sosdata.db

    64-bit Windows (all on one line):

    dbsvc -as -t Network -s Automatic -w mysos c:\sos\sa\bin64\dbsrv11.exe @c:\sos\server.prm c:\sos\data\sosdata.db

  3. After executing the command, you will find a new Windows service listed in Windows’ Administrative Tools > Services applet: SQL Anywhere – mysos. You can adjust the properties for the service just as you would for any other service.

For more detailed discussion and instruction for running SQL Anywhere as a Windows service, see:

Running the SOS SQL Anywhere 11 Database as a Windows Service

Adjust Scheduled Tasks

You may be using one or more Scheduled Tasks in Windows to control starting or stopping your database. Normally stopping the database automatically is handled with a –tq parameter in the SERVER.PRM file, such as:

-tq 22:00

to automatically shut down the database at 10:00 pm, but if running as a service, it is possible that you are using a scheduled NET STOP <your database service name> command to do so. Either way will work fine.

You probably will want to automate restarting the database after your nightly backup, whether you run the database engine as a foreground task, or as a Windows service. Here is an example of both STOP and START commands:

Inspecting the files in the SOS folder, you might find a CMD or BAT file that launches the database. If so, edit the batch file, making any necessary changes. That command file might include a NET START command that doesn’t reference the correct service name. In that event, change the command to start the correct service. The name, of course, should match the name of the service you created above. Check the properties of the new service in your Control Panel > Administrative Tools > Services to be sure of the name. Once you open the Properties dialog for the service, you will see the service name at the top of the first tab, probably in the form “SQLANYs_mysos”. You should be able to use the verbose name in the Services list, as in the example above, or the short name in the Properties dialog. Both should work equally well, though make sure you include quotation marks around the verbose name to prevent issues with the embedded spaces in the string.

Once you have the command corrected, whether it is to start the database service or launch the engine as a foreground application, create a scheduled task in Windows task scheduler to execute the command or command file every morning before staff arrives to work.

IMPORTANT: If you run the database engine as a foreground task, you must not logout of the server console. If you do, the database will shut down. That is another reason to run it as a service in the background.

Network Workstations

After installing the new version of the software on a network workstation, including a terminal server, check the ODBC settings to be sure that the Buffer Size setting on the Network tab is set to 7300 bytes to match the packet size setting on the server. While on this tab, note that specifying the IP address of your database server as a TCP/IP parameter in the form: HOST= (using your own server’s address) is sometimes necessary if the workstation cannot otherwise locate and connect to the database server. Unless you are having a problem, do not specify this parameter. Also on the Network tab is an option to Compress network packets. Using this option can make a significant performance improvement on some networks, but can slow things down on others. If your servers and workstations are relatively speedy, but your network is slow, this option should help. On the other hand, if your server and/or workstations are already working pretty hard, and you have abundant network capacity, checking this option may actually slow things down. You will have to experiment to know for sure. Test by timing the generation of large reports.

Firewall issues are the ones most often responsible for client-server communication problems. Make sure that you open port 2638, which is the one used by the SQL Anywhere engine by default.

Potential Security Vulnerability (TDS) Fix (incomplete)

(The following information is relevant only for those running the SOS database in networked mode. If you are running a standalone configuration of SOS, without the keyword “tcpip” in the SERVER.PRM file, there is nothing you need to do.)

Although this vulnerability applies primarily to Sybase’s larger, enterprise product, SOS strongly recommends the following, easy procedure to prevent any use of a similar exploit against your SQL Anywhere database.

  1. In your SOS folder, look for the SERVER.PRM file and open it with Notepad or any other plain text editor (NOT using a word processor like MS Word!). You could, for example, open Notepad on your server, then load the SERVER.PRM file, located in your SOS folder.
  2. Find the -x tcpip parameter. It may or may not be followed by one or more parameters within a set of parentheses.
  3. If there are no parentheses, then add (TDS=NO) immediately after the -x tcpip. If there are already parentheses and one or more parameters, then add a semi-colon and TDS=NO just before the closing parenthesis. See the examples below.


The change will take effect next time you shut down and restart the database. To be sure, after restarting the database, go into Office Manager or Case Manager, then select HELP > VERSION INFORMATION. Click the DB tab and examine the “Server Startup Command” at the bottom of the window:

Storage and Performance Impact of Attachments in SOS

Attachments are all compressed using the standard zip compression algorithm before being imported into the database, so anything that is not already in a compressed format will be significantly reduced in size when stored in the database. (FYI: Documents in pdf format, and graphics in jpg format, for example, are already compressed, so they will take up approximately the same amount of space in the SOS database as they do outside of it.)

Bottom line is that if you use the option to delete the originals after importing, the net storage used will be less than if you just saved the documents separately on your hard disk or network. You also enjoy the benefits of greater security, the convenience of having the attachment information in a handy location, and the peace of mind that your attached documents are backed up along with the rest of your SOS data, with no need for any change in backup strategy or configuration.

Impact on performance of the database should be negligible, assuming appropriate amounts of RAM for the size of your database and modern disk drives.

Backup and Recovery: Advanced Topics

The information in the Backing Up Your Data article is quite sufficient for most installations. The current document discusses advanced topics that will be of particular interest to those running the system in larger organizations with high data entry volume. The following topics include how to recover data entered since the most recent backup and how to backup up a running database. Much of this material is reprinted from the Backup and Data Recovery chapter of the SA Usage manual, with permission from Sybase, Inc. You can read this information directly in the online SA help, accessed from your Start > Programs > SOS Applications menu.

The material below explains how to use SA transaction log files to protect your data, how to make backup copies of your database and log files while the database is running, and how to recover after system and media failures.

Backing Up a Database While It Is Running

In some installations, it is desirable to keep the database running 24 hours a day. You cannot backup the running database using commercial backup software, so a special procedure must be used.

You can either:

1. Depend on Microsoft Volume Shadow Copy Services (VSS) to snapshot the database when using third party backup software after configuring your system to run the dbvss11 utility as a Windows service, or

2. (preferred) Use the included live backup utility to make a copy of the database in a separate directory, and backup that copy rather than the running production files in your backup set.

There is a downside to running the database non-stop. There are several maintenance/clean-up procedures that run automatically when the database is started. If you never restart the database, it is possible that you might run into minor issues, such as the inability to log into the system with a particular user ID after that user disconnected from the database in an unusual fashion. See Database Tools in the Admin Module to do the cleanup manually.

The recommended the following live backup procedure:

Create a CMD batch file containing the following commands:

(SOS 2015 and earlier)

C:\SOS\DBTOOLS B=<target directory> -y

(SOS 2016 - Requires that you create a backup-only user account in SOS! See the SOSADMIN.PDF document in the \SOS folder of your system.)
cd \sos\sa\bin32
DBBACKUP -c “uid=<account ID>;pwd=<account password>;dsn=SOSDATA” <target directory>

where <target directory> is an existing directory in which you want the copy of the database to be created. This directory must be located on the computer running the database. It should NOT be a shared folder on another computer! Make sure that this target directory is included in your backup configuration so that the database copy is backed up, and set the backup to skip the \SOS\DATA directory in which the running database files are located. Most backup software will fail to make usable backups of a running database!

Here is an example command line, including an optional -y parameter to create 
the directory if it doesn't exist, and to overwrite existing files:
DBBACKUP -y -c "uid=BU_BILL;pwd=PASSword#1;dsn=SOSDATA" C:\SOSBU

You can run a database validation or database copy interactively from the menus in the “DBA Utils” program in the Admin Module. Once the program has started, go to the Tools menu. Run the validation by selecting “Check Database”. You can run an ad-hoc live database copy by choosing “Copy Database” in the Tools menu. Generally, live backups are done as a Windows scheduled task that executes one or more times each day.

Note that for security reasons copying the database is only possible on the computer running the database software.

It is absolutely essential that you monitor your backups closely to be sure that the current database files are being copied to your backup media. If you are using commercial backup software, be sure to turn on the “verify” or “compare” operation and inspect your backup logs every day!

If you plan to backup the running database with Backup Exec, NovaBackup, or some other backup software that uses Microsoft Volume Shadow Copy Services (VSS) to backup open files, then create and run a Windows service for the SA VSS Writer component, DBVSS11.EXE, as follows:

  1. Open a command window.
  2. On 32-bit Windows type:
    CD \SOS\SA\BIN32 <enter>On 64-bit Windows type:
    CD \SOS\SA\BIN64 <enter>
  3. On 32-bit Windows type
    dbsvc -as -s Automatic -t vss -w VSSWriter “c:\SOS\SA\BIN32\dbvss11.exe” <enter>On 64-bit Windows type:
    dbsvc -as -s Automatic -t vss -w VSSWriter “c:\SOS\SA\BIN64\dbvss11.exe” <enter>

Once you have created the service, you should see it listed in your Windows Services as
“SQL Anywhere – VSSWriter”

It is absolutely essential that you test your backup during a period when the database is actively in use by restoring your backup, restarting your system, and validating the restored database as described in “Validating the Database”, above.

Live Backup and Database/Log Mirroring

SQL Anywhere also has the capability of running a continuous live backup of the transaction log on a separate computer, and even doing a failover database server running on a second computer. For discussion of these topics please see the appropriate topics in SQL Anywhere Database Administration manual located on your system. Go to Start > Programs > SOS Applications > Sybase SA Manuals. Open the Database Administration manual, then find “Backup and Data Recovery” in the “Maintaining Your Database” section.

System and Media Failures

SA has features to protect your data from two categories of computer failure: system failure and media failure.

System Failure: A system failure occurs when a power failure or some other failure causes the computer or operating system to go down while there are partially completed transactions. This could occur when the computer is inappropriately turned off or rebooted, or when another application causes the operating system to crash.

Media Failure: A media failure occurs when the database file, the file system, or the device storing the database file becomes unusable.

Recovery from Failure

When failures occur, the SA recovery mechanism treats transactions properly, as atomic units of work: any incomplete transaction is rolled back and any committed transaction is preserved. This ensures that even in the event of failure, the data in your database remains in a consistent state.

Make Regular Backups

You should make regular backups of your database files so that you can recover your database in the case of a media failure. SA uses the transaction log (which you should store on a separate device from the database for maximum data safety, or continuously backup using the transaction log mirroring feature of SA) to recover information put into the database since the last full backup. For information on management of the transaction log file, please see:

The SA Logs

SA uses three logs to protect your data from system and media failure. These log files exist for each database running on a database engine or server.

All these logs play a role in data recovery. Each log exists for each database running on a database engine or server. Optionally, you can maintain a mirror of the transaction log for greater protection of vital data.

The Checkpoint Log

An SA database file is composed of pages. Before a page is updated (made dirty), a copy of the original is always made. The copied pages are the checkpoint log.

Dirty pages are not written immediately to the disk. For improved performance, they are cached in memory and written to disk when the cache is full or the server has no pending requests. A checkpoint is a point at which all dirty pages are written to disk. Once all dirty pages are written to disk, the checkpoint log is deleted.

Reasons for a Checkpoint

A checkpoint can occur for several reasons:

· The database engine is shut down

· The amount of time since the last checkpoint exceeds the database option CHECKPOINT_TIME

· The estimated time to do a recovery operation exceeds the database option RECOVERY_TIME

· The database engine is idle long enough to write all dirty pages

· A transaction issues a CHECKPOINT statement

· The database engine is running without a transaction log and a transaction is committed

The priority of writing dirty pages to the disk increases as the time and the amount of work since the last checkpoint grows. This is important when the database engine does not have enough idle time to write dirty pages. The database option CHECKPOINT_TIME controls the maximum desired time between checkpoints. The database option RECOVERY_TIME controls the maximum desired time for recovery in the event of system failure. Both times are specified in minutes.

When the database server is running with multiple databases, the CHECKPOINT_TIME and RECOVERY_TIME specified by the first database started is used, unless overridden by command line switches.

For a description of the command-line options, see “The database engine” in the on-line SA manual.

The Rollback Log

As changes are made to the contents of tables, a rollback log is kept for the purpose of canceling changes. It is used to process the ROLLBACK statement for recovering from system failure. There is a separate rollback log for each transaction. When a transaction is complete, its rollback log is deleted. (Note that the term transaction in this context refers to a related set of database operations, not a transaction in the sense of a single charge or credit entry in OM.)

The Transaction Log

For a condensed and SOS-specific discussion of transaction log management, see on the SOS web site.

All changes to the database are stored in the transaction log in the order in which they occur. If you should suffer a data loss, you could restore your most recent backup, then use the transaction log to restore all your changes since the last backup was done. In most cases, therefore, you can restore everything up to the time of your failure or loss, not just up to the time of your backup! The only price you pay for this additional security is the disk space taken by the transaction log. In the sections below you will learn how to move the log to another disk and how to clear it from time to time (after you have made and verified a full backup).

Although use of the transaction log is optional, by default it is enabled. If you run SA with no transaction log, a checkpoint is carried out whenever a transaction is committed. The checkpoint ensures that all committed transactions are written to the disk. Checkpoints can be time consuming, so you should run with a transaction log for improved performance as well as protection against media failure and corrupted databases.

For greater protection, SA allows you to maintain two identical transaction logs in tandem. This is called transaction log mirroring.

The transaction log is not kept in the main database file. The filename of the transaction log is set when the database is installed. For additional security against media failure, the transaction log may be written to a different device than the database file. Some machines with two or more hard drive letters only have one physical disk drive with several logical drives or partitions. If you want protection against media failure, make sure that you have a machine with two separate disk drives or use a storage device on another computer on the network. Note that by default, the transaction log is put on the same device and in the same directory as the database (\SOS\DATA). This default installation, although convenient, does not protect against media failure.

Placing the transaction log on a separate device can also result in improved performance by eliminating the need for disk head movement between the transaction log and the main database file.

Moving the Transaction Log

Use the SA DBLOG utility, located in the \SOS\SA\BIN32 folder, to move the transaction log to a different drive by setting a new transaction log name. Simply copying the file from one drive to another will not work because the location of the log is stored in the database.

Make sure that the database is not running.

From the \SOS\SA\BIN32 prompt, type, in lower case:

dblog -t <new log name> <database name>

The new log name should include the drive, path, and filename of the new log file, for example, d:\soslog\sostran.log .

The database name should include the drive, path, and filename of the main database file, for example, c:\sos\data\sosdata.db.

Once you have reset the location in the database using this procedure, physically move or copy the current transaction log to the new location and, optionally, name you have specified. You will then be able to restart the database engine normally. Remember to include the transaction log, in its new location in your daily backup!

Using a Transaction Log Mirror

A transaction log mirror is an identical copy of the transaction log, maintained at the same time as the transaction log. Every time a database change is written to the transaction log, it is also written to the transaction log mirror file. By default, SA does not use a mirrored transaction log, but you can readily add a transaction log mirror to your system. The procedure is described in the next section.

A mirrored transaction log is useful for extra protection of critical data. There is a performance penalty for using a mirrored log, as each database log write operation must be carried out twice. The performance penalty will depend on the nature and volume of database traffic and on the physical configuration of the database and logs, but in most cases will not be apparent to users of the system.

A transaction log mirror should be kept on a separate physical drive from the transaction log, so that if either drive fails, the other copy of the log keeps the data safe for recovery. Both drives should be on the same computer, however! Maximum protection of your data, therefore, would require three drives (physical drives, not just partitions on the same drive). The database files (the *.DB files) would be on one drive, the transaction log (sosdata.log) on a second, and the mirrored log (sosdata.mlg) on a third.

Creating a Transaction Log Mirror

Use the SA DBLOG utility, located in the \SOS\SA\BIN32 folder, to create a transaction log mirror file on another hard drive.

Make sure that the database is not running.

From the \SOS\SA\BIN32 prompt, type, in lower case:

dblog -m <new mirror log name> <database name>

The new mirror log name should include the drive, path, and filename of the new mirror log file, for example, d:\soslog\sosdata.mlg (mlg is the default mirror extension)

The database name should include the drive, path, and filename of the main database file, for example, c:\sos\data\sosdata.db

Removing a Transaction Log Mirror

If you have been using a log mirror and no longer want to do so, use DBLOG to discontinue mirroring:

Make sure that the database is not running.

From the \SOS\SA\BIN32 prompt, type, in lower case:

dblog -r <database name>

The database name should include the drive, path, and filename of the main database file, for example, c:\sos\data\sosdata.db

Erasing Transaction Log and Mirror Files

You can erase transaction logs and mirrors using the DBERASE command-line utility. Note that this command physically deletes the file from the disk, but does not terminate the use of either. As soon as you restart the database, the files will be recreated.

To delete a mirror log file only:

Make sure that the database is not running.

From the \SOS\SA\BIN32 prompt, type, in lower case:

dberase <mirror file name>

The mirror file name should include the drive, path, and filename of the mirror file that you want to erase, for example, e:\sos\data\sosdata.mlg

To delete a transaction log file but not its mirror:

Make sure that the database is not running.

From the \SOS\SA\BIN32 prompt, type, in lower case:

dberase <transaction log file name>

The transaction log file name should include the drive, path, and filename of the transaction log file that you want to erase, for example, d:\sos\data\sosdata.log

Validating the Transaction Log and Mirror

When a database that is using a mirror starts up, the database engine or server carries out a series of checks and automatic recovery operations to confirm that the transaction log and its mirror are not corrupted, and to correct some problems if corruption is detected.

On startup, the database engine checks that the transaction log and its mirror are identical by carrying out a full comparison of the two files; if they are identical, the database starts as usual.

The comparison of log and mirror adds to database startup time when you are maintaining a log mirror.

If the database stopped because of a system failure, it is possible that some operations were written into the transaction log but not into the mirror. If the database engine finds that the transaction log and the mirror are identical up to the end of the shorter of the two files, then the remainder of the longer file is copied over into the shorter file to produce identical log and mirror. After this automatic recovery step, the database engine starts as usual.

If the check finds that the log and the mirror are different in the body of the shorter of the two, one of the two files is corrupt. In this case, the database does not start, and an error message is generated saying that the transaction log or its mirror is invalid. If this situation should occur, consult “Recovering from media failure on a mirrored transaction log” in the on-line SA Database Administration Guide.

Validating the Database

From time to time prior to running a full backup, it is a good idea to verify that the database file is not corrupt. A small portion of the database could be corrupt without you ever knowing until it was too late to recover the data.

With the database engine (single user) or server (network) running, execute the Check Database tool in the database utility that comes with your SOS software. Enter a Security Administrator id and password, then click the Admin icon. Once at the administration console window, select Database Tools > DBA Utilities > Tools > Check Database.

If desired, you can run the same procedure from the command line or from within a batch file, using the following command, from the prompt in the SOS directory:


You may want to include this command in a desktop shortcut. It is an excellent idea to run this routine before or after your daily backup to be sure that you are backing up an intact database.

The validation procedure can take a little while to complete on a large database. It scans every record in every table and looks up each record in each index on the table. It can be run while the database is in use, but you may notice some performance impact while it is running.

Any messages produced by the utility are collected in a file on disk (\SOS\SOSVALID.LOG) and displayed when the procedure is complete. If the database file is corrupt, you must restore the DB files (but not the LOG file) from your previous intact backup. You may then be able to apply the current transaction log in order to bring the database up to date (see “Lost Database Files” on page 28).

Backing up Your Database
On-line Backups

In some organizations, operations continue around the clock and it is not possible to stop the database to perform a traditional backup. Under these circumstances, you can use the provided Copy Database utility to make a copy of the database, while it is running, in a different directory. Once the copy is complete, the duplicated files can be included in your regular backup even though the open production database files will be skipped by your backup software. (To avoid errors in your backup logs, configure your backup to skip over the production database files or folder.)

Run Copy Database interactively from the menus in the DBA Utilities program, which is located in the Admin Module. The Admin icon appears on the login window for users who are designated as Security Administrators. On the Admin menu, select Database Tools, then DBA Utilities.

Once the program has started, go to the Tools menu. Select Copy Database to make a live copy of the running database into a different directory. Note that for security reasons copying the database is only possible on the computer running the database software.

The Copy Database utility can be run against a standalone engine or network database server. The utility will not run from a network workstation for security reasons. Using the Copy Database utility on a running database is equivalent to copying the database files when the database is not running. In other words, it provides a snapshot of a consistent database, even while it is being modified by other users.

For a full description of the SA online backup facility, see “Use the Backup utility (dbbackup) to make a server-side backup” in the online SA Database Administration Guide, page 882 (pdf page 900). It is important to understand, however, that you will not be able to use the utility directly against the secure SOSDATA database. Rather, you must run it from within the DBA Utilities shell program provided by SOS as described above or using the DBTools command line explained below.

You also can copy the database from the command line, batch file, or desktop shortcut by executing DBTOOLS.EXE B=<target directory> from the SOS directory (but only on a standalone system or directly on the database server computer). For example, assuming that the SOS folder is located on the C: drive:


would create a copy of the currently running database in the C:\SOS\BACKUP folder. Note that the folder must exist before you execute the command. SOS recommends that the destination for the copy operation be a different drive on the database computer than that on which the production database is located. That way, if your primary drive should fail, the most recent copy of the database on the secondary drive will be readily available to use in the restoration process.

Open File Backups Using Volume Shadow Copy Service

[SOS 2010 and later] SOS recommends the Copy Database procedure detailed above for best reliability. In addition, there is a side benefit that it leaves a backup copy of your database and log on a local hard disk as a redundant backup to the one you make on removable media, internet-based backup service, or both.

[SOS 2010 and later] That said, many commercial backup programs include the ability to backup files that are in use through the Volume Shadow Copy Service available on versions of Windows starting with XP. For best results, you should create and enable the SA VSS Writer service by executing the following command from the command prompt in the \SOS\SA\BIN32 directory:

dbsvc -as -s Automatic -t vss -w SAVSSWriter “c:\sos\sa\bin32\dbvss11.exe”

It is absolutely essential that you test your backup during a period when the database is actively in use by restoring your backup, restarting your system, and validating the restored database as described in “Validating the Database”, above .

Incremental Backups of the Transaction Log

You can carry out an off-line incremental backup by making a copy of the transaction log. Alternatively, you can carry out an online incremental backup by running the SOS Copy Database utility, and subsequently backing up the SOSDATA.LOG file in the normal fashion to tape or other removable media.

You should back up the transaction log daily. This is particularly important if you have the transaction log on the same device as the database file. If you have a media failure, you could lose both files. By doing daily backups of the transaction log, you will never lose more than one day of changes.

Daily backups of the transaction log are also recommended when the transaction log tends to grow to an unmanageable size between full backups and you do not want to get a larger storage device or do more frequent full backups. In this case, you can choose to archive and delete the transaction log by selecting the appropriate options in the SOS Copy Database utility.

There is a drawback to deleting the transaction log after a daily backup. If you have media failure on the database file, there will be several transaction logs since the last full backup. Each of the transaction logs needs to be applied in sequence to bring the database up to date, as described below. It is therefore extremely important to save all transactions logs that cover database activity after the most recent verified backup of the database files themselves!

SOS strongly recommends that Transaction logs be archived onto CD-R or other permanent media that will not be overwritten and that these media be stored in a safe location, such as a fireproof data safe, or even better, off-site in a bank vault.

Backing Up, Renaming and Restarting the Transaction Log

Use of the following command will do a live backup of the transaction log into the target directory, but will also rename the backup with the form YYMMDD##.LOG (where ## is a sequential number starting with 00, in case you execute the command more than once on a single date) and restart the log. The result will be a copy of the current SOSDATA.LOG in the backup directory, but renamed based on the date, and a brand new, small SOSDATA.LOG file in the \SOS\DATA folder.


You can specify any existing directory on the server instead of “C:\BACKUPS”, but be sure to type the -t -r and -n in lower case.

If you prefer to work within a graphical interface, you can do this process from Database Tools > DBAUtils in the Administration module. Select Copy Database on the Tools menu, select the desired backup directory and check the -t, -r, and -n options as shown below.

Use the Copy Database utility to backup and restart your transaction log file.

To create the renamed backup copy in the current data directory, just type a period (.) for the Backup directory. The dated copy of the old log will be created in the same directory as the database files and new log file.

Data Recovery
System Failure

After a power failure or other system failure you should run the Windows’ scandisk utility to correct simple errors in the file system structure that might have been caused by the system failure. This step should be done before running any other software.

After a system error occurs, SA automatically recovers when you restart the database. The results of each transaction committed (written to the database) before the system error are intact. All changes by transactions that were not committed before the system failure are canceled, and therefore lost. In most cases that means you will only lose those changes that had not been completely saved prior to the failure.

The database engine automatically takes three steps to recover from a system failure:

1. Restore all pages to the most recent checkpoint, using the checkpoint log.

2. Apply any changes made between the checkpoint and the system failure. These changes are in the transaction log.

3. Rollback all uncommitted transactions, using the rollback logs. There is a separate rollback log for every connection.

Step 3 may take a long time if there are long uncommitted transactions that have already done a great deal of work before the last checkpoint.

Media Failure

Perhaps the most frequent cause of data loss is the failure of a hard drive or failure of drive controller electronics, leading to corruption of the data on the drive.

If you have backups, you can always recover all transactions that were committed before the media failure. Recovery from media failure requires you to keep the transaction log on a separate device from the database file. The information in the two files is redundant. Regular backups of the database file and the transaction log reduce the time required to recover from media failures.

The first step in recovering from a media failure is to clean up, reformat, or replace the device that failed.

The steps to take in recovery depend on whether the media failure is on the device holding your database file or on the device holding your transaction log. These discussions assume that your system stores the transaction log (or mirror) on a separate drive from the database files.

Lost Database Files

When your transaction log is still usable, but you have lost your database file, the recovery process depends on whether you keep or delete the transaction log on incremental backup.

If you have not deleted or restarted the transaction log since the last full backup, the transaction log contains everything since the last backup. Recovery involves four steps:

1. If you do not already have one, make a backup of the current transaction log immediately. The database file is gone and the only record of the changes is in the transaction log.

2. Restore the most recent full backup (the database file).

3. Change to the \SOS\SA\BIN32 directory.

4. Use the database engine with the apply transaction log (-a) switch to apply the transaction log and bring the database up to date:

dbeng11 c:\sos\data\sosdata.db -a d:\soslogs\sosdata.log

Start the database in the normal way. The database engine will come up normally and any new activity will be appended to the current transaction log.

If you have archived and deleted the transaction log since the last full backup, each transaction log since the full backup needs to be applied in sequence to bring the database up to date.

Make a backup of all transaction logs immediately. The database file is gone and the only record of the changes is in the transaction logs.

Restore the most recent full backup (the database file).

Change to the \SOS\SA\BIN32 directory.

Starting with the first transaction log after the full backup, apply each archived transaction log by starting the database engine with the apply transaction log (-a) switch. For example, if the last full backup was on Sunday and the database file is lost during the day on Thursday:

dbeng11 c:\sos\data\sosdata.db -a d:\soslogs\mon.log

dbeng11 c:\sos\data\sosdata.db -a d:\soslogs\tue.log

dbeng11 c:\sos\data\sosdata.db -a d:\soslogs\wed.log

dbeng11 c:\sos\data\sosdata.db -a d:\soslogs\sosdata.log

SA does not allow you to apply the transaction logs in the wrong order or to skip a transaction log in the sequence.

An easier approach is to put all the log files in the same directory, then use this command:

dbeng11 c:\sos\data\sosdata.db -ad d:\soslogs

where “d:\soslogs” is the directory in which you have placed all the relevant log files. The database will inspect the files and automatically apply them in the correct sequence.

After applying the transaction logs, start the database in the normal way. The database engine will come up normally and any new activity will be appended to the current transaction log.

Lost Transaction File

When your database file is still usable but you have lost your transaction log, the recovery process is as follows:

1. Make a backup of the database file immediately. The transaction log is gone and the only record of the changes is in the database file.

2. Restart the database with the -f switch:

dbeng11 c:\sos\data\sosdata.db -f

Without the -f switch, the database engine will complain about the lack of a transaction log. With the switch, the database engine will restore the database to the most recent checkpoint and then roll back any transactions that were not committed at the time of the checkpoint. A new transaction log will be created and the database will do a clean shutdown, after which you should be able to restart it normally.

Media failure on the transaction log can have more serious consequences than media failure on the database file. When you lose the transaction log, all changes since the last checkpoint are lost. By default, database checkpoints are set at 30 minute intervals. This will be a problem when you have a system failure and a media failure at the same time (such as if a power failure causes a head crash that damages the disk). Frequent checkpoints minimize the potential for lost data but also create work for the database engine writing out dirty pages.

For running high-volume or extremely critical applications, you can protect against media failure on the transaction log by mirroring the transaction log or by using a special-purpose device, such as a storage device that mirrors the transaction log automatically.