Export and Import SharePoint List with content using PowerShell

We had SharePoint farms in many variation SharePoint 2007, SharePoint 2010 and SharePoint 2013. Recently I had worked on a assignment that migrating single list data from SharePoint 2007 to SharePoint 2010.  Initially I thought that it bit easy task but when get in to it, it had given many issue because I was told that there should not be any change in the data including modified date, modified by, created data, created by finally all versions as it is.

I was in trouble because the source list is 100% customized(custom fields, custom content types, list definition, event receivers and New, Edit & display forms as well)

I had upgraded the custom functionality to SharePoint 2010 excluding custom input forms. But export import command, failed all the time.

Ends with lot off issue like, fields are duplicated, content type is not matching, field ids are not matching, destination web, list are are not available and so many.

Thought of implementing some data correcting before importing the SharePoint 2007 list content.

1. I had trimmed the custom source code only with Custom fields and Custom Content Types
2. Deployed the latest build on SharePoint 2010 farm
3. Created a new list and added custom content type, Enabled versioning and removed default content type "Item".
4. Created a test item using new custom content type
5. Exported the SharePoint 2010 list as.DAT file
6. Renamed .DAT to .CAB and extracted all files in to new folder
7. Exported SharePoint 2007 list as .DAT file
8. Renamed .DAT to .CAB and Extracted all files in to new folder
9. Opened the manifest.xml file from SharePoint 2007 extracted folder and copied SPListItem elements
10. Opened the manifest.xml file from SharePoint 2010 extracted folder and pasted Copied SPListItem elements.
11. Replaced the below ids on newly pasted element
      ParentId, ParentWebId, FileUrl,URL,ContentTypeId,
12.Created .CAB files form extracted SharePoint 2010 files.(used makecab.exe)
13. Imported the cab to SharePoint 2010
14. Verified the list
15. All worked fine.

Blow are the PowerShell scripts I used for migration.

Export SharePoint List

# For Export a specified SharePoint List
Export-List "http://kmsnet:15006/Lists/sklist/"

function Export-List([string]$ListURL)
{
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint") > $null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Deployment") > $null

$versions = [Microsoft.SharePoint.Deployment.SPIncludeVersions]::All

$exportObject = New-Object Microsoft.SharePoint.Deployment.SPExportObject
$exportObject.Type = [Microsoft.SharePoint.Deployment.SPDeploymentObjectType]::List
$exportObject.IncludeDescendants = [Microsoft.SharePoint.Deployment.SPIncludeDescendants]::All

$settings = New-Object Microsoft.SharePoint.Deployment.SPExportSettings

$settings.ExportMethod = [Microsoft.SharePoint.Deployment.SPExportMethodType]::ExportAll
$settings.IncludeVersions = $versions
$settings.IncludeSecurity = [Microsoft.SharePoint.Deployment.SPIncludeSecurity]::All
$settings.OverwriteExistingDataFile = 1
$settings.ExcludeDependencies = $true

$site = new-object Microsoft.SharePoint.SPSite($ListURL)
Write-Host "ListURL", $ListURL

$web = $site.OpenWeb()
$list = $web.GetList($ListURL)

$settings.SiteUrl = $web.Url
$exportObject.Id = $list.ID
$settings.FileLocation = "C:\Temp\BackupRestoreTemp\"
$settings.BaseFileName = "ExportList-"+ $list.ID.ToString() +".DAT"
$settings.FileCompression = 1

Write-Host "FileLocation", $settings.FileLocation

$settings.ExportObjects.Add($exportObject)

$export = New-Object Microsoft.SharePoint.Deployment.SPExport($settings)
$export.Run()

$web.Dispose()
$site.Dispose()
}

Import SharePoint List

# For Import the list you export in previous command
Import-List "http://kmsnet:15006" "C:\SK_DEV\sklist.cab" "C:\SK_DEV\OUT\ImportLog.txt"

function Import-List([string]$DestWebURL, [string]$FileName, [string]$LogFilePath)
{
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint") > $null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Deployment") > $null

$settings = New-Object Microsoft.SharePoint.Deployment.SPImportSettings

$settings.IncludeSecurity = [Microsoft.SharePoint.Deployment.SPIncludeSecurity]::All
$settings.UpdateVersions = [Microsoft.SharePoint.Deployment.SPUpdateVersions]::Overwrite
$settings.UserInfoDateTime = [Microsoft.SharePoint.Deployment.SPImportUserInfoDateTimeOption]::ImportAll

$site = new-object Microsoft.SharePoint.SPSite($DestWebURL)
Write-Host "DestWebURL", $DestWebURL

$web = $site.OpenWeb()

Write-Host "SPWeb", $web.Url

$settings.SiteUrl = $web.Url
$settings.WebUrl = $web.Url
$settings.FileLocation = "C:\SK_DEV\OUT\"
$settings.BaseFileName = $FileName
$settings.LogFilePath = $LogFilePath
$settings.FileCompression = 1

Write-Host "FileLocation", $settings.FileLocation

$import = New-Object Microsoft.SharePoint.Deployment.SPImport($settings)
$import.Run()

$web.Dispose()
$site.Dispose()
}

Get all sites where a feature is activated or deactivated

Get list of webs where either feature activated or deactivated in SharePoint at different scopes like web, site collection, web application and farm.

Below PowerShell script will provide list of webs(sub site including top level site) where specified feature is installed and not activated in particular site collection

Get-SPSite "http://kmsnet:5050"| Get-SPWeb -Limit All | Where-Object { (Get-SPFeature "12f73b57-1db8-4272-3d45-d8c1cc9f3d41" -ErrorAction SilentlyContinue -Web $_.Url) -eq $null} | Select Url


Below script will provide list of webs(sub sites including toplevel site) where specified feature is installed and activated.

Get-SPSite "http://kmsnet:5050"| Get-SPWeb -Limit All | Where-Object { (Get-SPFeature "12f73b57-1db8-4272-3d45-d8c1cc9f3d41" -ErrorAction SilentlyContinue -Web $_.Url) -ne $null} | Select Url

Note: If the feature object is null then those web(s) considered that feature is installed and not activated. If it is not-null then feature is installed and activated on the web.

Below PowerShell script will provide list of webs(sub sites including top level site) where specified feature is installed and not activated in all site collections from current SharePoint Farm

Get-SPSite –Limit All | Get-SPWeb -Limit All | Where-Object { (Get-SPFeature "12f73b57-1db8-4272-3d45-d8c1cc9f3d41" -ErrorAction SilentlyContinue -Web $_.Url) -eq $null} | Select Url


Below script will provide list of webs(sub sites including toplevel site) where specified feature is installed and activated in all site collections from current SharePoint Farm.

Get-SPSite –Limit All | Get-SPWeb -Limit All | Where-Object { (Get-SPFeature "12f73b57-1db8-4272-3d45-d8c1cc9f3d41" -ErrorAction SilentlyContinue -Web $_.Url) -ne $null} | Select Url

Below PowerShell script will provide list of webs(sub sites including top level site) where specified feature is installed and not activated in all site collections from specified web application

Get-SPWebApplication “http://kmsnet:5050” |Get-SPSite –Limit All | Get-SPWeb -Limit All | Where-Object { (Get-SPFeature "12f73b57-1db8-4272-3d45-d8c1cc9f3d41" -ErrorAction SilentlyContinue -Web $_.Url) -eq $null} | Select Url

Below script will provide list of webs(sub sites including toplevel site) where specified feature is installed and activated in all site collections from specified web application.

Get-SPWebApplication “http://kmsnet:5050” |Get-SPSite –Limit All | Get-SPWeb -Limit All | Where-Object { (Get-SPFeature "12f73b57-1db8-4272-3d45-d8c1cc9f3d41" -ErrorAction SilentlyContinue -Web $_.Url) -ne $null} | Select Url

Activate Web (subsite) scoped feature in all existing sites and subsites using PowerShell

We ware asked to enable a new rule that site/sub site cannot be deleted if any document declared as record. Of course I have added  new web deleting event receiver and used a web scoped feature to register the event on web.

But we have more than 100000 sub sites in our SharePoint farm. And enabling web scoped feature for all existing sites became a risk and performance hit.

I had created two scripts to resolve this issue. 1. PS script to collect all site collection from a web application and store it in a XML file. 2. PS script will read the XML file and process the site collection one by one as we got a provision to pass number site to be processed at one time. It reduced the performance hit and trace issue if any feature failed to activate

PowerShell script to Fetch All Site Collection:

# This script to generate XML file which contains all site collections in web application

# .\1_GetsiteCollections.ps1 -url "http://kmsnet:5500/" 



param
(
[string]$url   # Web application URL

)

If ((Get-PSSnapIn -Name Microsoft.SharePoint.PowerShell -ErrorAction Stop) -eq $null )  
{ Add-PSSnapIn -Name Microsoft.SharePoint.PowerShell } 

#Start-Transcript
if($url)
{

[string]$filepath = $(get-location).Path;
# XML file generation code 
$ErrorActionPreference = "SilentlyContinue"
#$ErrorActionPreference = "Stop"

# Create a new XML File with config root node
[System.XML.XMLDocument]$oXMLDocument=New-Object System.XML.XMLDocument

# New Node
[System.XML.XMLElement]$oXMLRoot=$oXMLDocument.CreateElement("SiteCollection")
# Append as child to an existing node
$oXMLDocument.appendChild($oXMLRoot)
    $site = get-spsite $url
    $WebApp = $site.webapplication 
    $Count =0;
    try
    {
   foreach ($spsite in $WebApp.sites) 
   {
            [System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("site"))
            $Count +=1;
       $oXMLSystem.SetAttribute("Count",$($Count))
       $oXMLSystem.SetAttribute("URL",$($spsite.Url))
            $oXMLSystem.SetAttribute("GUID",$($spsite.ID))
            $oXMLSystem.SetAttribute("Status","New")
                        
        }
        
        $xmlPath=$filepath + "\SiteCollXML";
        New-Item -force -ItemType directory -Path $xmlPath;
        $Filename = $filepath + "\SiteCollXML\SiteURL.xml"
        $oXMLDocument.Save($Filename )
        Write-host " Site collection file is generated : " $Filename
        
    }
     catch
    {
       # $Error; 
        $ErrorMessage = $_.Exception.Message
        write-host $ErrorMessage 


        $FailedItem = $_.Exception.ItemName
        write-host $FailedItem 
        
    }

}
else
{
Write-host "Please provide the Web Application Url...!!!" -foregroundcolor "Yellow"
}

PowerShell Script to process each site collection.

# .\2_EnableWebScopedFeature.ps1 -count 5 -Enable Y -FeatureId "12f73b57-1db8-4272-9d95-d8c1cc9f3d41"

param
(
    
    [int]$count =$(throw "Count is mandatory, please provide a value."), # No. of site collections to be processed
    [string]$Enable =$(throw "Pass y/Y to Activate the feature else pass n/N to deactivate the deature"),
    [string]$FeatureId =$(throw "Pass feature Id")
    
 )



Start-Transcript

If ((Get-PSSnapIn -Name Microsoft.SharePoint.PowerShell -ErrorAction Stop) -eq $null )  
{ Add-PSSnapIn -Name Microsoft.SharePoint.PowerShell } 

write-host "Successfully added SharePoint PowerShell snapins"

#$ErrorActionPreference = "Stop"
$ErrorActionPreference = "SilentlyContinue"
$curDir=$(get-location).Path;
$LogTime = Get-Date -Format yyyy-MM-dd_h-mm  
$LogFile = $curDir + "\Logs1-$LogTime.txt"  
$DocIDFile = $curDir + "\Log2-$LogTime.txt"  
write-host "Log file Location : " $LogFile


$filePath = $curDir + "\SiteCollXML\SiteURL.xml"

#write-host "source XMl file Path is $filePath "
write-host " Doc Id file " $DocIDFile 


$SiteCollectionsXML = [xml](gc $filePath)
try
{
    write-host "$count number of site collection requested to process";
    $currentcount = 0
    $DocIDcount = 0

$SiteCollectionsXML = [xml](gc $filePath)
$newItems=$SiteCollectionsXML.SiteCollection.site | where { $_.Status -eq "New"} 
    $node="";
    foreach ($sColl in $newItems)
  {
      if ([int]$currentcount -lt $count)
            {
        write-host "Start: Site Collection ";
        
        try
        {
        $currentcount = $currentcount + 1
         $node = $SiteCollectionsXML.SiteCollection.site | where {$_.GUID -eq $sColl.GUID}   
        $node.Status = "New"
        $objsite = Get-SPSite $sColl.URL;
        if($Enable -eq "Y" -and $Enable -eq "y")
        {
        $objsite | Get-SPWeb -limit all | ForEach-Object {Enable-SPFeature -Identity $FeatureId -Url $_.Url -Confirm:$false -ErrorAction:SilentlyContinue -Force}
        $node.Status = "Feature Activated"
        }
        
        if($Enable -eq "N" -and $Enable -eq "n")
        {
        $objsite | Get-SPWeb -limit all | ForEach-Object {Disable-SPFeature -Identity $FeatureId -Url $_.Url -Force -Confirm:$false -ErrorAction:SilentlyContinue}
        $node.Status = "Feature Dectivated"
        }
        
        $objsite.Dispose();
        
              
        
         
        }
        catch
        {
        $ex = $_.Exception 
        $node.Status = "Failed to Activate: $ex.Message" 
        Write-Error "Error on $siteURL details: $ex.Message" 
continue 
        
        }
        finally
        {
        $objsite.Dispose();
        $SiteCollectionsXML.Save($filePath);
        }
        
          }
    }
        $SiteCollectionsXML.Save($filePath)
}
catch
{
       
    $ErrorMessage = $_.Exception.Message
    write-host $ErrorMessage 

    $FailedItem = $_.Exception.ItemName
    write-host $FailedItem 
       
    $node = $SiteCollectionsXML.SiteCollection.site | where {$_.GUID -eq $Guid}
    $node.Status = "Error"

    Add-Content -path $LogFile -value ("`n" + "Site URL : " + ($SiteCollectionUrl)  + ' Site GUID : ' + ($Guid) + ' Error: ' + ($ErrorMessage) )

    $SiteCollections.Save($filePath)

}

Stop-Transcript

XML Output

<SiteCollection>
  <site Count="1" URL="http://kmsnet:5500" GUID="870c5dbb-c1b8-417a-89b3-c428369b7e45" Status="Feature Activated" />
  <site Count="2" URL="http://kmsnet:5500/SC1/" GUID="888b3fd3-48a1-41c5-b1c9-bd5b9102b29f" Status="Feature Activated" />
  <site Count="3" URL="http://kmsnet:5500/SC2" GUID="9f6f0d3b-1e02-42e0-8fbd-070b298576dc" Status="Feature Activated" />

</SiteCollection>
 
 
 

Increase List items threshold in SharePoint

We think a lot if want to increase the list view items threshold for the web application because it will be applied to all the site collections that running under the same web application. When we work with list there are many question comes in our mind.

1. SharePoint list supports 3 million item in a list but if threshold limit is crossed the the user will not be able perform many operation including OOTB (Additional filter on list view, grouping, adding new filed or updating existing field). How to avoid such situation?

2. How to increase the threshold for a single list or single web(sub site)?

3. Are there any possibilities for viewing all items/ navigating to all items without any trouble?

4. How majorly system performance impacted if list view threshold increase for the total web application? Are there any best practices?

5. Why Server object model not returning all items from a view when fetching items from specific view?

6. Why OOTB view query is overriding custom CAML query when fetching specific view with CAML query?

and there are more question may arise if you dig further on SharePoint list.

But we can increase the List threshold for a specific list using server object mode / PowerShell scripts.

Server Object Model

using(SPSite site = new SPSite(@"http://kmsnet:5050"))
{
   foreach(SPWeb web in site.AllWebs)
   {
     web.AllowUnsafeUpdates=true;
     SPList lst=web.Lists.TryGetList("MyList");
     if(lst != null)
     {
        lst.EnableThrottling=false;
        lst.Update();       
     }
     web.Update();
     web.AllowUnsafeUpdates=false;
     web.Dispose();
   }
}

PowerShell

Add-PSSnapin Microsoft.SharePoint.PowerShell #-ErrorAction SilentlyContinue

$site = Get-SPSite -Identity "http://kmsnet:5050/"

foreach($web in $site.AllWebs)
{
   $web.AllowUnsafeUpdates = $True;
   $list=$web.Lists.TryGetList("MyList");
   if($list -ne $null)
   {
        $list.EnableThrottling = $False;
        $list.Update();
        Write-Host "List Updated in web" $web.Title
   }
   $web.Update();
   $web.AllowUnsafeUpdates = $False;
   $web.Dispose()
}
$site.Dispose();

Start SharePoint timer job and wait till job finished using PowerShell

Start or Run SharePoint timer job and wait till the job get completed.

Below PowerShell scripts to start the Document ID timer jobs(Document enable, Document Id Assignment) for specific web application. The function need to parameters one is job name and next one is web application.

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

function StartJobOnWebApp
{
    param([string]$WebAppName, [string]$JobName)

    Write-Host " ";
    Get-SPWebApplication;
    Write-Host " ";
 
    $WebApp = Get-SPWebApplication $WebAppName;
 

    ##Getting right job for right web application
    $job = Get-SPTimerJob | ?{$_.Name -match $JobName} | ?{$_.Parent -eq $WebApp}
    if($null -ne $job)
    {
        $startet = $job.LastRunTime
        Write-Host -ForegroundColor Yellow -NoNewLine "Running"$job.DisplayName"Timer Job."
        Start-SPTimerJob $job

        ##Waiting til job is finished
        while (($startet) -eq $job.LastRunTime)
        {
            Write-Host -NoNewLine -ForegroundColor Yellow "."
            Start-Sleep -Seconds 2
        }

        ##Checking for error messages, assuming there will be errormessage if job fails
        if($job.ErrorMessage)
        {
            Write-Host -ForegroundColor Red "Possible error in" $job.DisplayName "timer job:";
            Write-Host "LastRunTime:" $lastRun.Status;
            Write-Host "Errormessage:" $lastRun.EndTime;

        }
        else
        {
            Write-Host -ForegroundColor Green $job.DisplayName"Timer Job has completed.";
        }
    }
    else
    {
        Write-Host -ForegroundColor Red "ERROR: Timer job" $job.DisplayName "on web application" $WebApp "not found."
    }

}

# Input parameter for site collection/web application
$siteUrl = "http://kmsnet:2020/"

$rootSite = New-Object Microsoft.SharePoint.SPSite($siteUrl)

$spWebApp = $rootSite.WebApplication

#run document ID timer job
StartJobOnWebApp  $spWebApp.url "DocIdEnable"

# run Document ID Assignment  timer job
StartJobOnWebApp  $spWebApp.url "DocIdAssignment"