Syncing a live production database without locking it
You need the data out of production — for a reporting database, a new system that runs side by side with the old one, or a staging refresh — and production cannot stop. This guide shows how dbSlice reads a live source with as little impact as possible, what to check before the first run, and what to expect if the run is interrupted.
Try it free on the Microsoft Store · See pricing
At a glance
- The source is read in keyset pages: many short queries instead of one long one.
- Read access on the source and write access on the target are enough.
- By default nothing is created in either database: no tables, triggers or temporary objects.
- Interrupted runs resume from the last checkpoint.
- After the run, Record Diff shows which keys are still missing.
1. Why one big query hurts a live system
The simplest way to copy a table is SELECT * FROM big_table and stream the result. On a system that is in use, that query stays open for the whole copy: minutes or hours of a long-running cursor, held resources and, depending on the isolation level, locks that other sessions have to wait for. If it fails at 90%, it starts again from zero.
2. Keyset paging: many short reads
dbSlice reads the source page by page, ordered by a key column:
SELECT ... FROM orders WHERE order_id > @last_key ORDER BY order_id -- one pageEach page is a short query that finishes and releases what it used. The next page starts after the last key read. Nothing is held open between pages, so the source keeps serving its users while the copy runs.
Choose the key well. Use a unique, indexed column — usually the primary key. A unique, indexed key keeps every page cheap and makes the order stable. For keys made of several columns, dbSlice has a composite key ordering mode, because some engines sort ORDER BY differently from how they compare with >.
3. Ask your DBA for less
| What dbSlice needs | Source | Target |
|---|---|---|
| Permission | Read the table or view | Insert/update rows in the target table |
| Objects created | None | None by default |
If the target table does not exist yet, dbSlice stops before writing and shows the CREATE TABLE script translated from the source schema, for your DBA to review and run. Some faster write modes use a temporary table; they are off by default and you turn them on only if your DBA allows it.
4. Pick the mode for the job
| Mode | What it does to the target | Typical use |
|---|---|---|
| Initial load | Inserts into an empty table | First copy |
| Insert new | Inserts keys that are missing; existing rows untouched | Catching up an append-mostly table |
| Upsert | Inserts new keys and updates changed rows | Keeping a copy in step |
| Update only | Updates rows that already exist | Refreshing values without adding rows |
| Full reload | Empties the target table and loads it again | Small reference tables |
Incremental runs. Point the job at a column that only grows, such as updated_at or a row version. Each run remembers the highest value it read, and the next run reads only rows above it. Combined with Upsert, a nightly refresh reads what changed instead of the whole table. Deletes at the source are not detected by an incremental run: schedule a periodic comparison or a full reload if rows are deleted.
5. Before the first write
- Check the environment badges. Connections are tagged DEV, QAS, UAT or PROD with their own colors, and dbSlice warns before a run that crosses environments — for example, production data going into a test target.
- Mask what should not leave production. Sensitive columns can be obfuscated in flight, with keys and foreign keys preserved, and you can preview the result on a sample row.
- Start small. Run the first sync against a non-production target, or with a WHERE filter on a slice of the table, and compare the result before the full run.
6. During the run
Reading and writing overlap: while one page is written to the target, the next one is already being read from the source, so the slower side sets the pace. Where the target engine has a native bulk path — SqlBulkCopy on SQL Server, binary COPY on PostgreSQL for initial loads — dbSlice uses it.
The status panel shows start time, elapsed time, the estimated end and the row counters. Cancel stops the run at a safe point; the partial work is recorded in the history.
7. When the run is interrupted
Rows are written page by page. If the network drops, the target restarts or you cancel, the run resumes from the last checkpoint instead of starting over, and transient failures are retried. In Upsert mode a page that is written again updates the same keys instead of duplicating them.
8. Verify with Record Diff
After the run, Record Diff compares the keys on both sides and reports, per table, how many rows each side has and which keys are missing. The report lists the table, the key, both sides, when each list was read, and the divergent keys ready to paste into an IN (...) clause for a closer look.
9. Make it a scheduled job
Save the run as a job and execute it from the command line or Windows Task Scheduler. Jobs can send an e-mail or call a webhook when they succeed or fail, so the nightly sync reports back to whoever is on call.
Speed, with the conditions
One measured run: 31.4 million rows into Azure SQL in about 30 minutes (≈17,200 rows/s), initial-load mode, from a SQLite source on a local disk, through the desktop app (dbSlice 2.3, September 2026, before later optimizations). In that run, reading the source was the bottleneck; the writes to Azure overlapped with it.
Throughput depends on volume, network, engines, row width and transformations. Your numbers will differ — try it on your own data before you plan a window.
What dbSlice does not do
- It is not real-time replication or change data capture. Runs are jobs, on demand or scheduled, at the interval you choose.
- It does not sync in both directions. Each job copies one way, from source to target.
- It moves data, not code. Procedures, triggers and views are not converted.
Works with
SQL Server, PostgreSQL, Oracle, MySQL, MariaDB, Firebird, SQLite and InterSystems Caché/IRIS, in any pair, and MongoDB, Cassandra, Elasticsearch, Couchbase or Redis as targets. See also Sync SQL Server to PostgreSQL and Move the right data, mask it before it arrives.
Try it
The free version on the Microsoft Store connects to your databases, shows the data, compares structures and exports to CSV, with no license key. Syncing writes to the target and needs a Desktop or Server license (perpetual, 14-day money-back guarantee).