Fix "SQLite Database Disk Image Is Malformed"

Overview

Copying Historian.db, or writing to it with another program, while the EnvisionSCADA site is running can produce a damaged database file. Reading a damaged file fails with the error "SQLite Database Disk Image Is Malformed". Historian.db holds the historic data, the historic alarms, the historic events and the audit trail.

The .recover command of the SQLite command-line tool repairs the file. It reads the damaged Historian.db and writes every record it can read into a new database file with the same tables, indexes, views and database version. The new file then replaces the damaged file. Records in a damaged part of the file can be absent from the new file.

Before You Start

Prepare the following before repairing the historian.

• Plan a maintenance window. While the EnvisionSCADA site is stopped, no values are collected and no history is logged. The repair takes longer as the file grows.
• Locate Historian.db. By default it is stored in the Database folder of the installation folder, C:\inetpub\wwwroot\EnvisionSCADA\Database. When Historian Database Path in the Historian section of the Parameters page holds a path, the file at that path is in use.
• Check the free disk space. The new file is written next to Historian.db and takes about the size of Historian.db. The backup copy takes the same size again.
• Download the SQLite command-line tools for Windows, sqlite-tools-win-x64, from sqlite.org/download.html on a computer with internet access. Extract sqlite3.exe from the zip file and copy it to the server. The examples in this article use the folder C:\SQLite.

Repair the Historian Database

The example repairs Historian.db in the default Database folder. Access to that folder is limited to SYSTEM, the Administrators group and IIS_IUSRS, so run the commands in a Command Prompt that is started with Run as administrator. Use Command Prompt for these commands, as described in Troubleshooting.

• In IIS Manager, select the EnvisionSCADA site and click Stop.
• Copy Historian.db to a backup location. When a file named Historian.db-journal is present next to it, copy that file as well.
• Start Command Prompt with Run as administrator and go to the folder of Historian.db.

cd /d C:\inetpub\wwwroot\EnvisionSCADA\Database

• Recover the database into the new file Historian-recovered.db. The command returns when the new file is complete.

C:\SQLite\sqlite3.exe Historian.db .recover | C:\SQLite\sqlite3.exe Historian-recovered.db

• Check the new file. The command returns ok for an intact database. It reads the whole file and takes longer as the file grows.

C:\SQLite\sqlite3.exe Historian-recovered.db "PRAGMA integrity_check;"

• Compare the database version of both files. Both commands return the same number, for example 12.

C:\SQLite\sqlite3.exe Historian.db "PRAGMA user_version;"
C:\SQLite\sqlite3.exe Historian-recovered.db "PRAGMA user_version;"

• Replace the damaged file with the new file. The second line renames a remaining Historian.db-journal file together with the damaged file.

ren Historian.db Historian-damaged.db
if exist Historian.db-journal ren Historian.db-journal Historian-damaged.db-journal
ren Historian-recovered.db Historian.db

• In IIS Manager, select the EnvisionSCADA site and click Start.

Check the Result

Verify the repair before removing the damaged file.

• The login page opens without a message that begins with Database Error.
• The Historian Viewer shows records from before the repair as well as records logged after it.
• Keep Historian-damaged.db and the backup copy until these checks are complete. Remove them afterwards to free the disk space.

Troubleshooting

Each row lists a situation that affects the result of the repair, its effect and the action to take.

C:\SQLite\sqlite3.exe Historian-recovered.db "PRAGMA user_version = 12;"