[justinmiller87]

For those of us on Linux, here's a bash script that I made based on your PS script that does the same thing:

#!/bin/bash
# -------------------------------------------------------------------
# calibre-fix-libraries.sh
#
# Purpose:
# - Back up metadata.db into DBbackups/<LibraryName>/
# - Add missing legacy columns (flags, isbn, lccn) to the books table
# - Safe to re-run
# - Keeps only the most recent N backups per library
#
# IMPORTANT:
# - Close Calibre before running this script
# -------------------------------------------------------------------

# Path to sqlite3 command-line executable
SQLITE3=$(command -v sqlite3)

# Calibre libraries to process
LIBRARIES=(
    "/path/to/Calibre_Library"
)

# Central folder where database backups are stored
BACKUPS_ROOT="/path/to/DBbackups"

# Number of backups to keep per library
KEEP_BACKUPS=10

# -------------------------------------------------------------------
# Sanity checks
# -------------------------------------------------------------------

if [ -z "$SQLITE3" ] || [ ! -x "$SQLITE3" ]; then
    echo "Error: sqlite3 not found or not executable. Please install sqlite3."
    exit 1
fi

mkdir -p "$BACKUPS_ROOT"

# -------------------------------------------------------------------
# Process each library
# -------------------------------------------------------------------

for lib in "${LIBRARIES[@]}"; do
    if [ ! -d "$lib" ]; then
        echo "FAILED: Library directory not found: $lib"
        continue
    fi

    # Friendly name of the library folder
    libName=$(basename "$lib")

    # Full path to this library's metadata.db
    dbPath="$lib/metadata.db"

    if [ ! -f "$dbPath" ]; then
        echo "FAILED: metadata.db not found in $lib"
        continue
    fi

    # Folder where backups for this library will live
    backupDir="$BACKUPS_ROOT/$libName"
    mkdir -p "$backupDir"

    # Generate a timestamp for the backup filename
    timestamp=$(date +"%Y%m%d_%H%M%S")
    backupPath="$backupDir/metadata.db.backup_$timestamp"

    # Step 1: Back up metadata.db
    if ! cp "$dbPath" "$backupPath"; then
        echo "FAILED: $libName - Could not back up metadata.db"
        continue
    fi

    # Step 2: Read existing columns from the books table
    # Query SQLite for column names in the books table
    cols=$("$SQLITE3" "$dbPath" "SELECT name FROM pragma_table_info('books');" 2>/dev/null)
    if [ $? -ne 0 ]; then
         echo "FAILED: $libName - Could not read from database"
         continue
    fi

    changed=false

    # Step 3: Add missing columns
    # Check for 'flags'
    if ! echo "$cols" | grep -qx "flags"; then
        "$SQLITE3" "$dbPath" "ALTER TABLE books ADD COLUMN flags INTEGER DEFAULT 1;"
        changed=true
    fi

    # Check for 'isbn'
    if ! echo "$cols" | grep -qx "isbn"; then
        "$SQLITE3" "$dbPath" "ALTER TABLE books ADD COLUMN isbn TEXT;"
        changed=true
    fi

    # Check for 'lccn'
    if ! echo "$cols" | grep -qx "lccn"; then
        "$SQLITE3" "$dbPath" "ALTER TABLE books ADD COLUMN lccn TEXT;"
        changed=true
    fi

    # Step 4: Backup retention
    # Get all backup files for this library, newest first
    # using ls -t (sort by modification time, newest first)
    backups=($(ls -t "$backupDir"/metadata.db.backup_* 2>/dev/null))
    
    # If there are more backups than allowed, delete the oldest ones
    if [ "${#backups[@]}" -gt "$KEEP_BACKUPS" ]; then
        for (( i=$KEEP_BACKUPS; i<${#backups[@]}; i++ )); do
            rm -f "${backups[$i]}"
        done
    fi

    # Step 5: Status output
    if [ "$changed" = true ]; then
        echo "UPDATED: $libName"
    else
        echo "NO CHANGE: $libName"
    fi
done

You can confirm that they have been added with one or both of the following tests:

sqlite3 /path/to/Calibre_Library/metadata.db "PRAGMA table_info(books);" | grep -E "flags|isbn|lccn"

sqlite3 /path/to/Calibre_Library/metadata.db "Select name FROM pragma_table_info('books');"

Originally Posted by GeniusJ

Glsparks2, your code didn't work for me as expected (I blame myself) so I tweaked it a bit. This is now working for me.

PowerShell script to restore missing columns in Calibre metadata.db across all libraries (with backups)

If a third-party tool expects older columns in Calibre’s metadata.db and they are missing, this PowerShell script will add them back to the books table if needed. It does this for every Calibre library under a root folder. It also makes timestamped backups of each metadata.db in a separate DBbackups folder.

What you need

Windows
This is for Windows PowerShell.

sqlite3.exe (the command-line tool)
Download the SQLite tools zip from sqlite.org. You want the zip named like: sqlite-tools-win-x64-<version>.zip
Extract it and make sure you have sqlite3.exe.

Place sqlite3.exe somewhere stable, then update the script path. Example location:
C:\Tools\sqlite\sqlite3.exe
You can also put it in another folder if you prefer. Just point the script at it.

Your Calibre library root folder
This is the folder that contains your library folders, each with a metadata.db inside.

Important safety note
Close Calibre before running this. Calibre can keep metadata.db open.

What it does

Finds every library folder under a root path that contains metadata.db

Creates a matching backup folder under DBbackups<LibraryName>\

Copies metadata.db to a timestamped backup file

Adds these columns to the books table if they do not exist:

flags INTEGER DEFAULT 1
isbn TEXT
lccn TEXT

You should only need to run this script once after Calibre updates to 9.x or any minor revision after that. So if you apply a Calibre update, open the updated version of Calibre, close it, then run this script to put the columns back in the database.

Script
Copy this into a file named something like: Calibre-Fix-AllLibraries.ps1

# -------------------------------------------------------------------
# Calibre-Fix-AllLibraries.ps1
#
# Purpose:
# - Scan a Calibre root folder for library directories ending in " Library"
# - For each library:
#   - Back up metadata.db into DBbackups\<LibraryName>\
#   - Add missing legacy columns (flags, isbn, lccn) to the books table
# - Safe to re-run:
#   - If columns already exist, no schema change occurs
# - Keeps only the most recent N backups per library
#
# IMPORTANT:
# - Close Calibre before running this script
# -------------------------------------------------------------------

# Path to sqlite3 command-line executable
# This must be the sqlite3.exe tool, not the DLL
$Sqlite3 = "C:\Windows\System32\sqlite3.exe"

# Root folder that contains all Calibre libraries
# Each actual library is a subfolder ending in " Library"
$CalibreRoot = "R:\Jim\Dropbox\Books\Calibre"

# Folder where database backups are stored
# Each library gets its own subfolder under here
$BackupsRoot = Join-Path $CalibreRoot "DBbackups"

# Number of backups to keep per library
# Older backups beyond this count are deleted
$KeepBackups = 10

# -------------------------------------------------------------------
# Sanity checks
# -------------------------------------------------------------------

# Ensure sqlite3.exe exists
if (-not (Test-Path $Sqlite3)) {
  throw "sqlite3.exe not found: $Sqlite3"
}

# Ensure the Calibre root folder exists
if (-not (Test-Path $CalibreRoot)) {
  throw "Calibre root not found: $CalibreRoot"
}

# Ensure the DBbackups root folder exists
# -Force means it will not error if it already exists
New-Item -ItemType Directory -Force -Path $BackupsRoot | Out-Null

# -------------------------------------------------------------------
# Discover library folders
# -------------------------------------------------------------------

# Find all directories under the Calibre root that:
# - End with " Library"
# - Contain a metadata.db file
$libraries = Get-ChildItem -Path $CalibreRoot -Directory -Force |
  Where-Object { $_.Name -like "* Library" } |
  Where-Object { Test-Path (Join-Path $_.FullName "metadata.db") }

# If no libraries are found, stop immediately
if (-not $libraries) {
  throw "No library folders ending with ' Library' containing metadata.db found under: $CalibreRoot"
}

# -------------------------------------------------------------------
# Process each library
# -------------------------------------------------------------------

foreach ($lib in $libraries) {

  # Friendly name of the library folder
  $libName = $lib.Name

  # Full path to this library's metadata.db
  $dbPath = Join-Path $lib.FullName "metadata.db"

  # Folder where backups for this library will live
  $backupDir = Join-Path $BackupsRoot $libName

  # Ensure the library-specific backup folder exists
  New-Item -ItemType Directory -Force -Path $backupDir | Out-Null

  # Generate a timestamp for the backup filename
  $timestamp = Get-Date -Format "yyyyMMdd_HHmmss"

  # Full path to the backup file
  $backupPath = Join-Path $backupDir "metadata.db.backup_$timestamp"

  try {

    # ---------------------------------------------------------------
    # Step 1: Back up metadata.db
    # ---------------------------------------------------------------

    # Copy metadata.db to the backup folder
    # -ErrorAction Stop ensures we do not proceed if backup fails
    Copy-Item -Path $dbPath -Destination $backupPath -ErrorAction Stop

    # ---------------------------------------------------------------
    # Step 2: Read existing columns from the books table
    # ---------------------------------------------------------------

    # Query SQLite for column names in the books table
    $cols = & $Sqlite3 $dbPath "SELECT name FROM pragma_table_info('books');"

    # Normalize output:
    # - Trim whitespace
    # - Remove empty lines
    $cols = $cols | ForEach-Object { $_.Trim() } | Where-Object { $_ }

    # Track whether any schema change is made
    $changed = $false

    # ---------------------------------------------------------------
    # Step 3: Add missing columns
    # ---------------------------------------------------------------

    # Add flags column if missing
    if ($cols -notcontains "flags") {
      & $Sqlite3 $dbPath "ALTER TABLE books ADD COLUMN flags INTEGER DEFAULT 1;"
      $changed = $true
    }

    # Add isbn column if missing
    if ($cols -notcontains "isbn") {
      & $Sqlite3 $dbPath "ALTER TABLE books ADD COLUMN isbn TEXT;"
      $changed = $true
    }

    # Add lccn column if missing
    if ($cols -notcontains "lccn") {
      & $Sqlite3 $dbPath "ALTER TABLE books ADD COLUMN lccn TEXT;"
      $changed = $true
    }

    # ---------------------------------------------------------------
    # Step 4: Backup retention
    # ---------------------------------------------------------------

    # Get all backup files for this library, newest first
    $backups = Get-ChildItem -Path $backupDir -File -Filter "metadata.db.backup_*" -ErrorAction SilentlyContinue |
      Sort-Object LastWriteTime -Descending

    # If there are more backups than allowed, delete the oldest ones
    if ($backups.Count -gt $KeepBackups) {
      $backups | Select-Object -Skip $KeepBackups | Remove-Item -Force -ErrorAction SilentlyContinue
    }

    # ---------------------------------------------------------------
    # Step 5: Status output
    # ---------------------------------------------------------------

    if ($changed) {
      Write-Host "UPDATED:" $libName
    } else {
      Write-Host "NO CHANGE:" $libName
    }
  }
  catch {
    # Catch and report any error for this library without stopping the script
    Write-Host "FAILED:" $libName "-" $_.Exception.Message
  }
}