{"id":1131,"date":"2013-04-16T21:40:27","date_gmt":"2013-04-16T21:40:27","guid":{"rendered":"http:\/\/turtlellc.com\/?p=1131"},"modified":"2017-09-22T20:00:58","modified_gmt":"2017-09-22T20:00:58","slug":"archive-a-sharepoint-list","status":"publish","type":"post","link":"https:\/\/www.turtle.works\/knowledge\/archive-a-sharepoint-list\/","title":{"rendered":"Archive a SharePoint List"},"content":{"rendered":"

\"archive<\/a><\/p>\n

Intro<\/h2>\n

A SharePoint list was filling up rapidly and had exceeded 5,000 items. Not wanting to use filtered views, the user instead wanted to archive the older items. This seemed like the perfect automation nut to to crack open with the PowerShell ISE editor. Keep reading to learn how to archive a SharePoint list.<\/p>\n

The rate at which users were entering items\u00a0required\u00a0an archive done every quarter. From a high level this seemed straight forward:<\/p>\n

    \n
  1. Connect to the site<\/span><\/li>\n
  2. For each year, going back 10<\/span>\n
      \n
    1. And for each quarter<\/span>\n
        \n
      1. Query the list for items to archive<\/span><\/li>\n
      2. Create an archive list if necessary<\/span><\/li>\n
      3. Move the items to the archive list<\/span><\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n

        Connect to the site<\/h3>\n

        Let’s begin by connecting to the site there are two ways to do this either by hard coded values or passing in the site collection and web site as parameters to the script. You’ll notice the very first line of the script deals with parameters. There are four parameters for this script:<\/p>\n

          \n
        1. $sitePath: which is the site collection for the list<\/span><\/li>\n
        2. $webPath: which is the relative path for any sites or sub-sites within the site collection<\/span><\/li>\n
        3. $sourceList: which is the list that needs to be archived<\/span><\/li>\n
        4. $destListPrefix: which is the list we write archived items to<\/span><\/li>\n<\/ol>\n

          The first line of the script:<\/p>\n

          [powershell]
          \nparam(
          \n[string]$sitePath = “http:\/\/sharepoint1”,
          \n[string]$webPath = “”,
          \n[string]$sourceList = “Supervisor Log”,
          \n[string]$destListPrefix = “Supervisor Log”)
          \n[\/powershell]<\/p>\n

          And the actual connection:<\/p>\n

          [powershell]
          \n$site = new-object Microsoft.SharePoint.SPSite($sitePath)
          \n$web = $site.AllWebs | where { $_.name -eq $webPath}
          \n[\/powershell]<\/p>\n

          Set archive date variables<\/h4>\n

          The main code path is wrapped in a try catch block near the end of the script. The code block gets the current year and month and then calls a function to determine what the archived date variables are. The setArchiveDateVars function has five parameters: current year, current month, archive year, archive month, and archive quarter. \u00a0The archive parameters are passed by reference. \u00a0Below is the source code for that function, you can see how it handles moving back to the previous month, previous quarter and previous year.<\/p>\n

          [powershell]<\/p>\n

          function SetArchiveDateVars([int]$curYr, [int]$curMonth,
          \n[ref]$archYr, [ref]$archMonth, [ref]$archQtr)
          \n{
          \n#Set archive quarter, year and month
          \nif ($curMonth -lt 4)
          \n{
          \n$archYr.Value = $curYr – 1
          \n$archMonth.Value = 12
          \n$archQtr.Value = 4
          \n}
          \nelseif (3 -lt $curMonth -and $curMonth -lt 7)
          \n{
          \n$archYr.Value = $curYr
          \n$archMonth.Value = 3
          \n$archQtr.Value = 1
          \n}
          \nelseif (6 -lt $curMonth -and $curMonth -lt 10)
          \n{
          \n$archYr.Value = $curYr
          \n$archMonth.Value = 6
          \n$archQtr.Value = 2
          \n}
          \nelseif (9 -lt $curMonth)
          \n{
          \n$archYr.Value = $curYr
          \n$archMonth.Value = 9
          \n$archQtr.Value = 3
          \n}
          \n}
          \n[\/powershell]<\/p>\n

          For each year and quarter<\/h3>\n

          After setting the archived date variables, the script sets the $site, $web and listA objects. $listA is the list to archive. \u00a0Once we have set those SharePoint objects in our script, we begin looping backward on the year and then quarter.<\/p>\n

          [powershell]
          \ntry
          \n{
          \n…
          \n#Loop thru years
          \nfor ($archYr = $archYr; ($curYr – $archYr) -lt 10; $archYr–)
          \n{
          \n#Loop thru quarters
          \nfor ($qtrCnt = 1; $qtrCnt -lt 5; $qtrCnt++)
          \n{
          \n…
          \n[\/powershell]<\/p>\n

          Query the list for items to archive<\/h3>\n

          For each quarter we retrieve list items by using the GetItemsToMigrate function. This function uses SPQuery<\/a> to filter list items.<\/p>\n

          [powershell]
          \n<# Returns: SPList of items between $startDate and $endDate Globals Used: $listA #>
          \nfunction GetItemsToMigrate([string]$startDate, [string]$endDate)
          \n{
          \n$spQuery = new-object Microsoft.SharePoint.SPQuery
          \n$camlQuery =
          \n‘<Where>
          \n<And>
          \n<Leq>
          \n<FieldRef Name=”MyCreated” \/>
          \n<Value Type=”DateTime” IncludeTimeValue=”False”>’ + $endDate + ‘<\/Value>
          \n<\/Leq>
          \n<Geq>
          \n<FieldRef Name=”MyCreated” \/>
          \n<Value Type=”DateTime” IncludeTimeValue=”False”>’ + $startDate + ‘<\/Value>
          \n<\/Geq>
          \n<\/And>
          \n<\/Where>’
          \n$spQuery.Query = $camlQuery
          \n$localItems = $listA.GetItems($spQuery)
          \n, $localItems
          \n}
          \n[\/powershell]<\/p>\n

          The , <\/strong>at the front of the last line ensures only the list of $localItems is returned from the function.<\/p>\n

          Create an archive list if necessary<\/h3>\n

          Now that we have a list of items to archive, we can create an archive list and move those items to that list. \u00a0We only create a list when it doesn’t already exist, we check this with the TryGetList function.<\/p>\n

          We create the archive list by saving the source list has a template, then creating a new list from that template and finally adding fields to handle CreatedBy, ModifiedBy, Created and Modified.<\/p>\n

          [powershell]
          \nfunction CreateList([string]$destListName, [string]$destListDesc)
          \n{
          \n#Make Template
          \n$sourceList = $web.Lists[$sourceListName]
          \n$templateName = $sourceList.Title
          \n$templateFileName = $sourceList.Title
          \n$sourceList.SaveAsTemplate($templateFileName, $templateName, $sourceList.Description, 0)<\/p>\n

          #Add List
          \n$listTemplate = $site.GetCustomListTemplates($web)[$templateName]
          \n$web.Lists.Add($destListName, $destListDescription, $listTemplate)
          \n$web.Update()<\/p>\n

          #Remove Template
          \n$listTemplates = $site.RootWeb.Lists[“List Template Gallery”]
          \n$listTemplateItems = $listTemplates.Items
          \nforeach($listTemplateItem in $listTemplateItems)
          \n{
          \nif($listTemplate.Name.Equals($listTemplateItem[“Title”]))
          \n{
          \n$listTemplateItem.Delete()
          \nbreak
          \n}
          \n}
          \n#Add Columns to List
          \n$listNew =$web.Lists[$destListName]
          \n$listNew.Fields.Add(“Orig Created”, [Microsoft.SharePoint.SPFieldType]::DateTime, $False)
          \n$listNew.Fields.Add(“Orig Created By”, [Microsoft.SharePoint.SPFieldType]::User, $False)
          \n$listNew.Fields.Add(“Orig Modified”, [Microsoft.SharePoint.SPFieldType]::DateTime, $False)
          \n$listNew.Fields.Add(“Orig Modified By”, [Microsoft.SharePoint.SPFieldType]::User, $False)
          \n$listNew.Update()
          \n}
          \n[\/powershell]<\/p>\n

          Move the items to the archive list<\/h3>\n

          The move is a two step process, copy the item to the archive list then delete the item from the source list. \u00a0I use the CopyItem function to:<\/p>\n

            \n
          1. Copy each field<\/li>\n
          2. Handle read only fields<\/li>\n
          3. Handle the CreatedBy, ModifiedBy, Created dates and Modified dates<\/li>\n
          4. Handle file attachments<\/li>\n<\/ol>\n

            Files attached to an item are handled in the CopyItem function with this block of code:<\/p>\n

            [powershell]
            \n#Handle Attachments
            \nforeach($leafName in $item.Value.Attachments)
            \n{
            \n$spFile = $web.GetFile($($item.Value.Attachments.UrlPrefix + $leafName));
            \n$newItem.Attachments.Add($leafName, $spFile.OpenBinary());
            \n}
            \n[\/powershell]<\/p>\n

            Then we decrement archive quarter, making adjustments for the previous year when necessary, and continue the looping.<\/p>\n

            The Full script file link below contains the entire script needed to archive a SharePoint list. Enjoy the script and feel free to comment.<\/p>\n

            Resources<\/h2>\n

            Full script file<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

            Intro A SharePoint list was filling up rapidly and had exceeded 5,000 items. Not wanting to use filtered views, the user instead wanted to archive the older items. This seemed like the perfect automation nut to to crack open with the PowerShell ISE editor. Keep reading to learn how to archive a SharePoint list.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[42,20],"tags":[],"class_list":["post-1131","post","type-post","status-publish","format-standard","hentry","category-powershell","category-sharepoint"],"_links":{"self":[{"href":"https:\/\/www.turtle.works\/knowledge\/wp-json\/wp\/v2\/posts\/1131","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.turtle.works\/knowledge\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.turtle.works\/knowledge\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.turtle.works\/knowledge\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.turtle.works\/knowledge\/wp-json\/wp\/v2\/comments?post=1131"}],"version-history":[{"count":1,"href":"https:\/\/www.turtle.works\/knowledge\/wp-json\/wp\/v2\/posts\/1131\/revisions"}],"predecessor-version":[{"id":1711,"href":"https:\/\/www.turtle.works\/knowledge\/wp-json\/wp\/v2\/posts\/1131\/revisions\/1711"}],"wp:attachment":[{"href":"https:\/\/www.turtle.works\/knowledge\/wp-json\/wp\/v2\/media?parent=1131"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.turtle.works\/knowledge\/wp-json\/wp\/v2\/categories?post=1131"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.turtle.works\/knowledge\/wp-json\/wp\/v2\/tags?post=1131"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}