Regardless of where your application is storing data a common set of maintenance concerns should be addressed such as:

• Backup and Restore
• Maintaining Performance    
• Handling Corruption
• Handling Transient Errors

VistaDB is no different.  To provide the best support for your users once your application is deployed you should incorporate some features in your system to address each of these issues.

Backing Up and Restoring

This is generally simpler than when using SQL Server - there are no custom SQL Commands to learn and no special permissions are required to backup or restore because a VistaDB database is a single file.  VistaDB has archiving features on its API to make a compressed snapshot of a database and restore that snapshot to a new database file. 

It is also possible to make a backup by copying the VistaDB database file on your own.  Restoring it would similarly be just copying it back to the original name.  You will need to either have no connections open to the database or only readers since any updates will prevent the operating system from getting a clean copy of the file.  To avoid these challenges, use the built-in Archive feature

For more information, see How To - Backup and Restore a Database.

Maintaining Performance

LIke SQL Server, VistaDB does require some periodic maintenance to function best.  In SQL Server this would typically consist of index defragmentation/rebuilding and DBCC CheckDB to detect and resolve minor data file corruption.  In VistaDB these functions are done by Pack and Repair. 

Why pack a database? It:

• Frees Disk Space: While VistaDB attempts to refill empty spots in the data file, this often isn't feasible so as rows are deleted and updated the file size will grow.
• Improves Index Performance: Just like SQL Server indexes performance can drop over time when large numbers of rows have had their indexed values change.
• Corrects Minor Corruption: If the VistaDB database file has gotten damaged (most commonly because the system ran out of disk space and couldn't completely write index and data pages) a Pack will frequently resolve the problem by rebuilding the indexes.

If the database has become truly corrupted (where the data on disk can't be loaded by the engine) a Pack is the first step since it will identify any uncorrectable problems.  If it can't resolve the issue, see Handling Corruption below.

For more information see How To - Pack a Database.

Handling Corruption

If you store data on enough computers or access it over a network long enough at some point it'll get corrupted.  It may be due to an underlying hardware or OS problem, running out of disk space at a really bad moment, a hard drive writing data out of order or any of a number of esoteric reasons.  When this happens to your application you want to be ready.  If you find yourself with a database that is throwing odd errors when you access it what do you do?

1. Back Up the Database: While working to fix the problem it's useful to have a baseline to be sure if something goes really wrong while fixing it you can get back to your initial state.
2. Pack the Database: Since the Pack process rebuilds the data file completely and in the process discards the original index data and rebuilds it many examples of file corruption will simply be ignored by the Pack process and the result is a file that's ready to go.  If not, the Pack process will provide a clear technical fault that can be used to determine the appropriate next step.
3. Repair the Database: VistaDB has a Repair process that will attempt to create a valid VistaDB database by dropping indexes that can't be created (typically due to constraint violations) and data pages that can't be read.  Be sure you have a backup at this point because Repair may end up dropping more data than you can live with.  Otherwise, you can use the repaired database and add back missing indexes once you've corrected the data constraint problems.
4. Database Recovery: If all else fails, Gibraltar Software offers a service where we will step through the database in a debugger and hand-correct problems to attempt to recover as much data as possible from the database.  There is a fee for this service.  Contact Support for more information.

The most common sources of file corruption are:

• Running out of disk space: If the VistaDB engine can't write out all pages when it needs to flush data this can create problems where indexes and table rows are out of sync.  This is typically fixable with a normal Repair with no data loss.
• Network problems: If a database is on a network drive, particularly an inexpensive NAS device that doesn't perfectly implement the SMB protocol or underlying disk write semantics a network interruption can cause the data file to be damaged.

For more information on repairing a database see How To - Pack a Database.

Handling Transient Errors

Some errors VistaDB throws indicate a clear problem not worth retrying - like a missing foreign key value or SQL code that won't parse.  Like SQL Server, there are some errors that you should retry because they probably will not recur.  In VistaDB the most common retryable errors are the Concurrency Error and Lock Timeout Error. 

Exceptions will often wrap other exceptions so it's important to check each VistaDB exception in the exception chain.  To help detect this, when you perform a database operation in a Try/Catch block you can use the Contains method to see if a particular error code is anywhere in the exception chain.  The following code sample shows a simplistic approach to this:

public void ExecuteWithRetry(DbCommand command)
{
    try
    {
        command.ExecuteNonQuery();
    }
    catch (VistaDBException ex)
    {
        if (ex.Contains(Errors.dda_TransactionConcurrency) || ex.Contains(Errors.dda_LockTimeout))
        {
            //this is a retryable error
            ExecuteWithRetry(command);
        }
        else
        {
            throw; //we don't want to retry these so let the exception continue to propagate
        }
    }
}

Public Sub ExecuteWithRetry(command As DbCommand)
    Try
        command.ExecuteNonQuery()
    Catch ex As VistaDBException
        If ex.Contains(Errors.dda_TransactionConcurrency) OrElse ex.Contains(Errors.dda_LockTimeout) Then
            'this is a retryable error
            ExecuteWithRetry(command)
        Else
            'we don't want to retry these so let the exception continue to propagate
            Throw
        End If
    End Try
End Sub

In your application you should determine a suitable place to put retry logic that minimizes the code you write and fits with your architecture and data access approach.

For more information, see How To - Handle SQL and VistaDB Exceptions.