These list migrations have been tested on MOSS2007 to SQL 2005 and 2008 R2.

How to migrate non-Lookup items:

The lists can be migrated as templates, as explained here:

1. Create the tables in SQL. A simple script to do so:

use MYDB
CREATE TABLE myTable
(
ID int PRIMARY KEY,
name varchar(50),
someData varchar(50),

2. Once the templates are converted and installed, and the tables are set up, a client object program has to be used to push them into SQL.  Unfortuantely, SharePoint’s client OM does not work in MOSS, so you’ll have to transfer the templates first. For my program I chose a console application. Here’s a simplified script for a non-specific list:

using System.Collections.Generic;
using Microsoft.SharePoint;
using System.Data.SqlClient;
using Microsoft.SharePoint.Client;
using SP = Microsoft.SharePoint.Client;
using System.Data.Sql;
//Note that I cut out the first half of the references since they're all standard system assemblies
namespace ListToSQL
class Program
 {
string connString = "Data Source=ucla2010db;Initial Catalog=REACTOR;Integrated Security=SSPI";

	static void Main(string[] args)
 	{
		Program prog = new Program();
 		prog.GetListItems();
 	}  

         private void GetListItems()
         {

	  string SPSite = "http://MySite/siteName";

	  using (SP.ClientContext context = new SP.ClientContext(SPSite))
 	  {
 			SP.Web web = context.Web;
 			SP.List myList = web.Lists.GetByTitle("MyList");
 			SP.CamlQuery qry = new SP.CamlQuery();

			//No need to actually filter the query. We want all the items
 			qry.ViewXml = @"";

 			SP.ListItemCollection listColl = myList.GetItems(qry);
 			context.Load(listColl,
				items => items.Include(
 				item => item["Title"],
 				item => item["Date"],
 				item => item["OtherField"]));
 				context.ExecuteQuery();

 			BulkUpdateSQL(BuildTable(listColl),"myDataTableName");
            }
        }

       private DataTable BuildTable(SP.ListItemCollection contextList)
      {
           DataTable dt = new DataTable();

           //Select any ListItem at all and loop through the field names
           ListItem colItem = contextList[0];

           foreach (string name in colItem.FieldValues.Keys)
           {
	       //Make the field names into column names
                 dt.Columns.Add(name);
           }

           foreach (ListItem item in contextList)
           {
                 //Create a new row for each ListItem
                 dt.Rows.Add(dt.NewRow());

                 foreach (string name in item.FieldValues.Keys)
                 {
	            //Add each FieldValue item to the row
                      dt.Rows[dt.Rows.Count - 1][name] = item[name];
                 }
           }

         return dt;
       }

       private void BulkUpdatePI(DataTable dt, string destName)
       {
             using (SqlConnection conn = new SqlConnection(connString))
             {
                 conn.Open();
                 using (SqlBulkCopy copy = new SqlBulkCopy(conn))
                 {
 /* Note that you can use ColumnMappings [i.e. "copy.ColumnMappings.Add("Title", "name")"]
  * With either ordinal or string-based mapping,
  * But every column you don't map will get ignored if you explicitly map
  */
                        copy.DestinationTableName = destName;
                        copy.WriteToServer(dt);
                 }
          }
}

TADA! Lists in SQL!

But what about… LOOKUPLISTS!?

The only way to do this from MOSS2007 and maintain the lookup info is to export the list to Excel:

1. In the Data View list view there is an option to export to excel.
2. Save the file in a location accessible by your DB
3. From there, go to the DB you want the data in
4. Right click the management folder and select “Import Data”
5. In the Import Data Wizard, select “Microsoft Excel” from the dropdown

• This will prompt you to enter the file name for the excel file. Do that.

• Destination:

Whatever type of DB your MOSS server is, if you are  unsure, use SQL Native Client.

• Server Name: The name of your MOSS server. If you are unsure, try “MOSS.”
• Authentication: Most likely windows
• Database: The database you wanto to import the excel file into.
• And… Next

Follow

Related posts: