Does SPSync keep SharePoint version history?

No. SPSync keeps one row per item, holding the item as it was when SPSync last read it. It does not copy the item's earlier versions. Several edits between two runs arrive as one change. If you need every version, keep versioning on in SharePoint and keep the item there.

What happens to the SQL row when the flow deletes the item?

With the default soft delete, the row stays in the table and the next run sets sp_deleted_at to the time the deletion was seen. Every other column keeps its last value. The command-line tool can be set to hard delete instead, which removes the row, so leave that setting at its default for an archive.

Is a backup of a SharePoint list to SQL automatic once it is set up?

Yes. After the first full load, each run reads only what changed through Graph delta, on the schedule you set, and a reconcile, weekly by default, checks the whole list. You do not export anything by hand.

Archive old SharePoint list items and keep the history

Q: What is the best way to archive SharePoint list items?

It depends on what the archive is for. To keep records for a set period and dispose of them, a Purview retention label does that. To keep old items queryable for reports while the live list stays small, copy the list into a database and then delete old items from SharePoint.

Q: Can I put an archived item back into the list?

From the SharePoint recycle bin, yes, while it is still there. SPSync clears sp_deleted_at when a restored item appears again on a later read. That restore path has been tested against our Graph simulator only, and has not been run on a live tenant. SPSync never writes to SharePoint, so recreating an item from the database is a manual step.

23 October 2026

Tags: Relational databases Migration Desktop Command line

“Our Requests list has three years of closed tickets in it. Views time out, the flow that reads it fails, and nobody looks at anything older than six months. How do I archive the completed items without losing them?”

That question appears on community forums every few weeks, and the accepted answer is nearly always the same: create a second list called Requests Archive and write a Power Automate flow that moves completed items into it. It works for a while. Then the archive list grows past the point the original did, and it is now the list that times out when anyone filters or reports on it.

This post covers the routes Microsoft offers for archiving list items, what each one is good for, and a different order of work: copy every item into a database first, then trim the live list with a retention flow, so the history stays queryable and SharePoint only holds what people still work on.

What is the best way to archive SharePoint list items?

Start with what the archive is for, because the three common goals pull in different directions.

If the goal is records management, keeping items for a set number of years and then disposing of them on a schedule, Microsoft Purview retention labels are the tool. That is what they are built for, and an auditor will recognise them.

If the goal is a faster list, the items need to leave the list, and the question becomes where they go and whether anyone can still find them.

If the goal is reporting history, last year’s tickets need to stay queryable alongside this year’s, with their lookups and people intact, by tools such as Power BI.

Most teams asking how to archive completed items want the second and third together: a small live list, and old items still counted in the dashboard. The archive-list answer handles the first half and breaks the second.

Moving completed items to an archive list with Power Automate

The usual flow runs on a schedule, finds items with a closed status that are older than a cut-off, creates a copy of each in the archive list and deletes the original. The SharePoint connector reference has Move file and Move folder actions for libraries, but no move action for list items, so the flow is always Get items, then Create item in the archive list, then Delete item.

That has some side effects worth knowing before you build it:

The archived item is a new item with a new ID. Any lookup column in another list that pointed at the original item no longer finds it, and a report that joins on the ID breaks for every archived row.
Created and Created By on the copy describe the flow’s run unless the flow sets them some other way, so the archive no longer records who raised the request or when.
The original item’s version history goes to the recycle bin with the deleted original. The copy starts again at its first version.
Reports now need two sources, the live list and the archive list, combined in Power BI or in the flow that feeds them.
Get items on a large list needs a filter on an indexed column, or the flow fails on the threshold before it archives anything. The Get items limits post covers paging and filtering that list.

None of this makes the flow wrong. For a list of a few thousand items that rarely needs history, an archive list is a reasonable answer.

Why does the archive list hit the same threshold?

Because it is the same kind of list. A SharePoint list can hold up to 30 million items, so the archive list will not run out of room. Its views run into the list view threshold, which Microsoft Learn sets at 5,000 items by default for any one query.

The archive list collects every closed item from every year, so it grows faster than the live list ever did. Within a year or two it passes 5,000 items, and every view and report against it meets the threshold message the archive was meant to get rid of. Teams then create Requests Archive 2024 and Requests Archive 2025, and a report over three years of history needs three sources. The 5,000 item limit page covers what fails past that point.

Purview retention labels and Microsoft 365 Archive

Microsoft has two archive features, and both are worth a fair look.

Purview retention applies to list items through labels only. Microsoft Learn’s page on retention for SharePoint says list items “aren’t supported by retention policies but are supported by retention labels”. A label can keep an item for a period, delete it at the end, or both. When someone edits or deletes a labelled item, SharePoint keeps a copy in a hidden Preservation Hold library, and you reach those copies with compliance tools such as eDiscovery. That is the right tool for proving an item was kept for seven years. It does not give anyone a table to report on, and the retained copies are not something you browse or join.

Microsoft 365 Archive moves inactive sites, and files within sites, into a colder storage tier. The Microsoft 365 Archive overview says that when a site is archived, all its content goes with it, lists and list data included, and the site cannot be opened until it is reactivated. It works at the level of a whole site or a file, so it cannot archive last year’s items out of a list that is still in use.

The recycle bin is not an archive either. Deleted items stay there for 93 days across the first and second stage, per the same retention page, and then they are gone.

How do you back up a SharePoint list to SQL automatically?

With a replication tool that reads the list on a schedule and writes it into a database table. SPSync does that for SharePoint Online lists and library metadata, into SQL Server, Postgres and other databases. It starts with a full load of the list, and each later run reads only what changed through Microsoft Graph’s delta query for list items, which reports new, changed and deleted items, every few minutes on the default schedule. A reconcile, weekly by default, checks the full list against the copy.

Each list becomes one table keyed on the SharePoint item ID, so the ID never changes and nothing is renumbered. Lookups become id columns that join to the target list’s table, person columns join to one shared sp_users table, and multi-value columns get a child table each. The schema reference lists every column.

Done this way, the copy is a database table with no view threshold, and a report can filter it by date and status across every year at once.

What SPSync keeps when an item is deleted

This is the part the whole approach depends on, so here it is plainly. When an item is deleted in SharePoint, SPSync keeps the row and marks it deleted. The next run sees the deletion in the delta feed and sets sp_deleted_at to the time it saw it. Every other column keeps the value it had when SPSync last read the item. Soft delete is the default in the desktop app, the command-line tool and SPSync in your Azure.

There is one exception. The command-line tool’s YAML config has a per-replication deletes: hard setting, which removes the row instead, for a database that should hold only what is in SharePoint now. For an archive, leave it at the default. Desktop and Azure runs always soft delete.

If someone restores the item from the recycle bin, the item appears again on a later read and SPSync clears sp_deleted_at, so the row is live again. That restore path has been tested against our Graph simulator only, and has not been run on a live tenant.

What SPSync does not keep matters just as much:

It does not copy SharePoint version history. Each item is one row in its latest state. Two edits between runs arrive as one change, and an item created and deleted between two runs may never appear in the table at all.
It copies metadata, not file contents. List attachments are not copied.
It does not carry SharePoint item permissions into the database, so access to the archive is whatever you grant on the database.
It only reads SharePoint. SharePoint stays the system of record, and SPSync never deletes or restores an item.

For old, closed items that are no longer edited, the latest state is the state worth keeping. If your records policy needs every version, that is a Purview and versioning job, and the items should stay in SharePoint until the policy says otherwise.

Trimming the live list once the copy has it

The order is what makes this safe. The copy has to hold an item before anything deletes it.

Replicate the list. In the desktop app, connect the site, tick the list and every list its lookups point at, and pick SQL Server or Postgres as the destination. From the command line, spsync export-config writes the same replication; check that it does not set deletes: hard.
Let the first full load finish, then compare the row count in the table with the item count in the list. Leave a reconcile to run before you delete anything.
Add an indexed column the flow can filter on, if the list does not have one. Status and Modified are the usual pair. Without an index, Get items on a big list fails on the threshold.
Optionally, have the flow set a choice column such as Archive state to “Trimmed” before it deletes. The next run copies that edit, so the table can tell an item your flow removed from one a person deleted by hand. Give the sync a run to pick it up before the delete step.
Build a scheduled flow with Get items, filtered to closed items whose Modified date is well past your cut-off, then Delete item for each one. A cut-off measured in months leaves many sync runs, and at least one reconcile, between the last edit and the delete.
If you want a hard check, the flow can query the table before each delete and skip any item whose row is missing or older than the item. The SQL Server connector is a premium connector in Power Automate, so weigh that against the cut-off alone.
After the next SPSync run, the trimmed items have sp_deleted_at set, and the live list holds only open and recent work.

Keep the flow small. It deletes, and nothing else. If the flow fails halfway through, the items it did not reach are still in the list and still in the table, and the next run of the flow picks them up.

Querying archived and live items together

Archived and live items sit in the same table. A trimmed item is a row with sp_deleted_at set, and a live item is a row without it, so a report over three years of requests is one query with no union:

SELECT r.sp_item_id,
       r.title,
       r.status,
       r.category_value,
       r.sp_created,
       r.sp_deleted_at
FROM ops_requests AS r
WHERE r.sp_created >= '2023-01-01';

Add WHERE r.sp_deleted_at IS NULL for only what is in the list today, or IS NOT NULL for only what has been trimmed. The table and column names here are an example; yours follow the list’s own name and column names.

One thing to watch: SPSync’s optional reporting views exclude deleted rows on purpose, so they show what is live in SharePoint now. For history, point Power BI or your SQL at the tables, or build your own view over them that keeps the deleted rows.

Where this fits

The archive and audit use case covers keeping deleted items on file for audit, and the continuous backup use case covers recovering items someone deleted by mistake. The SQL Server and PostgreSQL pages cover each destination, and if you are moving off the list for good rather than trimming it, the cutover guide walks through that instead.

Archive old SharePoint list items without losing the history