Home / Guides / Restore the nightly eClinicalWorks backup

Restore the nightly eClinicalWorks backup to your own SQL Server

If your practice is hosted by eClinicalWorks (eCW), a nightly backup of your data can be delivered to you. A backup file on its own answers no questions. This guide explains what it takes to turn it into a SQL Server database you can query every morning, and what tends to break.

Short answer.

You keep a read-only copy of the database current by restoring each night's transaction log backup on top of it, in order, with no night missing. The restore itself is a few lines of T-SQL. The work is the nightly download, the extract, the logging, and noticing the morning something failed, because one missed night stops every later night from applying.

Why restore it at all

A current local copy lets you run any SQL query or SSRS report against your own data, without going through the hosted application. It is the practical route to local reporting and analysis. Many practices never use the backup, because the setup needs a person who knows Azure storage and SQL Server restores, and someone has to watch it every day.

What eClinicalWorks provides, and how, depends on your arrangement with them. Ask your eCW contact how nightly backups are offered for your account. This guide covers the SQL Server side. It is not affiliated with or endorsed by eClinicalWorks.

How a log-based copy works

SQL Server can keep a copy current from transaction log backups. You restore one starting backup, then apply each log backup after it, in order. Microsoft's rule is that you must apply them in the order they were created, without any gaps in the log chain.

Between log restores you can leave the database in standby mode, which gives read-only access. Each night you apply that night's log backup:

-- Once: restore the starting backup and leave the copy readable between log restores.
RESTORE DATABASE [YourEcwCopy] FROM DISK = N'D:\Ecw\starting.bak'
    WITH STANDBY = N'D:\Ecw\YourEcwCopy.stby';

-- Every night: apply the new log backup, in order, with no night skipped.
RESTORE LOG [YourEcwCopy] FROM DISK = N'D:\Ecw\nightly.trn'
    WITH STANDBY = N'D:\Ecw\YourEcwCopy.stby';

Three practical points about that restore:

The restore needs exclusive access. Anyone connected to the copy has to be disconnected first. Microsoft's own log shipping has an option to disconnect users automatically while it restores.
The standby file is an undo file that the restore relies on. Keep it with the database files and do not delete it between restores.
If a night is missed, later log backups will not apply. The fix is a new starting restore, which is why gaps cost days rather than hours.

What the nightly job has to do

Download the latest backup from the Azure Blob Storage container that eCW assigns to you.
Extract the zip and find the transaction log backup inside it.
Restore it to the copy, in standby mode, as above.
Record the date, how long it took and whether it worked.
Alert a person when any of those fails, and when a night is missing altogether.

Steps 1 to 3 are a script. Steps 4 and 5 are what turn a script into something you can depend on, and they are the part most home-built setups skip. A job that fails quietly for a week leaves you with a week-old copy and a rebuild to do.

What usually goes wrong

A night is missed (the download failed, the disk was full, the server was down), and the log chain breaks.
Someone is connected to the copy when the restore starts, so the restore fails.
The source changes. The location, the credentials or the format of the backup can change, and a hand-built script stops working without a sound.
Nobody is watching. The job runs at night, so nobody sees the failure until a report looks wrong.

Handle patient data like patient data

The restored database contains patient information. Keep it on a SQL Server you control, limit who can query it, and apply the same access controls and auditing you apply to the rest of your patient data. This guide is not legal or compliance advice.

Questions

Can I query the restored database while it is in standby?

Yes. Standby mode leaves the database available for read-only access between log restores. Connections have to be closed when the next restore runs.

What happens if one night's backup is missing?

Later log backups cannot be applied, because the log chain has a gap. The copy has to be rebuilt from a new starting restore. Catching the missing night the next morning keeps that to a day.

Does RestoreMD send my data to the cloud?

No. It is downloaded from the Azure Blob Storage that eCW provides and restored to a SQL Server that you control. It is not sent to any third-party cloud, and it stays inside your network.

Is RestoreMD part of eClinicalWorks?

No. RestoreMD is an independent service from Solutions Group Unlimited and is not affiliated with, endorsed by, or sponsored by eClinicalWorks.

How much does it cost?

See the RestoreMD page for the current price. Setup, monitoring and support are included.

Sources (Microsoft Learn): Restore a transaction log backup, Secondary database settings (log shipping), RESTORE arguments (STANDBY).

eClinicalWorks® is a registered trademark of eClinicalWorks, LLC. RestoreMD is an independent service and is not affiliated with, endorsed by, or sponsored by eClinicalWorks.