Part of: Automation and the command line

Export a SharePoint list to SQL Server with PnP PowerShell

A PnP PowerShell script that copies a SharePoint Online list into a SQL Server table, with app-only sign-in, and the places it breaks once it runs every night.

Relational databasesAutomationDesktopCommand line

“I need the Maintenance Requests list in SQL Server so finance can join it to invoices. Every PowerShell example I find either uses Get-SPWeb, which is for on-premises servers, or stops at Export-Csv.” That is the usual starting point. The list lives in SharePoint Online, the report wants a table, and PnP PowerShell is already installed on someone’s laptop.

PnP PowerShell is a good tool for this. It is free and cross-platform, and one cmdlet pages through a list of any size. This post gives you a script that copies a list into SQL Server, with the sign-in setup that changed in 2024 and again in 2026. The second half covers where a nightly run of that script starts to cost you time.

Can PnP PowerShell export a SharePoint list to SQL Server?

Yes, with two modules and about a hundred lines of PowerShell. PnP PowerShell reads the list. Microsoft’s SqlServer module brings the SQL client library, and its SqlBulkCopy class loads the rows. The script here was written against these versions, which were current on 23 October 2026:

Pin the PnP version. The project ships often, and parameter behaviour does change between releases, so a scheduled job should only move to a new version after a test run.

We ran the SQL half of this script (table creation, bulk load, staging swap, lookup split and the 12-lookup guard) against SQL Server 2022 with simulated SharePoint list items. We have not run it end to end against a live SharePoint Online list, so test it on a copy of your list first.

What sign-in does PnP PowerShell need now?

Two changes catch people who last wrote one of these scripts a few years ago.

PnP’s shared multi-tenant app is gone. The PnP guide to registering an application says that using your own Entra ID app registration became mandatory on 9 September 2024. Every Connect-PnPOnline call now passes the client id of an app in your own tenant.

The old SharePoint app-only route has gone as well. Microsoft’s Azure ACS retirement notice says ACS stopped working for existing tenants and was fully retired on 2 April 2026. A client id and secret created through appregnew.aspx no longer signs in.

For a script that runs unattended, the setup is:

  1. Create the app registration and its certificate. Register-PnPEntraIDApp does both in one step, or you can create them in the Entra admin centre and upload the public key.
  2. Give the app the SharePoint application permission Sites.Selected, which on its own grants access to no site at all, and have an administrator consent to it.
  3. Grant the app read access to the one site it needs with Grant-PnPEntraIDAppSitePermission -AppId <client id> -DisplayName "List export" -Permissions Read -Site <site url>, run by an administrator who signs in interactively. The same steps for SPSync, with the list-level variant, are on Connect to SharePoint.

PnP’s authentication guide is clear that app-only access needs a certificate; a client secret is not supported for it. Note that the permission sits on the SharePoint API. The cmdlets in this script use SharePoint’s client object model, and when we pointed the script at a site our app could read only through a Microsoft Graph Sites.Selected grant, Connect-PnPOnline succeeded and the first read came back Unauthorized.

The script, step by step

Save this as Export-ListToSql.ps1. It takes the site, list and table as parameters and reads both secrets from environment variables, so nothing sensitive is written into the file or printed to the console.

Terminal window
#Requires -Version 7.4
#Requires -Modules @{ ModuleName = 'PnP.PowerShell'; RequiredVersion = '3.4.1' }
#Requires -Modules @{ ModuleName = 'SqlServer'; ModuleVersion = '22.4.5.1' }
<#
  Copies one SharePoint Online list into a SQL Server table.
  Full reload on every run: read every item, load a staging table, then swap
  it for the live table in one transaction. A failed run leaves the old table.


  Secrets come from environment variables, never from this file:
    PNP_CERT_PASSWORD  password for the app registration's .pfx file
    SQL_CONNECTION     SQL Server connection string
#>
param(
    [Parameter(Mandatory)] [string] $SiteUrl,
    [Parameter(Mandatory)] [string] $ListName,
    [Parameter(Mandatory)] [string] $ClientId,
    [Parameter(Mandatory)] [string] $Tenant,
    [Parameter(Mandatory)] [string] $CertificatePath,
    [Parameter(Mandatory)] [string] $TableName,
    [string] $Schema = 'dbo',
    [int] $PageSize = 2000
)
$ErrorActionPreference = 'Stop'
Import-Module SqlServer   # brings Microsoft.Data.SqlClient


# 1. Sign in as your own Entra ID app registration, app-only, with a certificate.
$certPassword = ConvertTo-SecureString $env:PNP_CERT_PASSWORD -AsPlainText -Force
Connect-PnPOnline -Url $SiteUrl -ClientId $ClientId -Tenant $Tenant `
    -CertificatePath $CertificatePath -CertificatePassword $certPassword


# 2. Read the column definitions and decide a SQL type for each one.
$sqlTypes = @{
    Counter = 'int'; Text = 'nvarchar(255)'; Note = 'nvarchar(max)'
    Choice = 'nvarchar(255)'; MultiChoice = 'nvarchar(max)'
    Number = 'float'; Currency = 'decimal(19,4)'; DateTime = 'datetime2'
    Boolean = 'bit'; URL = 'nvarchar(max)'
    Lookup = 'int'; User = 'int'; LookupMulti = 'nvarchar(max)'; UserMulti = 'nvarchar(max)'
}
$clrTypes = @{ int = [int]; float = [double]; 'decimal(19,4)' = [decimal]; datetime2 = [datetime]; bit = [bool] }
$keep = 'ID', 'Created', 'Modified', 'Author', 'Editor'


$visible = Get-PnPField -List $ListName | Where-Object {
    -not $_.Hidden -and (-not $_.ReadOnlyField -or $_.InternalName -in $keep)
}
$fields = @($visible | Where-Object { $sqlTypes.ContainsKey($_.TypeAsString) })
$skipped = @($visible | Where-Object { -not $sqlTypes.ContainsKey($_.TypeAsString) })
if ($skipped) { Write-Host "Skipping: $(($skipped | ForEach-Object { "$($_.InternalName) ($($_.TypeAsString))" }) -join ', ')" }
$lookupTypes = 'Lookup', 'LookupMulti', 'User', 'UserMulti'
$lookupCount = @($fields | Where-Object TypeAsString -in $lookupTypes).Count
if ($lookupCount -gt 12) {
    throw "$ListName has $lookupCount lookup and person columns; one query can project 12."
}


# One SQL column per field; a lookup or person column becomes an id and a display value.
function New-Column($name, $field, $part, $sqlType) {
    [pscustomobject]@{ Name = $name; Field = $field.InternalName; Kind = $field.TypeAsString; Part = $part; SqlType = $sqlType }
}
$columns = foreach ($f in $fields) {
    if ($f.TypeAsString -in 'Lookup', 'User') {
        New-Column "$($f.InternalName)_id" $f 'id' 'int'
        New-Column "$($f.InternalName)_value" $f 'label' 'nvarchar(255)'
    }
    else {
        New-Column $f.InternalName $f 'value' $sqlTypes[$f.TypeAsString]
    }
}


# 3. Read every item, a page at a time, into a DataTable shaped like the SQL table.
$data = [System.Data.DataTable]::new()
foreach ($c in $columns) {
    $clr = $clrTypes[$c.SqlType]; if (-not $clr) { $clr = [string] }
    [void]$data.Columns.Add($c.Name, $clr)
}


function Get-CellValue($value, $column) {
    if ($null -eq $value) { return [DBNull]::Value }
    switch ($column.Kind) {
        { $_ -in 'Lookup', 'User' } { if ($column.Part -eq 'id') { return $value.LookupId } else { return $value.LookupValue } }
        { $_ -in 'LookupMulti', 'UserMulti' } { return (@($value | ForEach-Object LookupValue) -join '; ') }
        'MultiChoice' { return (@($value) -join '; ') }
        'URL' { return $value.Url }
        default { return $value }
    }
}


$fieldNames = @($fields.InternalName)
Get-PnPListItem -List $ListName -PageSize $PageSize -Fields $fieldNames | ForEach-Object {
    $row = $data.NewRow()
    foreach ($c in $columns) { $row[$c.Name] = Get-CellValue $_.FieldValues[$c.Field] $c }
    $data.Rows.Add($row)
}
Write-Host "Read $($data.Rows.Count) items and $($columns.Count) columns from '$ListName'."


# 4. Load a staging table, then swap it in one transaction.
function Quote([string] $name) { '[' + $name.Replace(']', ']]') + ']' }
$target = "$(Quote $Schema).$(Quote $TableName)"
$stagingName = "$($TableName)__staging"
$staging = "$(Quote $Schema).$(Quote $stagingName)"
$columnSql = ($columns | ForEach-Object {
    $constraint = if ($_.Name -eq 'ID') { ' NOT NULL PRIMARY KEY' } else { ' NULL' }
    "$(Quote $_.Name) $($_.SqlType)$constraint"
}) -join ', '


$conn = [Microsoft.Data.SqlClient.SqlConnection]::new($env:SQL_CONNECTION)
$conn.Open()
try {
    $cmd = $conn.CreateCommand()
    $cmd.CommandText = "DROP TABLE IF EXISTS $staging; CREATE TABLE $staging ($columnSql);"
    [void]$cmd.ExecuteNonQuery()


    $bulk = [Microsoft.Data.SqlClient.SqlBulkCopy]::new($conn)
    $bulk.DestinationTableName = $staging
    $bulk.BulkCopyTimeout = 0
    foreach ($c in $columns) { [void]$bulk.ColumnMappings.Add($c.Name, $c.Name) }
    $bulk.WriteToServer($data)


    $tx = $conn.BeginTransaction()
    $cmd = $conn.CreateCommand(); $cmd.Transaction = $tx
    $cmd.CommandText = "DROP TABLE IF EXISTS $target; EXEC sp_rename @old, @new;"
    [void]$cmd.Parameters.AddWithValue('@old', "$Schema.$stagingName")
    [void]$cmd.Parameters.AddWithValue('@new', $TableName)
    [void]$cmd.ExecuteNonQuery()
    $tx.Commit()
    Write-Host "Replaced $target with $($data.Rows.Count) rows."
}
finally {
    $conn.Dispose()
    Disconnect-PnPOnline
}

What each part does:

  1. Connect-PnPOnline signs in as the app registration with the certificate file. The certificate password comes from PNP_CERT_PASSWORD. On Windows you can import the certificate into the user’s certificate store and pass -Thumbprint instead, which removes the password altogether.
  2. Get-PnPField returns the list’s columns. The script keeps visible, editable columns plus the ID, Created, Modified, Author and Editor columns, and maps each SharePoint type to a SQL type. Column names are SharePoint internal names, which stay the same when someone renames a column in the browser. Any type the script does not map, such as managed metadata, is named on screen and left out.
  3. Get-PnPListItem -PageSize 2000 pages through the list by item ID, which is why it works on lists well past the 5,000-item view threshold. A lookup or person column becomes two SQL columns, the item id and the display value. Multi-value columns are joined into one string separated by semicolons.
  4. The rows go into a staging table with SqlBulkCopy. Only when the load succeeds does one transaction drop the old table and rename the staging table into its place, so readers never see a half-loaded table.

Running the export on a schedule

Set the two environment variables from your secret store at run time, then call the script:

Terminal window
$env:PNP_CERT_PASSWORD = (Get-Secret -Name ListExportCertPassword -AsPlainText)
$env:SQL_CONNECTION    = (Get-Secret -Name ListExportSqlConnection -AsPlainText)


./Export-ListToSql.ps1 -SiteUrl https://contoso.sharepoint.com/sites/estates `
    -ListName 'Maintenance Requests' -ClientId <client id> `
    -Tenant contoso.onmicrosoft.com -CertificatePath ./list-export.pfx `
    -TableName maintenance_requests

Get-Secret comes from Microsoft’s SecretManagement module; Azure Key Vault or a pipeline’s secret variables work the same way. Windows authentication to SQL Server, or Microsoft Entra authentication to Azure SQL, keeps the password out of the connection string entirely. Run it from Task Scheduler, cron or a CI job, and send the script’s exit code to whatever already watches your scheduled jobs.

Where the script is the right tool

If you need a one-off export before a migration, or a small list that someone checks every Monday, stop here. You have a working copy, and the code is yours to read and change. PnP PowerShell is also the better choice when the job includes logic specific to your tenant, such as tidying values as they move or filling in a column from another system in the same run.

The trouble appears later, when the export has run every night for a year and a report depends on it. The rest of this post covers the four places it usually breaks.

Throttling on a nightly full read

The script reads every item on every run. For a 30,000-item list at 2,000 items a page, that is 15 requests, which on its own is light. The cost depends on which API you use. Microsoft’s throttling guidance prices a Microsoft Graph multi-item query at 2 resource units, and a Graph delta request with a token at 1 resource unit. Get-PnPListItem uses SharePoint’s client object model (CSOM), and the same page says CSOM and REST have no predetermined resource unit cost, usually consume more than Graph for the same work, and are subject to other internal limits as well. A tenant with 1,001 to 5,000 licences gives each app 2,500 resource units a minute.

PnP does handle the basics. Its CSOM calls go through PnP Framework’s ExecuteQueryRetry, which waits for the time in the Retry-After header on a 429 or 503 response and tries again, up to 10 times by default. The trouble is volume. Several scripts that share one app registration and all read in full at 2am draw on the same per-app budget. When the retries run out, the run stops and the table stays as it was the night before, which the staging swap makes safe but not current.

What happens when the list passes 5,000 items?

The full read keeps working, because paging by ID avoids the list view threshold, set at 5,000 items by default. Filtering is where large lists bite. The usual way to make a nightly read cheaper is a CAML query with a Where on Modified, so the script only fetches what changed. On a list past the threshold that query needs Modified to be indexed, or SharePoint refuses it.

Even with the index in place, a Modified filter cannot see deletes, because a deleted item has no row to come back. The full reload in this script handles deletes by accident: the item is missing from the new read, so it is missing from the new table. Nothing records that it existed, when it went, or who removed it. Microsoft Graph’s list item delta query does report deleted items, with a deleted facet, but that means rewriting the read side around Graph and storing a delta link between runs.

Why do lookup columns break a PowerShell export?

PnP’s Get-PnPListItem reference describes a limit most people meet without warning. When you read all items, SharePoint returns at most 12 lookup columns and silently omits the rest, and a query that names more than 12 is refused. Person, group, managed metadata, Created By and Modified By columns all count. Microsoft documents the list view lookup threshold, 12 join operations per query, on its SharePoint Server 2019 boundaries and limits page; PnP’s reference describes the same limit in SharePoint Online. The script above stops with an error instead of losing columns. The fix is to read the lookups in groups of 12 and merge the groups by item ID, one more full read per group. Our post on the SharePoint lookup column threshold covers the limit in more depth.

The shape is the second problem. Joining Assigned To on a display name breaks when someone changes their name, so the script keeps the user’s item id next to it. That id belongs to the site’s user list, which you would also need to export to resolve it to an email address. Multi-value columns arrive as a string such as Plumbing; Electrical, and counting jobs per trade then means splitting strings in SQL. A child table with one row per value is the relational answer, and it is more code again.

Deletes and schema drift

The swap rebuilds the table from SharePoint’s current columns every night, so a new column appears without any work. Everything else about a changed column reaches the reports first:

Each of these is fixable, for example with an ALTER TABLE step that adds new columns and refuses retypes, or a soft-delete flag fed from a delta query. Added together they are most of a replication engine, and the comparison of PnP PowerShell scripts and SPSync lists what that engine has to own.

When to replace the script with replication

Keep the script while the list is small, the export runs by hand or weekly, and the person who wrote it is still around. Move to replication when a report or an application depends on the table being current and somebody would notice a missed night.

SPSync reads a list in full once, then reads only changes through Graph delta queries, with a full reconcile every seven days by default. Lookups arrive as key columns, multi-value columns as child tables, and deleted items stay in the table with sp_deleted_at set. A new column appears on the next run, a retyped column moves into a new _v2 column, and a removed column stays. It is one way: SharePoint stays the system of record and SPSync never writes to it. Our post on what it costs to keep a SharePoint export script running covers that trade-off for scripts and flows alike, and the SQL Server destination page shows the tables and reporting views you get.

If you liked scripting it, the command-line tool keeps the habit: one YAML file, secrets as environment variable references, run from the same scheduler that runs your PowerShell today. SQL Server is one of many destinations, and the same lists can go to several of them, one replication each. Tell us if the database you need is not on the list yet.

Compared with

Book a call

Book a call to talk through your use case, see further walkthroughs or demos, and set up a trial in your own environment.