SharePoint list cutover to a database without losing rows

22 October 2026

Tags: Relational databasesMigrationDesktopCommand line

You have built the replacement app and it works against test data. What holds up go-live is the Loans list. It has been the record of every equipment loan since 2019, a Power Apps form on top of it still takes new loans each day, and it passed the 5,000-item view threshold a long time ago. The plan says the data moves over and the app switches on Monday. The part that keeps you up is proving, on Monday afternoon, that every loan made it across, including the one somebody edited at 8.55 that morning.

A one-off export does not give you that proof. You run it on Friday, people keep working, and by Monday the export is behind the list by a weekend of edits you cannot see. Running the script again either duplicates rows or means writing your own merge.

The order that holds up in practice is to replicate first and check the copy while SharePoint is still live. After that you freeze edits, run a final delta and a reconcile, switch the app to its own tables and leave the list in place as a read-only archive. Each step below says what you run and what you should see.

Replicate the list into the new database first

Start the replication days or weeks before cutover, while the old app is still in use. In the desktop app you connect the site, tick Loans and the Equipment list it looks up to, and choose Postgres or SQL Server as the destination. We prefer a config file for a migration, because the same file runs again in staging and again on the day, and the post on scripting a SharePoint migration explains why. From the command line it looks like this:

spsync export-config --output loans.yaml --pipeline-name loans \
  --prefix eq --destination-kind postgres \
  --connection-string-env LOANS_DB_URL --dataset-name sharepoint \
  --client-id $SPSYNC_CLIENT_ID \
  --site-url https://contoso.sharepoint.com/sites/facilities \
  --lists-by-name Loans,Equipment
spsync run loans.yaml

The first run is a full load and every run after it is a delta, which reads only the items that changed. On a schedule the copy stays a few minutes behind the list. The default interval is five minutes with delegated sign-in and one minute with app permissions, and you can set your own.

After the first run you have sharepoint.eq_loans and sharepoint.eq_equipment, one row per item, with sp_item_id as the primary key. That column is SharePoint’s own item id, the number you see as ID= in the item’s URL, and it is the thread that ties every check below back to the list. The Equipment lookup on Loans becomes equipment_id, which joins to eq_equipment.sp_item_id, with equipment_value beside it. The Borrower person column becomes borrower_user_id, which joins to the shared sp_users table. Every row also carries sp_modified, sp_etag and sp_synced_at.

Leave the replication running. A copy that has been current for a fortnight gives you two weeks of checks before the day that matters, and the migration use case covers the longer form of the plan if the move will take months. The SharePoint list to database migration planner sets out the same steps as a checklist you can work through list by list.

How do you verify a SharePoint migration row by row?

We run two checks every day of the parallel period, and both are cheap enough that there is no reason to skip one. The first is the count. In SharePoint, Site contents shows the item count for each list. In the database, count the live rows:

select count(*) from sharepoint.eq_loans where sp_deleted_at is null;

The two numbers should agree once the latest run has finished. If they differ by a handful during a busy morning, wait for the next run and count again before you start digging.

The second check is a sample, chosen by sp_item_id. Pick some rows at random and a few on purpose: the oldest loan, the newest, one with an empty Equipment lookup, one with a long comment, one edited today. Then put the database row next to the item in the browser:

select sp_item_id, title, status, equipment_id, equipment_value,
       borrower_user_id, sp_modified
from sharepoint.eq_loans
where sp_item_id in (17, 2403, 9120)
   or sp_item_id in (select sp_item_id from sharepoint.eq_loans
                     order by random() limit 20);

For each id, open .../Lists/Loans/DispForm.aspx?ID=2403 and read the fields across. Text, number, choice, yes/no and lookup key columns come across today. A few column types are still being verified on a live tenant, among them currency, hyperlink, lookup display values and multi-value lookup and person columns, so if Loans uses any of those, put them in the sample on purpose. Managed metadata columns are not replicated yet. If the list has one, plan to bring it across another way before you commit to a date.

Keep the sample query in the repository next to loans.yaml. You will run it again after the freeze, and it is quicker to trust a check you have already run ten times.

Freeze edits on the SharePoint list

On the morning of cutover, stop people changing the list. SPSync cannot do this for you, because it never writes to SharePoint, so the freeze is a SharePoint change you make yourself. The simplest version is on the list’s permissions page. Stop inheriting permissions from the site, then set members and visitors to Read, leaving owners as they are.

Then turn off anything else that writes. The Power Apps form is the obvious one. Flows are easy to forget, so check Power Automate for any flow that creates or updates Loans items and switch it off. Tell people the time of the freeze a few days ahead, and tell them the new app’s address in the same message.

Before you move on, confirm SPSync can still read. If the replication signs in as a person, that person needs at least Read on the list. Click Run now in the desktop app, or run spsync run loans.yaml, and check that the run completes.

Final delta and reconcile before cutover

With the list frozen, run the replication once more. This picks up every edit made between the last scheduled run and the freeze, including the 8.55 change. The command-line tool prints one line per list with the items read, inserted, updated and soft-deleted. Run it a second time a minute later and the inserted, updated and soft-deleted counts on Loans should all be zero, which tells you the list has stopped moving. Then run a reconcile with the command-line tool:

spsync reconcile loans.yaml

A reconcile lists every item id in SharePoint and compares it with the replica. Any row whose etag differs is fetched again, and any row whose item no longer exists is marked deleted. On a normal day it runs on its own schedule, weekly by default, as a QA check that both sides agree. The desktop app runs it on that schedule and has no button to start one, which is one more reason we drive a migration from the config file.

On cutover day you run it on demand, because a check that the two sides match is the evidence you want to put in front of whoever signs off the move. It is also how you catch up after an outage on your side or Microsoft’s, so if the network dropped during the freeze, this is the step that puts that right.

Now repeat the count and the sample query from the previous section. If both match, write the numbers down with the time. That note is your record that the database matched the list at the moment you switched.

Switching the app to its own tables

Pause the replication before the app takes its first write. In the desktop app that is the Pause control on the replication; from the command line, remove the scheduled job. The replicated tables now hold the list as it stood at the freeze, and they stop changing.

Do not point the new app at sharepoint.eq_loans and start writing to it. SPSync owns that table. An edit there never reaches SharePoint, and if anyone restarts the sync later, the next change to that item in the list writes over your row. Copy Equipment into the app’s schema first, so the foreign key has something to point at, then copy Loans into a table the app owns:

insert into app.loans (id, title, status, equipment_id, borrower_email,
                       created_at, updated_at)
select l.sp_item_id, l.title, l.status, l.equipment_id, u.email,
       l.sp_created, l.sp_modified
from sharepoint.eq_loans l
left join sharepoint.sp_users u on u.id = l.borrower_user_id
where l.sp_deleted_at is null;

select setval(pg_get_serial_sequence('app.loans', 'id'),
              (select max(id) from app.loans));

Keeping sp_item_id as the app’s id means a loan has the same number in the new app as it had in SharePoint, so a support call that quotes an old item id still finds the right row. The setval call moves the Postgres sequence past the highest old id, so new loans do not collide with migrated ones. On SQL Server, SET IDENTITY_INSERT does the same job for the insert. Person columns resolve through sp_users, which holds each person’s login, display name and email, so you can match borrowers to the app’s own user table by email.

The where clause leaves soft-deleted rows behind. They are still in sharepoint.eq_loans with sp_deleted_at set to when SPSync saw the delete, so you have the last values of every loan anybody removed during the migration. Some teams copy them into an archive table, and others leave the replica schema in place as the record. Either works, as long as you decide before go-live. Once the copy is done, run the app’s smoke tests against the new tables and only then switch the address over.

Keep SharePoint read-only as the archive

Leave the list where it is, frozen at Read. The replica holds the current field values, and SharePoint still holds two things the replica does not. Version history is not replicated, so the record of who changed a field and when, before the last edit, lives only in the list. Attachments are not replicated either. The replica records whether an item had attachments, and the files themselves stay on the item.

That makes the frozen list the place auditors and long-serving colleagues go when the question is about history. Nobody can change it, and the new app never needs to touch it. If you want the database side to keep its own audit record too, the archive and audit use case shows how soft deletes keep removed items on file.

Some time later, months for most teams, you can archive or delete the list under your normal retention rules. The replica schema can go at the same time, or stay as a read-only copy for reporting.

Can you roll back a SharePoint cutover?

Yes, for a while, and the window is shorter than people expect. Rolling back means setting the list’s permissions back, turning the Power Apps form and the flows on again and pointing people at the old app. SharePoint is exactly as you left it, because nothing in this process wrote to it.

What rolling back cannot do is carry the new app’s writes back to the list. SPSync has no write path to SharePoint today. Every loan created in the new app after the switch would have to be entered in SharePoint by hand or by a script you write. Staged write-back, where a person approves each change before it goes to the list, is designed and described on the two-way sync roadmap page, and it has not shipped. So set a rollback deadline, such as the end of the first working day, and after that fix forward.

If your plan needs a long period where both the old app and the new one write, and the list has to show both, SPSync is the wrong tool for that stretch. Layer2 Cloud Connector writes back to SharePoint today, and the roadmap page links to our comparison of the two.

When a one-off export is enough

Not every list needs this. If Loans were a few hundred items, edited by two people, you could freeze it for an afternoon, export it with PnP PowerShell or a CSV from the list view, import it and check it by eye. That is a perfectly good migration, and it costs less than setting up a replication.

The sequence above earns its keep when the list is large, edited all day, or depended on by an app that cannot be down for long. It also helps when you need to run the same move in staging first and show someone the evidence afterwards. In those cases the extra setup pays for itself in the first parallel week, when the daily count and sample show you problems while there is still time to fix them.

If you want to walk through your own lists and the order you would cut them over in, book a call.