What’ is the easiest way to extract SharePoint list or library data to a separate SQL Server table?

We reckon by far the easiest way to get list or library data (and it’s associated binary objects, if required) out of SharePoint On-Prem or SharePoint Online and into SQL Server is as follows:

1. Download SQList from here

2. Install SQList using these instructions (takes a couple of minutes)

3. Run SQList for the first time and install the licence

If you don’t have any replication defined, SQList Manager will prompt you so start the wizard to create one:

Just click “Yes” to get started. Subsequently, to create a replication click “New Replication” to start the wizard.

4. Enter the details of the SharePoint site you want to export

The first step in the wizard is to create a new site definition, or select an existing one if you have defined it previously. Once you have entered the details to connect to the SharePoint site, you can use the “Test Connection” to ensure they are correct and SQList can connect to the site (SQList connects to the SharePoint site’s web services via HTTP).

The site definition you create in this step will be used in the replication as the source SharePoint site to export, and it will also be saved for future uses.

A few points worth noting:

When you enter the Site URL, make sure you do not enter the URL to a specific list, or to a default page, but to the folder of the site you want to export;
If you choose “Trusted Authentication”, make sure you understand how SQList uses Trusted Authentication.

Once you entered the details, click “Next >” to go to the next step, to define the destination database connection.

5. Enter the details of the SQL Server database onto which to export the SharePoint lists

In this step you create a new database connection, or select an existing one if you have defined it previously. Once you have entered the details to connect to the SQL Server database, you can use the “Test Connection” to ensure they are correct and SQList can connect to the database (SQList connects to the SQL Server database using standard .NET connection via TCP).

The database connection you create will be used in the replication as the destination database of the export, and it will also be saved for future uses.

A few points worth noting:

If your SQL Server does not use the standard port, you can use the format localhostsqlexpress,1234 as “Server NameInstance”;
If you choose “Trusted Authentication”, make sure you understand how SQList uses Trusted Authentication.

Once you entered the details, click “Next >” to go to the next step, to select what to export.

6. Select what type of export you want to use

In this step in the wizard you decide what you want to export. SQList supports two main types of exports:

Export lists and libraries from this site only: this is the most common type and it is used to export lists and libraries that belong directly to the site you have defined in the site definition;

Export form this site and its sub-sites: in this export SQList lets you select lists and libraries across site you have defined in the site definition and its sub-sites; this more complex type of export is described in detail in our “How To” section.

For the purpose of this article, we will select the first option.

Click “Next >” to go to the next step and select the lists to export.

7. Select which lists and libraries you want to export

At this point you are presented with the list of all lists and libraries in your SharePoint site.

You have two ways to select which lists and libraries you want SQList to export; this is determined by the “By Default” selection:

Export only the lists and libraries selected below: using this option, SQList will only export the lists and libraries that you select in the table below. This is the mode to use if you only want to export certain lists onto your SQL database.
Export all lists and libraries, except those unchecked below: using this option, SQList will export all lists and libraries in your SharePoint site, with the exception of those you de-select in the table below. Be aware that in this mode, SQList will automatically export new lists added to the SharePoint site at a later stage. This is the mode to use if you want to export the entire content of your SharePoint site, with the exception of certain lists (e.g. you may want to exclude SharePoint system lists like “List Template Gallery” or “Form Templates“).

Once you select a default option, you can override it for individual lists by selecting:

Export List: select or deselect this checkbox to include or exclude the list from being exported;
Export binary content: use this option to export the binary content of document libraries and image libraries as a blob column in the SQL tables. You may want to export it if you plan to serve those documents via other technologies (e.g. a .NET website), or you may rather not export to save storage on the SQL Server database.
Export attachments: select this option if you want SQList to export attachments for the lists that have them enabled. Attachments are exported on a separate table in the database.

For our purpose, we select only “Big List“, “Categories“, “Customers“, and “Invoices” to be exported; for “Customers” (which in our case is a Picture library) also the binary data of the image will be exported:

Click “Next >” to complete the wizard.

And you’re done!
This is all you need to do to get your SharePoint lists exported as normalised tables into your SQL Server database.

Click “Finish” to save your changes and start the SQList Service.

When the SQList Service is started, SQList Manager switches to the “Service Status” tab, from where you can monitor the correct functioning of SQList. If there are errors,they will be displayed in the “Event Viewer” window.

You can also use the “Table Status” tab to send us an “Event Report” in case you are getting errors that you cannot resolve. When we receive an Event Report we get back to you as soon as possible.

And that is it. You can now take a look at your SQL database and see the tables being created and populated by SQList. Note that you do not need to keep SQList Manager open for the replication to continue as that is done by the SQList Service in the background.

In this example, this is what the SQL tables look like:

Note: the table “(SQList.Status)“: this table is reserved for SQList and should not be used by your queries or applications.

For more ways to export SharePoint lists and libraries to SQL Server, please visit our “How to” section.