Moving Benson Data Between Computers: Restore Failures and Field Sync

What's happening

Two different jobs get confused with each other, and using the wrong one for the second job destroys data. Setting up a new computer means copying an entire database onto a machine that has nothing on it yet — that's a backup and restore. Getting field-laptop tests back to the main computer means adding new records to a database that already has years of records in it — that's an Extract and Merge, and it is a completely different operation.

Never use Restore to bring field data back. A restore replaces the destination database entirely. Run it in that direction and the main computer's history is gone. If you only read one line of this guide, read that one.

First: the missing SQL Server service is normal

When a new computer won't take a restore, the natural first check is whether the database engine is installed — and on version 8.x it looks like it isn't. Open Services and you'll typically find only SQL Server VSS Writer, with no SQL Server (something) entry. Open C:\Program Files\Microsoft SQL Server and you'll see numbered folders (80, 90, 110, 120, 160) and a Client SDK folder, but nothing named MSSQL##.

Nothing is missing. Benson version 8.0 and later installs SQL Server Express LocalDB, which runs as a user process rather than a Windows service, so it never appears in the Services list. Benson's manual gives the instance name as BMILOCALDBSERVER. Software that predates version 8.0 installed full SQL Server Express instead, and that older engine does appear as a service — which is why an older machine and a newly set-up one can look completely different while running the same version of the program.

That difference also explains a speed gap. The older engine and LocalDB are not equally fast at the same operation, so a restore that finishes quickly on a long-established computer can crawl on a brand-new one.

The "Use SQL LocalDB" checkbox decides which database you open

At the Welcome dialog when the program launches, the Use SQL LocalDB checkbox controls which engine the program connects to. Checked, it uses the local LocalDB instance. Unchecked, it attempts to connect to an older-style instance on that machine instead.

Toggling that box does not move your data. It points the program at a different database, which may be empty or may hold an older copy. If you tick or untick it while troubleshooting and your records appear to vanish or reports suddenly start working, the data didn't change — you're looking at a different database. Note which state you were in when things looked right, and put it back.

Choose the right backup method — this is usually the whole problem

The Backup dialog offers three database types, and they behave very differently:

• File database (.mdb) — exports and rebuilds the data record by record. Available on every package, and by far the slowest.
• SQL database — creates a live database equivalent to the one being backed up. Plus Package only.
• TSQL file (.bak) — Plus Package only, and per Benson's manual it "is the fastest of the three methods," because the database is treated as a single file and "the complexity of the database is ignored."

If a restore fails with a timeout, the method is the first thing to change. A file-database restore of a database with years of tests in it can easily run past the time the program is willing to wait, while the same data restores from a TSQL file quickly. Use TSQL whenever your package offers it.

One limit worth knowing in advance: TSQL backups are only available when you are logged into a database on the local machine. If the program is connected to a database elsewhere, that option won't work.

"Query timeout expired" (error 80040E31)

This error — sometimes shown as a large negative number such as -2147217871, which is the same value — means the program gave up waiting for the database to finish. It does not mean your backup file is bad, and it does not mean the database engine rejected anything. The operation was still running when the program stopped waiting.

1. Switch to a TSQL file backup and restore if your package allows it. This resolves most timeouts on its own, because the operation finishes far faster.
2. Make sure the backup file is on the computer's own drive, not a USB stick or a network share.
3. Plug the laptop in and set the Windows power plan to High Performance. Battery-saving settings throttle disk and processor enough to matter here.
4. Check free disk space — allow roughly two to three times the size of the backup file.

"Access is denied" / operating system error 5

If the backup fails with Cannot open backup device and operating system error 5: access is denied, this is a Windows permissions issue, not a database fault. The database engine connects using your Windows account, and Windows does not allow ordinary accounts to write files directly to the root of the C: drive.

1. Create a folder for the purpose, for example C:\CCABackup.
2. In the Backup dialog, type the full path into the file box — for example C:\CCABackup\stx.bak — rather than accepting a default that points at the root of a drive or at a drive letter the computer doesn't have.
3. Run the backup again.

Apply the same rule on the receiving computer: put the file in a real folder, not at the top of C:.

"The current active database is located on a network server" when it isn't

When you choose a TSQL file and click Browse, you may get a message saying the active database is on a network server and that it can't browse for the path — even when Use SQL LocalDB is checked and the database is unquestionably on that machine.

Read it as a warning about how the path is interpreted, not as a statement that your data is somewhere else. With a TSQL backup the file path is resolved by the database engine rather than by the window you're clicking in, so the program disables the file browser and asks you to type the path instead. Type the full path and continue. The Browse button staying greyed out is expected here and is not a sign that something is misconfigured.

Getting field-laptop tests onto the main computer

This is the part where using the wrong tool costs you data. A restore overwrites the destination, so restoring a field laptop's database onto the main computer would replace everything already there. The correct tools are Extract and Merge (Plus Package), which Benson describes as intended "for data transfer and synchronization between two Benson Medical Instruments products."

On the field laptop, after testing

1. Choose Extract from the File menu.
2. For Database Type choose File database.
3. Type a path in a folder you've created, such as C:\CCABackup\stxextract.mdb, rather than accepting a default drive letter that may not exist.
4. Choose the Extract Configuration that matches what you want to send — the factory configurations cover common cases, and custom ones can be defined.
5. Choose OK.

Extract only copies. Per the manual, "records are only copied, not removed from the existing database," so the laptop keeps its own records intact.

On the main computer

1. Copy the extract file across.
2. Choose Merge from the File menu, choose File database, and point at that file.
3. Select the Trial Merge checkbox and run it first. Nothing is written; a log file is produced instead.
4. Read the log — it shows how many records would merge, what errors would occur, and which records would be ignored.
5. If the log looks correct, clear Trial Merge and run it again for real.

Benson is explicit about why the trial run exists: "merging data into the database carries the risk of overwriting data that is needed." Treat Trial Merge as a required step, not an optional one.

Two things that catch people out

Company names must match exactly. Merge matches records to companies by an exact match on the Company Name field, and "if there is no exact match, a new company is created." A company entered slightly differently on the laptop won't merge into the existing company — it will silently create a second one. Standardize company names across both machines before the first merge, and check the trial log for unexpected new companies.

Extract to a file, not to a SQL database. Both machines typically use a database named STX, and the manual notes you "cannot merge from a database that has the same name as the one you are currently logged into, even if the database is on a different server." Extracting to a file avoids that conflict entirely.

Can two computers share one database instead?

Sometimes, but not with LocalDB. Benson's Technical Reference Guide states that LocalDB "doesn't allow external connections" — so a database on LocalDB cannot be reached from another computer no matter how the network or firewall is configured.

Sharing requires the database to live on a real SQL Server instance that accepts network connections, with the other computers pointed at it from the Welcome dialog, and it is a Plus Package feature. That is a genuine IT setup rather than a checkbox, and an aging database engine can bring its own problems — if reporting or printing has been unreliable on an older machine, the age of the engine is worth investigating before building more on top of it. Call us before starting; getting the engine choice right the first time is much less work than moving the data twice.

When to call us

• A restore or merge failed partway through, or you're unsure whether records were lost.
• A merge created duplicate companies and you need them combined.
• You want the field laptop and the main computer to share one database rather than syncing.
• Reports or printing stopped working after a software upgrade on an older computer.
• You're about to move data between computers and would rather have us on the phone while you do it.

Call (513) 891-0868 or email service@fosterinstruments.com. If a transfer has already gone wrong, stop before running anything else — the recovery options are much better before another operation writes over the evidence.