SharePoint Data Migration/Conversion

Weather you are performing a SharePoint migration or you simply want to restructure your current farm, PowerShell provides ways to reorganize/migrate your data without having to employ 3rd party tools.

When we think of data migration, as it pertains to SharePoint Information Architecture we think of Web Apps, Site Collections, and Webs. Maybe we want a current web to become a site collection? Maybe we want to move a web or make an existing site collection a web? These are all questions you must ponder during the planning of your data migration. We can get much more granular than this and go down the list and even item level, but my normal encounters with this type of requirement involve moving the top level SP objects rather then items and lists.

 

What should we do first?

First I create a matrix in Excel and export that matrix to CSV. This matrix is our master lookup table and rules list of what we want to do within our farm. See below for a sample of what I am talking about. I have put this into a table for easier viewing.

Name	SourceURL	DestinationURL	Path	Owner	Email	Object Type	Action	Template
Finance	http://lab/sites/finance	http://lab/dept/fin	D:\Temp\fin.cmp	Lab\Administrator	admin@company.com	Web	Convert	BLANKINTERNET#0
Support	http://lab/sites/support	http://lab/dept/support	D:\Temp\sup.cmp	Lab\Administrator	admin@company.com	Web	Convert	BLANKINTERNET#0
Development	http://lab/sites/dev	http://lab/dept/support/dev	D:\Temp\dev.cmp	Lab\Administrator	admin@company.com	SiteCollection	Convert	STS#0
News	http://lab/sites/news	http://lab/dept/news	D:\Temp\news.bak	Lab\Administrator	admin@company.com	SiteCollection	Move	NA
Sales	NA	http://lab/dept/sales	NA	Lab\Administrator	admin@company.com	SiteCollection	New	BLANKINTERNET#0

Note: Object Type and Action determine what function will be called on these objects.

Object Type = Web + Action = Convert = Convert Web to Site Collection

All of these options are things that can be added or removed for your scenario. The script you can use is below.

#Script

# Load Snapin and get script directory
add-pssnapin microsoft.sharepoint.powershell

#Logging functions courtesy of Brian Caauwe (MCM: SharePoint 2010)
function Write-ToScreen($msg)
{
    $currentTime = Get-Date -UFormat "%H:%M:%S"
    $msg = $currentTime + " :: INF :: " + $msg

    Write-ToLog($msg)
    Write-Host -ForegroundColor Yellow $msg
}

function Write-ActionToScreen($msg)
{
    $currentTime = Get-Date -UFormat "%H:%M:%S"
    $msg = $currentTime + " :: ACT :: " + $msg

    Write-ToLog($msg)
    Write-Host -ForegroundColor Green $msg
}

function Write-ErrorToScreen($msg)
{
    $currentTime = Get-Date -UFormat "%H:%M:%S"
    $msg = $currentTime + " :: ERR :: " + $msg

    Write-ToLog($msg)
    Write-Host -ForegroundColor Red $msg
}

function Write-ToLog($msg)
{
    $logDate = Get-Date -UFormat "%m%d%Y"
    $path = "SharePointReOrg-" + $logDate + ".log"

    $msg | Out-File $path -append
}

function New-SiteCollections($DestinationURL,$Owner,$Name,$Template)
{
    Start-SPAssignment
    Write-ActionToScreen "Creating new site collection $Name"
    $nsite = New-SPSite -Url $DestinationURL -OwnerAlias $Owner -Name $Name -Template $Template
    Write-ToScreen "Finished creating $Name site collection"
    Stop-SPAssignment -Global
}

function Convert-WebToSiteCollection($SourceURL,$Path,$DestinationURL,$Owner,$Name)
{
    Write-ActionToScreen "Creating new site collection $Name"
    $nsite = New-SPSite -Url $DestinationURL -OwnerAlias $Owner -Name $Name
    Write-ToScreen "Finished creating $Name site collection"
    Write-ActionToScreen "Backing up source web $SourceURL"
    $eweb = Export-SPWeb -Identity $SourceURL -Path $Path -Force
    Write-ToScreen "Finished backing up $SourceURL"
    Write-ActionToScreen "Restoring backup $path to destination site collection $DestinationURL"
    $iweb = Import-SPWeb -Identity $DestinationURL -Path $Path -Force
    Write-ToScreen "Finished restoring web $SourceURL to desination site collection $DestinationURL"
    Write-ActionToScreen "Deleting the backup file to save space"
    Remove-Item D:\BackupLogs\*.cmp
    Write-ToScreen "Finished deleting backup file."
    Stop-SPAssignment -Global
}

function Convert-SiteCollectionToWeb($SourceURL,$DestinationURL,$Path,$Name)
{
    Write-ActionToScreen "Exporting web $SourceURL to $path"
    $eweb = Export-SPWeb -Identity $SourceURL -Path $Path -Force
    Write-ToScreen "Finished exporting $DestinationURL to $path"
    Write-ActionToScreen "Creating new web at $DestinationURL"
    $nweb = New-SPWeb -Url $DestinationURL -Name $Name -AddToQuickLaunch
    Write-ToScreen "Finished creating new web at $DestinationURL"
    Write-ActionToScreen "Restoring data from $SourceURL into new web $DestinationURL"
    $iweb = Import-SPWeb -Identity $DestinationURL -Path $Path -Force
    Write-ToScreen "Finished restoring web $SourceURL to destination site collection $DestinationURL"
    Write-ActionToScreen "Deleting the backup file to save space"
    Remove-Item D:\BackupLogs\*.cmp
    Write-ToScreen "Finished deleting backup file."
    Stop-SPAssignment -Global
}

function Move-Web($SourceURL,$DestinationURL,$Path,$Name)
{
    Start-SPAssignment
    Write-ActionToScreen "Exporting web $SourceURL to $path"
    $eweb = Export-SPWeb -Identity $SourceURL -Path $Path -Force
    Write-ToScreen "Finished exporting $DestinationURL to $path"
    Write-ActionToScreen "Creating new web at $DestinationURL"
    $nweb = New-SPWeb -Url $DestinationURL -Name $Name -AddToQuickLaunch
    Write-ToScreen "Finished creating new web at $DestinationURL"
    Write-ActionToScreen "Restoring data from $SourceURL into new web $DestinationURL"
    $iweb = Import-SPWeb -Identity $DestinationURL -Path $Path -Force
    Write-ToScreen "Finished restoring web $SourceURL to destination site collection $DestinationURL" 
    Write-ActionToScreen "Deleting the backup file to save space"
    Remove-Item D:\BackupLogs\*.cmp
    Write-ToScreen "Finished deleting backup file."
    Stop-SPAssignment -Global
}

function Move-SiteCollection($SourceURL,$DestinationURL,$Path,$Name)
{
    Start-SPAssignment
    Write-ActionToScreen "Backing up site collection $SourceURL"
    $bsite = Backup-SPSite -identity $SourceURL -path $Path -force
    sleep 4
    Write-ToScreen "Finished backing up site collection $SourceURL to $Path"
    Write-ActionToScreen "Restoring $Path to $DestinationURL"
    $rsite = Restore-SPSite -identity $DestinationURL -path $Path -force -Confirm:$false
    Write-ToScreen "Finished restoring $path to $DestinationURL"
    Write-ActionToScreen "Deleting the backup file to save space"
    Remove-Item D:\BackupLogs\*.bak
    Write-ToScreen "Finished deleting backup file."
    Stop-SPAssignment -Global
}

function New-Webs($DestinationURL,$Name,$Template)
{
    Start-SPAssignment
    Write-ActionToScreen "Creating New Web $DestinationURL"
    $nweb = New-SPWeb -Url $DestinationURL -Name $Name -Template $Template -AddToQuickLaunch
    Write-ToScreen "Finished creating web $Name at $DestinationURL"
    Stop-SPAssignment -Global
}

$SiteInfo = Import-Csv .\siteinfo.csv
$tranpath = "SharePointReOrgTranscript-" + $logDate + ".log"
Start-Transcript -Path .\$tranpath -Append

#Create New Site Collections
Write-ActionToScreen "CREATE NEW SITE COLLECTIONS"
$NewSites = $SiteInfo | where { $_.Action -eq "New" -and $_.Type -eq "SiteCollection" }
foreach ($site in $NewSites)
{
    New-SiteCollections $site.DestinationURL $site.OwnerAlias $site.Name $site.Template
}

#Move Site Collections
Write-ActionToScreen "MOVE EXISTING SITE COLLECTIONS"
$MoveSites = $SiteInfo | where { $_.Action -eq "Move" -and $_.Type -eq "SiteCollection" }
foreach ($site in $MoveSites)
{
    Move-SiteCollection $site.SourceURL $site.DestinationURL $site.SavePath $site.Name
}

#Create New Webs
Write-ActionToScreen "CREATING NEW WEBS"
$NewWebs = $SiteInfo | where { $_.Action -eq "New" -and $_.Type -eq "Web" }
foreach ($web in $NewWebs)
{
    New-Webs $web.DestinationURL $web.Name $web.Template
}

#Convert Webs to Site Collection
Write-ActionToScreen "CONVERTING WEBS TO SITE COLLECTIONS"
$ConvertWebs = $SiteInfo | where { $_.Action -eq "Convert" -and $_.Type -eq "Web" }
foreach ($Web in $ConvertWebs)
{
    Convert-WebToSiteCollection $Web.SourceURL $Web.SavePath $Web.DestinationURL $web.OwnerAlias $web.Name
}

#Convert Site Collections to Webs
Write-ActionToScreen "CONVERT SITE COLLECTIONS TO WEBS"

$ConvertSites = $SiteInfo | where { $_.Action -eq "Convert" -and $_.Type -eq "SiteCollection" }
foreach ($site in $ConvertSites)
{
    Convert-SiteCollectionToWeb $site.SourceURL $site.DestinationURL $site.SavePath $site.Name
}

#Move Webs
Write-ActionToScreen "MOVING EXISTING WEBS"
$MoveWebs = $SiteInfo | where { $_.Action -eq "Move" -and $_.Type -eq "Web" }
foreach ($web in $MoveWebs)
{
    Move-Web $web.SourceURL $web.DestinationURL $web.SavePath $web.Name
}

Stop-Transcript

 

{Kam}

Using SQL to supply metadata to SharePoint

Recently I had a requirement to publish over 1000 documents to a SharePoint library. These documents also needed to be tagged with the necessary metadata. Luckily for me, a SQL table existed that held all of the metadata along with a field that matched the filename. This made it easy to perform a lookup and return the needed metadata and update SharePoint.
The script runs in two parts. The first function uploads a list of files to a specified Document library. You can then specify any number of metadata via the foreach loop that have applied to each matching item.
In order to query sql you need to have the SQL module in one of your $ENV: PSModulePath locations.

If you do not already have this you can install the SQL-Module cmdlets here via my other post.

#Script
Add-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue
Import-Module sqlserver
#Uploads documents to sharepoint via URL, LibraryName and full path of file
function UploadToSharePoint($WebUrl,$DocLib,$FilePath) {
    $web = Get-SPWeb $weburl
    $List = $Web.GetFolder($DocLib)
    $files = $list.files
    $FileName = $FilePath.Substring($FilePath.LastIndexOf("\")+1) 
    $file = Get-Item $filepath
    $files.add($DocLib +"/" + $filename, $file.OpenRead(),$false) | Out-Null
    Write-host -ForegroundColor Blue "$Filename has been uploaded"
    $web.Dispose()
}#Function

#Function to apply metadata to each column .Must add line for each column to be updated $SPITEM must have where clause to make match to update appropriate field.
function ApplyMetadata($web,$doclib,$filename,$company,$date,$idnumber) {
    $web = Get-SPWeb $web
    $SPlist = $web.Lists["$DocLib"]
    $SPitem = $Splist.items | where {$_.Name -eq $FileName}
    $spitem["Company"] = "$Company"
    $spitem["Date"] = "$Date"
    $spitem["IDNumber"] = "$IDNUmber"
    #Add new item for each piece of metadata
    $SPItem.Update()
    Write-host -ForegroundColor Green "Metadata assigned to $filename"
    $web.Dispose()
} #function

#Create SQL connection and sql query
#$sqlConnection = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) "$sqlServer"   
$sqlServer = "SPSQL"
$sqlDatabase = "MetaData_DB"
$sqlQuery = "SELECT Filename, Date, Company, IDNumber FROM  MetaData_SampleView"
#Load in all files that you want to upload the SharePoint
$UploadFiles = Get-ChildItem C:\testfiles -Recurse -Include *.txt
#Find associated metadata
$metadata = @(Get-SqlData -sqlserver $sqlConnection -dbname $sqlDatabase -qry $sqlQuery -ea stop)
#Designate Site URL and Doc Library to upload to.
$SiteURL = "http://lab02"
$DocLibrary = "Shared Documents"

#Cycle through all files in the $UploadFiles and put then into the Sharepoint libary and apply metadata. All Metadata values need to be mapped within the loop
foreach ($f in $UploadFiles) {
   Write-host -ForegroundColor Blue "Processing $($f.Name)..."
   $Meta = $metadata | where {$_.filename -eq $f.name}
  
   UploadToSharePoint $SiteURL $Doclibrary $f.FullName
   ApplyMetadata $SiteURL $DocLibrary $f.Name $meta.Company $meta.Date $meta.IDNumber
}
Note: Another way to do this eaily is to use Access. You can create a complex update query using multiple ODBC source to update your SharePoint metadata. This can be a very effective way to mass update metadata in SharePoint. I have had companies who need to auto-tag content daily and they build Access macros that run on a scheduled task to complete the tagging.
{Kam}

Installing SQL cmdlets for PowerShell

If you want to run command against SQL such as Invoke-SQLCmd or just run a query against a table or view and return a list of objects, you need to have the SQL cmdlets. You can download them using the link below. Prior to Posh 3.0 you will need to run Import-Module SQLServer. Posh 3.0 will auto import upon first command run.

Tip: A nice little feature of the SQL cmdlets are creating a PSDrive to your SQL box. You can do so by running the following command.

New-PSDrive -name Name -PSProvider SQLServer -Root "SQLServer:\SQL\SQLServerName"

Download SQLPSX.msi here

Capacity Planning

When working on a ECM/DM type SharePoint deployment where a client is interested in moving a large amount of their network data to their new/existing SharePoint farm some people step back and say wait a second, how much data are we talking here?
Not only are we worried about the shear amount of data but what is relevant and was is duplicate information? Using PowerShell we can easily weed out the files and folders within a path that are older than a certain date-time.
$files = get-childitem \\share –recurse | ?{$_.CreationTime -gt “Date”}
Then we can do some operation on that subset of files.
But a key metric that can help establish growth and usage is new file creation. How many new files are created daily? How many are updated? What are the busy days of the week for new/modified files? I am currently working on a project where they have a 1TB file share and they estimate that maybe 20% of data is pertinent information.They want to move it to SharePoint Online (O365) so storage space comes at a price. So to help get an idea of how data is being added to a certain location daily I whipped up a PowerShell script to tell us just that. It gives you a good trend analysis over any defined start/end time.


As you can see, this data shows us how many files are being added to this particular share(File path) daily from 8/1/2012 – 9/1/2012. It also tells us what our busiest days for new data are along with the estimated size added per day. This can be valuable information when we think about scaling a system to accommodate an already growing file system.
Right now the script is set to look for NEW files but I am adding in  the ability check for modified files daily as well. Modified metrics are not as valuable because we are normally making minor edits and not adding several MB of content. However, It does offer some information on daily usage(hits) that would be redirected to SharePoint in a scenario where file shares are being replaced by SharePoint.

#Script
New-FileReport.ps1
$files = Get-ChildItem "FilePath" -Recurse
[datetime]$StartDate = "8/1/2012"
[datetime]$EndDate = "9/1/2012"
[datetime]$CurDate = $StartDate
$output = ""
$output += "Date,TotalFiles,Size,Day`n"

while ($CurDate-le $EndDate)
{
    $tfiles = $files | where {$_.CreationTime -ge (Get-date -Date $CurDate -UFormat %m/%d/%Y) -and $_.CreationTime -lt (Get-date -Date $CurDate.AddDays(1) -UFormat %m/%d/%Y) }
    [double]$TotalSize = "0"
    $TotalFiles = $tfiles.count
        if ($TotalFiles -gt 1)
        {
            foreach ($f in $tfiles)
            {
                 $TotalSize += $f.Length /1MB
            }
        }
        else
        {
            $TotalSize = $f.Length /1MB
        }
    $day = $CurDate.DayOfWeek
    $output += "$CurDate,$TotalFiles,$TotalSize,$Day`n"
    #$output
    $CurDate = $CurDate.AddDays(1) 
}
$output | Out-File $path\FileInfo.csv -Force