Backups and Database Maintenance: Protecting Your Data

Since each database may contain a large amount of valuable information that would be difficult to re-create, it is essential to make sure that you are diligent about storing adequate backups and performing the necessary maintenance activities to keep the database operating smoothly. The necessary procedures vary depending on the type of database.

Enterprise Database Maintenance

When you choose to store analysis information in an enterprise database, a database administrator must perform backups and database maintenance activities using the data management tools that are packaged with and/or designed for the database platform (e.g., SQL Server Enterprise Manager for SQL Server). Each individual organization typically establishes its own procedures for protecting the data stored in the Oracle or SQL Server databases. At minimum, we recommend the following:

  • For SQL Server:

    • Create a Database Maintenance Plan that creates a Full backup within SQL Server.

    • Create a Database Maintenance Plan that creates a Transaction Log backup within SQL Server.

  • For SQL Server Express:

    • Create a Backup on Demand Using SQL Server Management Studio Express.

    • Automate Backups Using SQL Server Management Studio Express and Windows Task Scheduler.

  • For Oracle:

    • Create a schedule for regular daily/nightly backups within Oracle.

Standard Database Maintenance

When you choose to store analysis information in a standard database, it will be subject to the same limitations and vulnerabilities as any other file that uses the Microsoft Access® database file format. For example, the maximum file size is ~ 2GB, maximum number of concurrent users is 255, etc.

It is also important to understand the inherent vulnerabilities of Access databases. Microsoft Jet, the database engine that is used in Microsoft Access, is a file sharing database system. When Microsoft Jet is used in a multi-user environment, multiple client processes are using file read, write, and locking operations on a shared database. Because multiple client processes are reading and writing to the same database and because Jet does not use a transaction log (as do the more advanced database systems, such as SQL Server), it is not possible to reliably prevent any and all database corruption.

Although our developers have made every effort to reduce or eliminate the possibility that the software will induce a database error, there is no way to absolutely prevent corruption that might be caused by other factors, such as faulty network hardware, an unexpected "crash" on your computer or a network interruption. Therefore, this section provides some recommendations for standard precautions that all users can take to protect the data in their standard databases from this type of corruption and reduce the impact of the data loss if corruption is unavoidable.

  1. Create backups regularly. As with any resource that contains a large amount of valuable information that would be difficult to re-create, it is essential to make sure that you are diligent about creating and storing backup files. There are a number of ways this can be accomplished:
    • If you select Automatically back up database upon closing from the Backup/Check Out Options page of the Application Setup, the ReliaSoft desktop application that you use to open the database will back up the database every time you close the file.
    • If you have a database open and choose File > Save As, the application will create a copy of the database to a pathname/filename of your choosing.
    • If you browse to the database file (*.rsr24) in one of the Windows file management tools (such as My Computer or Windows Explorer), you can copy and paste the database file as needed.
  2. Compact and repair regularly. Using the "Compact and Repair" feature will help to reduce the size of the database file and help to protect against problems with the operation of the database. If you have the database open and it is not currently in use by another user, you can initiate the process by at any time by choosing File > Manage Database > Compact and Repair.

  3. Do not store the database in a shared network location if you suspect that your network connection and/or hardware may be unreliable. Faulty network hardware is one of the main reasons why a file that uses the Microsoft Access database file format may become corrupted. The cause can be one or more links in the hardware chain between the computer that the database resides on and the computer that has the database open. This list includes, but is not limited to, network interface cards, network cabling, routers, and hubs. Hardware-based corruption is typically indicated by .mdb files that cannot be restored through the use of compacting, repairing or Jetcomp. Hardware corruption will typically recur until the responsible hardware is repaired or replaced.

If you have experienced this type of corruption for a standard database file, it is recommended that you take steps to correct the network problem or refrain from accessing database files over the network. In such cases, you may choose to use an enterprise database instead (i.e., Oracle or SQL Server), which would be less vulnerable to network interruptions. Alternatively, you could keep multiple analysis projects together in a single shared standard database file but ask users to export the analysis to a separate "working" database on their own computers when there is a need to make substantial modifications. Users could then import the data back into the shared database after the modifications have been completed.

  1. Do not allow the file size of the database to grow too large. Performance will be affected by the size of the database and the number of simultaneous users. Therefore, it is important for users to monitor the sizes of their database files and take steps to export the data into several smaller and more manageable files if they become too large. Please be aware of the following factors, which can lead to very large database files:
    • Failure to compact and repair the database on a regular basis.
    • Using a very large number of attached documents. In some cases, using a link instead of an attachment may provide equivalent functionality with a much smaller impact on the size of the database file.

If you try to open a standard database via the software and receive a message that says "Unable to open the database," this is an indication that the database file may have become corrupted. Please contact Technical Support and provide as much information as possible about exactly what you were doing when the corruption occurred. Whenever possible, please provide a copy of the corrupted file. In some cases, we may be able to provide assistance with salvaging some or all of the affected data. However, in many cases, the best recourse may be to restore the latest backup from before the corruption occurred.

Related Topics and Links