In this article we will see how to export SharePoint lists from a site and its sub-sites to a SQL Server database with just a few clicks.
This is an advanced type of export; if you are not familiar with SQList we recommend that you
read our getting started section first.
For clarity, let's review some of the terminology we will be using in this article:
- A site definition contains the details necessary to connect to a single SharePoint site (URL, credentials, etc.). When you create a site definition, you will also give it a name, which has to be unique.
- A database connection contains the details necessary to connect to a SQL Server database (server name, database name, credentials, etc)
- A replication defines which lists, libraries, and sub-sites (where applicable) are exported from a one SharePoint site (defined by a site definition) to a SQL Server database (defined by a database connection).
We will be using the same site definition and database connection we use used in the article describing how to
export lists from a site.
Choose export type
After selecting the site definition and the database connection, SQList manager presents us with the choice of exporting only lists belonging to the SharePoint site, or also the lists belonging to its sub-sites.
We choose the second option: "
Export from this site and its sub-sites".
Note the checkboxes next to the option; these allow you to select which levels you want SQList to export. You can export up to 5 levels of sub-sites; however, if you need to go deeper that that, please contact us.
By default all five levels are selected, but you may need to export only sub-sites at a certain level.
An example where you would need only a specific level is if you have your company's site at the root, the departments sites as level 1, and at level 2 a site for each department's project, and you need to export data from all projects across the company. In that case you would only choose level 2.For this example, we choose all sub-sites.
Choose the sub-sites to export
At this point you are presented with the list of all sub-sites that belong to your SharePoint site (the root site is also included).
You have two ways to select which sub-sites you want SQList to export; this is determined by the "By Default" selection:
- Export only the sub-stes selected below: using this option, SQList will only export the sub-sites that you select in the table below. This is the mode to use if you only want to export certain sub-sites onto your SQL database.
- Export all sub-sites, except those excluded below: using this option, SQList will export all sub-sites 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 sub-sites added to the SharePoint site at a later stage.
Once you select a default option, you can override it for individual sub-sites by selecting:
- Export sub-site: select or deselect this checkbox to include or exclude the sub-site from being exported;
For our example, we choose to export all sub-sites.
Select which lists and libraries you want to export
The list selection at this stage works exactly like the list selection for a single site, but there is a major difference behind the scene:
- the list presented, is not the list of individual SharePoint lists belonging to all sub-sited, but the distinct list of the names of all lists across all sub-sites (apologies for excessive use for the word "list"...).
This means that with this type of export, you cannot select an individual lists but only all lists by specific names.
For example, for the "
Products" list, SQList will export all lists named "
Products" across all sub-sites.
One additional option you have with this mode, is to "
Generate a unified view of all lists with the same name". If you select this option, SQList will automatically create a view, in the destination SQL database, that select all items from all lists with the same name, across all sub-sites.
For example, taking the "Products" list mentioned above, SQList will create a view that select all items from all "Products" lists across all sub-sites.
That is it, click "Next >" to export your data.
Get all your SharePoint data exported to SQL Server
After saving your changes and starting the SQList service, the lists you selected will be exported as normalised tables in the destination database.
In our example, this is what they look like:
Note the highlighted "
Products" lists; these are the individual lists, one from each sub-site that contains a list named "
Products". Each list contains the items from its correspondent list:
As we selected to generate the union view, SQList has also generated a view that selects all rows from all the tables above:
Note: the union view, contains only the columns that are in common with all the lists included in it.