# encoding: ascii
# api: powershell
# title: Export-SPListToSQL
# description: Exports a SharePoint list to a SQL Server table using OLEDB
# version: 12.0
# type: function
# author: Chad Miller
# license: CC0
# function: Get-SPList
# x-poshcode-id: 1002
# x-archived: 2015-05-19T05:51:54
# x-published: 2009-04-07T07:52:00
#
#
#Change these settings as needed
#MS Access 2007 Data Components required
$connString = 'Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes; DATABASE=http://sharepoint.acme.com/;LIST={96801432-2d03-42b8-82b0-ac96ca9fea8a};'
#See http://chadwickmiller.spaces.live.com/blog/cns!EA42395138308430!275.entry for instructions on obtaining list GUID
$qry = 'Select Title,Notes,CreateDate, Modified, Created from list'
#Create a table in destination database with the with referenced columns and table name.

$sqlserver = 'Z002\SQL2K8'
$dbname = 'SQLPSX'
$tblname = 'splist_fill'

#######################
function Get-SPList
{

    param($connString, $qry='select * from list')

    $spConn = new-object System.Data.OleDb.OleDbConnection($connString)
    $spConn.open()
    $cmd = new-object System.Data.OleDb.OleDbCommand($qry,$spConn) 
    $da = new-object System.Data.OleDb.OleDbDataAdapter($cmd) 
    $dt = new-object System.Data.dataTable 
    $da.fill($dt) > $null
    $dt

} #Get-SPList

#######################
function Write-DataTableToDatabase
{ 
    param($destServer,$destDb,$destTbl)
    process
    {
        $connectionString = "Data Source=$destServer;Integrated Security=true;Initial Catalog=$destdb;"
        $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
        $bulkCopy.DestinationTableName = "$destTbl"
        $bulkCopy.WriteToServer($_)
    }

}# Write-DataTableToDatabase

#######################

Get-SPList $connString $qry | Write-DataTableToDatabase $sqlserver $dbname $tblname
