How to backup specific ListItems to database?

Question

We are running a ticketsystem on Sharepoint 2010 which got alarmingly large over the last few months. The ListView Threshhold currently sits at 15k for this WebApplication.

I am currently looking for a suitable way to remove old items from that list and write them into a table on our SQL server.

So my plan would be something along those lines:

• eventhandler gets fired daily/weekly
• check list for items older then 3 months
• check if items have status 'implemented'
• put items into datatable
• delete items from List 'Tickets'
• load items into SQL server table.

Is there any flaw in my bulletlist above? I am looking for resources on any of the above steps or even better ways to implement this.

Kind regards

Answer 1

I would use SQL Server Integration Services to accomplish this, using the SharePoint list adapters from here:

SharePoint SSIS List Adapters - codeplex

SSIS Connections - Microsoft

I have used these and they work well. You would connect to the list as the input data source, and use the SSIS tools to loop through and check each list item and then act on the information from the list item (i.e. skipping it, or writing to a database table and deleting the list item).

You can schedule SSIS packages to run periodically in a variety of ways, using SQL Server Agent, or using the SSIS command line (dtexec) with the regular Windows scheduler.

You also could write code against the the object model that loops through all the items in the list, checks them, and outputs as required to a database, and you also could use Powershell scripting with the SharePoint and SQL Server snap-ins to accomplish this. You would then schedule the command-line program or powershell script to run periodically.