Instantly share code, notes, and snippets.
Created
May 17, 2019 17:43
-
Star
(1)
1
You must be signed in to star a gist -
Fork
(0)
0
You must be signed in to fork a gist
-
Save joerodgers/5277b906d8cc8dd99113ccec5ce39e1f to your computer and use it in GitHub Desktop.
SharePoint 2010 to SharePoint 2013 Content Database Upgrade Automation
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<# | |
Microsoft provides programming examples for illustration only, without warranty either expressed or | |
implied, including, but not limited to, the implied warranties of merchantability and/or fitness | |
for a particular purpose. | |
This sample assumes that you are familiar with the programming language being demonstrated and the | |
tools used to create and debug procedures. Microsoft support professionals can help explain the | |
functionality of a particular procedure, but they will not modify these examples to provide added | |
functionality or construct procedures to meet your specific needs. if you have limited programming | |
experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting | |
line at (800) 936-5200. | |
---------------------------------------------------------- | |
History | |
---------------------------------------------------------- | |
04-22-2015 - Created | |
06-21-2015 - Removed feature deactivation code, making this a generic upgrade script | |
06-22-2015 - Added ability to remove URL migration suffixes (-migration, -temp, -eval) | |
07-01-2015 - Purge the 1st and 2nd stage recycle bins to allow SPSite.Rename() | |
08-03-2015 - fixed a bug with site renaming | |
08-21-2015 - fixed a but that prevented removal of legacy service accounts | |
09-09-2015 - Replaced Join-Path with [System.IO.Path]::Combine | |
https://connect.microsoft.com/PowerShell/feedback/details/779068/join-path-and-split-path-should-not-fail-if-the-path-does-not-exist-on-the-current-machine | |
01-06-2016 - Updated to allow for parallel site UI ugprades | |
03-22-2016 - Duplicated setting the max & warning site counts to earlier in the code to help prevent new sites from be added to the DB | |
during the content database upgrade process | |
04-25-2016 - Updated to include a summary showing any sites that failed to upgrade. | |
04-25-2016 - Updated to include a summary showing any sites that failed to upgrade. | |
06-21-2016 - Update to allow leaving the RootHNSCUrl null or empty to skip the HNSC conversion step | |
02-08-2018 - Updated script to write database upgrade log file to ULS log path | |
==============================================================#> | |
# Number of current threads to upgrade content databases in parallel. | |
# This is limited to a max of logical processors on the box, but >5 will probably kill the SQL Instance | |
$maxContentDatabaseUpgradeThreads = 5 | |
$maxSiteCollectionUIUpgradeThreads = 10 | |
# Array to hold database info | |
$databaseUpgradeInfo = @() | |
$databaseUpgradeInfo += New-Object PSObject -Property @{ | |
# unc path to staging database backup file, leave blank or $null of database is already mounted in SQL | |
UpgradeDatabaseUNCPath = "\\dc01\_backups\SP2010_CONTENT_UPGRADE_REGRESSSION_TEST.bak" | |
# 2013 Settings | |
SP2013ContentDatabaseName = "SP2013_CONTENT_UPGRADE_REGRESSSION_TEST" # the new name of the content database after its being migrated to SharePoint 2013 | |
SP2013TargetSQLInstance = "SQL01\SHAREPOINT"; # use the AG name if you are using SQL AOAGs, script will add it to the AG automatically | |
DataFileGrowthIncrement = "500MB"; # use the KB, MB or GB syntax | |
LogFileGrowthIncrement = "500MB"; # use the KB, MB or GB syntax | |
WebApplicationUrl = "https://root.contoso.com"; # web application to attach the database to in 2013 | |
RootHNSCUrl = "https://sharepoint.contoso.com"; # the root HNSC in 2013 to rename the sites under. Leave null/empty to leave sites as path based sites. | |
WarningSiteCount = 2000; # Specifies the number of site collections allowed in the content database prior to generating a warning event in the Windows event log. | |
MaxSiteCount = 5000; # Specifies the maximum number of site collections allowed in the content database | |
AvailabilityGroupName = $null; # The name of the availablity group, leave empty or null to not add to a SQL AOAG | |
AvailabilityGroupFileShare = $null; # The name of the availablity group file share, used for backing up the database | |
} | |
# output file generated that contains the same output from screen | |
$executionLog = "E:\LogFiles\ULS\DatabaseUpgradeExecutionLog_{0}_{1}.log" -f $env:COMPUTERNAME, [DateTime]::Now.ToString("yyyy-MM-dd_hh-mm-ss") | |
# if you are migating sites from an existing farm and give the sites a temporary migration name (-migration), you can optionally | |
# remove that name suffix here. | |
# Example: | |
# The 2010 url could be /sites/finance-migration. If you want the 2013 url will be /sites/finance, you would | |
# add '-migration' to the suffix list and it would be removed during the rename operation | |
$siteUrlSuffixesToRemove = @("-migration" ) | |
# leave any of these blank and no error or completion email notification emails will be sent | |
$smtpServer = "mail.contoso.com" | |
$emailNotificationTo = "" #"admin@contoso.com" | |
$emailNotificationFrom = "upgradeScript@contoso.com" | |
# Path on the local server to stage the site collection backup files | |
$localPathToStageBackupFiles = "C:\SiteBackup\" | |
# flag to update the sites to the 2013 look at feel during database upgrade | |
$upgradeSitesToSharePoint2013UI = $true | |
# delete any sites that are in the site recycle bin | |
$deleteUpgradedSitesInSiteRecycleBin = $true | |
# during the upgrade, rename the sites to fall under the following managed path | |
$moveAllSitesUnderTheFollowingManagedPath = "sites" | |
<############ YOU SHOULD NOT HAVE TO MODIFY ANYTHING BELOW THIS POINT ############> | |
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue | |
Import-Module -Name ".\Upgrade-SharePointContentDatabase.Utils.psm1" -Force | |
$moduleLoaded = Get-Module | ? { $_.Name -eq "Upgrade-SharePointContentDatabase.Utils" } | |
if(!$moduleLoaded) { return } | |
$currentStep = 1 | |
$totalSteps = 15 | |
# setup the exection log | |
Set-ExecutionLogFile -FilePath $executionLog | |
Write-Message "Executing User: $($env:USERDOMAIN)\$($env:USERNAME)" -LogFileOnly | |
# setup the email notification properties | |
Set-EmailNotificationProperties -ToAddress $emailNotificationTo -FromAddress $emailNotificationFrom -SmtpServer $smtpServer | |
# write our database info to the log file | |
Write-UpgradeDatabaseInfoToLogFile -DatabaseInformation $databaseUpgradeInfo | |
# if we don't have to convert site to HNSC, we can skip this step | |
if($($databaseUpgradeInfo | SELECT -Unique RootHNSCUrl | Measure).Count -eq 0) { $totalSteps-- } | |
# if we are not upgrading to the 2013 UI, we can skip this step | |
if(!$upgradeSitesToSharePoint2013UI) { $totalSteps-- } | |
# limit the number of threads to a max of the number of logical processsors on the box | |
$logicalProcessorCount = [int](@(Get-WmiObject -Class Win32_Processor) | Measure-Object -Property NumberOfLogicalProcessors -Sum).Sum | |
if($maxContentDatabaseUpgradeThreads -gt $logicalProcessorCount) | |
{ | |
$maxContentDatabaseUpgradeThreads = $logicalProcessorCount | |
Write-Message "`n`tLimiting max content database upgrade threads to the number of logical processsors ($maxContentDatabaseUpgradeThreads).`n" -ForegroundColor Yellow | |
} | |
# make sure this is a 2013 server | |
if((Get-SPFarm).BuildVersion.Major -ne 15) | |
{ | |
Write-Message "`n`t`tThis script is designed to run on the SharePoint 2013 farm only." -ForegroundColor Red | |
return | |
} | |
############################################################################################################ | |
# Validate the database backup file exist | |
############################################################################################################ | |
Write-Step -CurrentStep $currentStep -TotalSteps $totalSteps -Message "Validating Database Backup Files" -Color Green | |
$currentStep++ | |
$isvalid = $true | |
# enumerate all the distinct backup locations and make sure it exists | |
$databaseUpgradeInfo | SELECT -Unique UpgradeDatabaseUNCPath | % { | |
$exists = Test-Path -Path $_.UpgradeDatabaseUNCPath -PathType Leaf | |
if(!$exists) | |
{ | |
$isvalid = $false | |
Write-Message "`t`tFatal Error: Database backup file not found: $($_.UpgradeDatabaseUNCPath)" -ForegroundColor Red | |
} | |
} | |
if(!$isvalid) | |
{ | |
Send-EmailNotification -Subject "Fatal Error: Backup File Not Found" -Body "Backup file not found" -AttachConsoleLog | |
return | |
} | |
############################################################################################################ | |
# Validating the specified web applications exsit in the current farm | |
############################################################################################################ | |
Write-Step -CurrentStep $currentStep -TotalSteps $totalSteps -Message "Validating Web Application URLs" -Color Green | |
$currentStep++ | |
# enumerate all the distinct web applications and make sure they exist | |
$databaseUpgradeInfo | SELECT -Unique WebApplicationUrl | % { | |
$webApplication = Get-SPWebApplication -Identity $_.WebApplicationUrl -ErrorAction SilentlyContinue | |
if(!$webApplication) | |
{ | |
Write-Message "`t`tError: Web Application Not Found" -ForegroundColor Red | |
Write-Message "`t`tDetail: $($_.WebApplicationUrl)" -ForegroundColor Red | |
Send-EmailNotification -Subject "Fatal Error: Validating Web Application URLs" -Body "See attached log for exception details." -AttachConsoleLog | |
exit | |
} | |
} | |
############################################################################################################ | |
# Validating Site Collection Rename Requirements | |
############################################################################################################ | |
if(-not [string]::IsNullOrEmpty( $($databaseUpgradeInfo | SELECT -Unique RootHNSCUrl).RootHNSCUrl) ) | |
{ | |
Write-Step -CurrentStep $currentStep -TotalSteps $totalSteps -Message "Validating Site Collection Rename Requirements" -Color Green | |
$currentStep++ | |
# if we can't rename a site, we need to validate the site collection backup/restore folder path | |
$farm = Get-SPFarm | |
if( $farm.BuildVersion.Major -eq 15 -and $farm.BuildVersion.Build-lt 4693) | |
{ | |
if(!(Get-Item -Path $localPathToStageBackupFiles -ErrorAction SilentlyContinue)) | |
{ | |
Write-Message "`t`tError: Directory not found: $localPathToStageBackupFiles" -ForegroundColor Red | |
Send-EmailNotification -Subject "Fatal Error: Validating Site Collection Rename Requirements" -Body "See attached log for exception details." -AttachConsoleLog | |
return | |
} | |
} | |
else | |
{ | |
$isValid = $true | |
# enumerate all the distinct HNSC Root Urls | |
$databaseUpgradeInfo | SELECT -Unique RootHNSCUrl | % { | |
$exists = Get-SPSite -Identity $_.RootHNSCUrl -ErrorAction SilentlyContinue | |
if(!$exists) | |
{ | |
$isValid = $false | |
Write-Message "`t`tError: HNSC not found: $($_.RootHNSCUrl)" -ForegroundColor Red | |
} | |
} | |
if(!$isValid) | |
{ | |
Send-EmailNotification -Subject "Fatal Error: Validating Site Collection Rename Requirements" -Body "See attached log for exception details." -AttachConsoleLog | |
return | |
} | |
} | |
} | |
############################################################################################################ | |
# Validating the content database names specified don't exist in the 2013 farm | |
############################################################################################################ | |
Write-Step -CurrentStep $currentStep -TotalSteps $totalSteps -Message "Validating 2013 Content Database Names" -Color Green | |
$currentStep++ | |
$message = $null | |
$databaseUpgradeInfo | % { | |
if(Get-SPContentDatabase -Identity $_.SP2013ContentDatabaseName -ErrorAction SilentlyContinue) | |
{ | |
$message = "`t`tError: Content Database Exists: $($_.SP2013ContentDatabaseName)" | |
} | |
} | |
if($message) | |
{ | |
Write-Message $message -ForegroundColor Red | |
Send-EmailNotification -Subject "Fatal Error: Validating 2013 Content Database Names" -Body "See attached log for exception details." -AttachConsoleLog | |
return | |
} | |
############################################################################################################ | |
# Restore the content databases to the specified SQL instance | |
############################################################################################################ | |
Write-Step -CurrentStep $currentStep -TotalSteps $totalSteps -Message "Database Restoration and Configuration" -Color Green | |
$currentStep++ | |
try | |
{ | |
$databaseUpgradeInfo | % { | |
$dbUpgradeInfo = $_ | |
$backupFileUNCPath = $dbUpgradeInfo.UpgradeDatabaseUNCPath | |
$originalDatabaseNameFromBackupFile = Get-DatabaseNameFromBackupFile -DatabaseServer $_.SP2013TargetSQLInstance -BackupFilePath $backupFileUNCPath | |
############################################################################################################ | |
# Restore the content database | |
############################################################################################################ | |
if(Test-Path -Path $backupFileUNCPath -PathType Leaf) | |
{ | |
try | |
{ | |
$originalDatabaseNameFromBackupFile = Get-DatabaseNameFromBackupFile -DatabaseServer $dbUpgradeInfo.SP2013TargetSQLInstance -BackupFilePath $dbUpgradeInfo.UpgradeDatabaseUNCPath | |
Write-Message "`t`t- Restoring $originalDatabaseNameFromBackupFile" | |
Restore-Database -DatabaseServer $dbUpgradeInfo.SP2013TargetSQLInstance -BackupFilePath $dbUpgradeInfo.UpgradeDatabaseUNCPath -Verbose | |
} | |
catch | |
{ | |
Write-Message "`t`tError: Database Restore Failed." -ForegroundColor Red | |
Write-Message "`t`tDetails: $($_.Exception.Message)" -ForegroundColor Red | |
Send-EmailNotification -Subject "Fatal Error: Executing Database Restore" -Body "See attached log for exception details." -AttachConsoleLog | |
if($databaseUpgradeInfo.Count -gt 1) | |
{ | |
# remove this object from the list of databases to process | |
$databaseUpgradeInfo = {$databaseUpgradeInfo}.Invoke() | |
$databaseUpgradeInfo.Remove($dbUpgradeInfo) | |
$databaseUpgradeInfo | Set-Variable databaseUpgradeInfo | |
return | |
} | |
# no other databases to process, bail out | |
exit | |
} | |
} | |
else | |
{ | |
Write-Message "`t`tError: Backup File not found." -ForegroundColor Red | |
Write-Message "`t`tDetails: File Name '$($dbUpgradeInfo.UpgradeDatabaseUNCPath)'" -ForegroundColor Red | |
Send-EmailNotification -Subject "Fatal Error: Executing Database Restore" -Body "See attached log for exception details." -AttachConsoleLog | |
if($databaseUpgradeInfo.Count -gt 1) | |
{ | |
# remove this object from the list of databases to process | |
$databaseUpgradeInfo = {$databaseUpgradeInfo}.Invoke() | |
$databaseUpgradeInfo.Remove($dbUpgradeInfo) | |
$databaseUpgradeInfo | Set-Variable databaseUpgradeInfo | |
return | |
} | |
# no other databases to process, bail out | |
exit | |
} | |
############################################################################################################ | |
# Rename Database | |
############################################################################################################ | |
if($originalDatabaseNameFromBackupFile -ne $_.SP2013ContentDatabaseName) | |
{ | |
try | |
{ | |
# if the 2010 and 2013 database names are different, we need to rename the database | |
Write-Message "`t`t- Renaming $originalDatabaseNameFromBackupFile to $($dbUpgradeInfo.SP2013ContentDatabaseName)" | |
Rename-Database ` | |
-DatabaseName $originalDatabaseNameFromBackupFile ` | |
-DatabaseServer $dbUpgradeInfo.SP2013TargetSQLInstance ` | |
-NewDatabaseName $dbUpgradeInfo.SP2013ContentDatabaseName | |
} | |
catch | |
{ | |
Write-Message "`t`tError: Error renaming database $originalDatabaseNameFromBackupFile." -ForegroundColor Red | |
Write-Message "`t`tDetails: $($_.Exception.Message)" -ForegroundColor Red | |
Send-EmailNotification -Subject "Fatal Error: Executing Database Rename" -Body "See attached log for exception details." -AttachConsoleLog | |
if($databaseUpgradeInfo.Count -gt 1) | |
{ | |
# remove this object from the list of databases to process | |
$databaseUpgradeInfo = {$databaseUpgradeInfo}.Invoke() | |
$databaseUpgradeInfo.Remove($dbUpgradeInfo) | |
$databaseUpgradeInfo | Set-Variable databaseUpgradeInfo | |
return | |
} | |
# no other databases to process, bail out | |
exit | |
} | |
} | |
############################################################################################################ | |
# Set Autogrowth Settings | |
############################################################################################################ | |
if($dbUpgradeInfo.DataFileGrowthIncrement -and $dbUpgradeInfo.LogFileGrowthIncrement) | |
{ | |
try | |
{ | |
Write-Message "`t`t- Applying Autogrowth Settings" | |
Set-DatabaseAutogrowth ` | |
-DatabaseName $dbUpgradeInfo.SP2013ContentDatabaseName ` | |
-DatabaseServer $dbUpgradeInfo.SP2013TargetSQLInstance ` | |
-DataFileGrowthIncrement $dbUpgradeInfo.DataFileGrowthIncrement ` | |
-LogFileGrowthIncrement $dbUpgradeInfo.LogFileGrowthIncrement | |
} | |
catch | |
{ | |
Write-Message "`t`tError: Error applying Autogrowth settings to database: $($dbUpgradeInfo.SP2013ContentDatabaseName)" -ForegroundColor Red | |
Write-Message "`t`tDetails: $($_.Exception.Message)" -ForegroundColor Red | |
} | |
} | |
############################################################################################################ | |
# Update the Database Owner to the 2013 Farm Account | |
############################################################################################################ | |
try | |
{ | |
Write-Message "`t`t- Updating dbo login to the farm account" | |
Set-DatabaseOwner ` | |
-DatabaseName $dbUpgradeInfo.SP2013ContentDatabaseName ` | |
-DatabaseServer $dbUpgradeInfo.SP2013TargetSQLInstance ` | |
-OwnerLogin (Get-SPFarm).TimerService.ProcessIdentity.Username | |
} | |
catch | |
{ | |
Write-Message "`t`tError: Error updating the dbo login for database: $($dbUpgradeInfo.SP2013ContentDatabaseName)." -ForegroundColor Red | |
Write-Message "`t`tDetails: $($_.Exception)" -ForegroundColor Red | |
} | |
############################################################################################################ | |
# Remove legacy 2010 Permissions | |
############################################################################################################ | |
try | |
{ | |
Write-Message "`t`t- Removing Legacy Permissions" | |
Remove-CustomDatabasePermissions ` | |
-DatabaseName $dbUpgradeInfo.SP2013ContentDatabaseName ` | |
-DatabaseServer $dbUpgradeInfo.SP2013TargetSQLInstance | |
} | |
catch | |
{ | |
Write-Message "`t`tError: Error removing permission from database: $($dbUpgradeInfo.SP2013ContentDatabaseName)." -ForegroundColor Red | |
Write-Message "`t`tDetails: $($_.Exception)" -ForegroundColor Red | |
} | |
############################################################################################################ | |
# Update the Database Comptablity Level to the highest level | |
############################################################################################################ | |
try | |
{ | |
Write-Message "`t`t- Updating Compatability Level" | |
Set-DatabaseComptablityLevel ` | |
-DatabaseName $dbUpgradeInfo.SP2013ContentDatabaseName ` | |
-DatabaseServer $dbUpgradeInfo.SP2013TargetSQLInstance | |
} | |
catch | |
{ | |
Write-Message "`t`tError: Error updating compatablity level for database: $($dbUpgradeInfo.SP2013ContentDatabaseName) ." -ForegroundColor Red | |
Write-Message "`t`tDetails: $($_.Exception.Message)" -ForegroundColor Red | |
} | |
} | |
} | |
catch | |
{ | |
Write-Message "`t`tError: Error restoring and configuring database." -ForegroundColor Red | |
Write-Message "`t`tDetails: $($_.Exception.Message)" -ForegroundColor Red | |
Send-EmailNotification -Subject "Fatal Error: Executing Database Restore and Config" -Body "See attached log for exception details." -AttachConsoleLog | |
# bail out since we don't handle this case specifically | |
exit | |
} | |
############################################################################################################ | |
# Get a list of all the managed paths in the farm prior to mounting the database | |
############################################################################################################ | |
$originalManagedPaths = @{} | |
$databaseUpgradeInfo | % { | |
if(-not $originalManagedPaths.ContainsKey($_.WebApplicationUrl)) | |
{ | |
$webAppManagedPaths = Get-SPManagedPath -WebApplication $_.WebApplicationUrl | |
$originalManagedPaths.Add($_.WebApplicationUrl, $webAppManagedPaths) | |
} | |
} | |
############################################################################################################ | |
# Mounting the content databases to the SharePoint 2013 web application | |
############################################################################################################ | |
Write-Step -CurrentStep $currentStep -TotalSteps $totalSteps -Message "Upgrading Content Databases" -Color Green | |
$currentStep++ | |
try | |
{ | |
$databaseUpgradeResults = $upgradeJobCollection = @() | |
# create the run space | |
$runspacePool = New-RunspacePool -MaxRunspaces $maxContentDatabaseUpgradeThreads | |
# enumerate all the content databases to upgrade | |
$databaseUpgradeInfo | % { | |
$newDatabaseName = $_.SP2013ContentDatabaseName | |
$sqlInstance = $_.SP2013TargetSQLInstance | |
$webApplicationUrl = $_.WebApplicationUrl | |
# create the mount content database job | |
$contentDatabaseUpgradeJob = New-ContentDatabaseUpgradeJob ` | |
-DatabaseName $newDatabaseName ` | |
-DatabaseServer $sqlInstance ` | |
-WebApplicationUrl $webApplicationUrl ` | |
-RunspacePool $runspacePool | |
# add the job to our upgrade job list and start the job | |
$upgradeJobCollection += New-Object PSObject -Property @{ | |
PowerShell = $contentDatabaseUpgradeJob | |
Runspace = $contentDatabaseUpgradeJob.BeginInvoke() | |
ContentDatabase = $newDatabaseName | |
UpgradeJobResult = $null; | |
NeedsUpgrade = $null; | |
} | |
} | |
# wait for all the upgrade jobs to complete | |
while( $upgradeJobCollection | ? { !$_.Runspace.IsCompleted } ) | |
{ | |
$numberCompleted = $($upgradeJobCollection | ? { $_.Runspace.IsCompleted }).Count | |
if(!$numberCompleted) { $numberCompleted = 0 } | |
Write-Progress ` | |
-Activity "Content Database Upgrade" ` | |
-Status "Upgraded $numberCompleted of $($upgradeJobCollection.Count) Content Databases" ` | |
-PercentComplete $(($numberCompleted/$upgradeJobCollection.Count)*100) | |
Start-Sleep -Seconds 5 | |
} | |
Write-Progress -Activity "Content Database Upgrade" -Completed | |
# collect the job results | |
$upgradeJobCollection | % { $_.UpgradeJobResult = $_.PowerShell.EndInvoke($_.Runspace) } | |
# dispose of threads | |
$upgradeJobCollection | % { $_.PowerShell.Dispose() } | |
# pull the NeedsUpgrade property from the attached database | |
$upgradeJobCollection | % { $_.NeedsUpgrade = $(Get-SPContentDatabase -Identity $_.ContentDatabase).NeedsUpgrade } | |
# build an output object collection we can display and write to csv | |
$upgradeJobCollection | % { | |
$databaseUpgradeResults += New-Object PSObject -Property @{ | |
"Database Name" = $_.ContentDatabase; | |
"Needs Upgrade" = $_.NeedsUpgrade; | |
"Database Upgrade Result" = $_.UpgradeJobResult | % { $_.DatabaseUpgradeResult }; # hack for PowerShell 2.0 | |
"Database Upgrade Time" = $_.UpgradeJobResult | % { $_.DatabaseUpgradeExecutionTime }; # hack for PowerShell 2.0 | |
"Claims Upgrade Result" = $_.UpgradeJobResult | % { $_.ClaimsUpgradeResult }; # hack for PowerShell 2.0 | |
"Claims Upgrade Time" = $_.UpgradeJobResult | % { $_.ClaimsUpgradeExecutionTime }; # hack for PowerShell 2.0 | |
} | |
} | |
Write-Host "`n`nUpgrade Results:`n" -ForegroundColor Green | |
$outputFile = "ContentDatabaseUpgradeResults_{0}.csv" -f [DateTime]::Now.ToString("yyyy-MM-dd_hh-mm-ss") | |
# write out the results | |
$databaseUpgradeResults | Sort "Database Name" | FT "Database Name", "Database Upgrade Result", "Claims Upgrade Result", "Needs Upgrade" -AutoSize | |
$databaseUpgradeResults | Export-Csv -Path "$($(Get-SPDiagnosticConfig).LogLocation)\$outputFile" -NoTypeInformation -Force | |
Write-Host "`n`nUpgrade Info Log File: $($(Get-SPDiagnosticConfig).LogLocation)\$outputFile`n" | |
} | |
catch | |
{ | |
Write-Message "`t`tError: Error upgrading content database" -ForegroundColor Red | |
Write-Message "`t`tDetails: $($_.Exception.Message)" -ForegroundColor Red | |
Send-EmailNotification -Subject "Fatal Error: Error upgrading content database" -Body "See attached log for exception details." -AttachConsoleLog | |
return | |
} | |
finally | |
{ | |
if( $runspacePool -and $runspacePool.RunspacePoolStateInfo -and $runspacePool.RunspacePoolStateInfo.State -eq [System.Management.Automation.Runspaces.RunspaceState]::Opened) | |
{ | |
$runspacePool.Dispose() | |
} | |
} | |
############################################################################################################ | |
# Force a refresh of the cache of local sites | |
############################################################################################################ | |
$databaseUpgradeInfo | % { | |
Get-SPContentDatabase -Identity $_.SP2013ContentDatabaseName | % { $_.RefreshSitesInConfigurationDatabase(); Start-Sleep -Seconds 5 } | |
} | |
############################################################################################################ | |
# Delete any sites in the site recycle bin | |
############################################################################################################ | |
if($deleteUpgradedSitesInSiteRecycleBin) | |
{ | |
Write-Step -CurrentStep $currentStep -TotalSteps $totalSteps -Message "Deleting Sites from Recycle Bin" -Color Green | |
$currentStep++ | |
# enumerate all databases added to the farm and deleted all the sites in the recycle bin | |
$databaseUpgradeInfo | % { | |
Get-SPDeletedSite -ContentDatabase $_.SP2013ContentDatabaseName -Limit All -WarningAction SilentlyContinue | % { | |
$_.Delete() | |
} | |
} | |
} | |
############################################################################################################ | |
# Delete any "office Web Apps Cache" Sites in the upgraded databases, don't need those anymore | |
############################################################################################################ | |
Write-Step -CurrentStep $currentStep -TotalSteps $totalSteps -Message "Deleting Office Viewing Service Cache Sites" -Color Green | |
$currentStep++ | |
$databaseUpgradeInfo | % { | |
$databaseName = $_.SP2013ContentDatabaseName | |
Get-SPSite -ContentDatabase $databaseName -Limit All | ? { $_.Url -match "Office_Viewing_Service_Cache" } | % { | |
Write-Message "`t`t- Deleting $($_.Url)" | |
$_ | Remove-SPSite -Confirm:$false -GradualDelete:$false | |
} | |
} | |
############################################################################################################ | |
# Set the warning and max site limits (+1 to to prevent reaname from failing later in the script) | |
############################################################################################################ | |
Write-Step -CurrentStep $currentStep -TotalSteps $totalSteps -Message "Updating Database Site Count Settings" -Color Green | |
$currentStep++ | |
$databaseUpgradeInfo | % { | |
if(![string]::IsNullOrEmpty($_.MaxSiteCount) -and ![string]::IsNullOrEmpty($_.WarningSiteCount) -and $_.MaxSiteCount -gt 0) | |
{ | |
$contentDatabase = Get-SPContentDatabase -Identity $_.SP2013ContentDatabaseName | |
$maxSiteCount = $_.MaxSiteCount | |
$warningSiteCount = $_.WarningSiteCount | |
# if there are more sites in the db then the requested cap, set the cap to the number of current sites | |
if($contentDatabase.Sites.Count -gt $maxSiteCount) | |
{ | |
$maxSiteCount = $contentDatabase.Sites.Count | |
$warningSiteCount = $maxSiteCount - 1 | |
} | |
Write-Message "`t`t- Updating Max and Warning Site Count on $($_.SP2013ContentDatabaseName)" | |
$contentDatabase | Set-SPContentDatabase -MaxSiteCount $($maxSiteCount +1) -WarningSiteCount $warningSiteCount | |
} | |
else | |
{ | |
Write-Message "`t`t$($_.SP2013ContentDatabaseName) site count settings are invalid" -ForegroundColor Yellow | |
} | |
} | |
############################################################################################################ | |
# Upgrading the sites to the 2013 UI (Optional) | |
############################################################################################################ | |
if($upgradeSitesToSharePoint2013UI) | |
{ | |
Write-Step -CurrentStep $currentStep -TotalSteps $totalSteps -Message "Upgrading Site Collections" -Color Green | |
$currentStep++ | |
$runspacePool = New-RunspacePool -MaxRunspaces $maxSiteCollectionUIUpgradeThreads | |
$upgradeJobCollection = @() | |
$databaseUpgradeInfo | % { | |
$databaseName = $_.SP2013ContentDatabaseName | |
# increase the concurrent site upgrade session limit on the content database to the larger value between | |
# the current default limit or the max number of upgrade threads. | |
$contentDatabase = Get-SPContentDatabase $databaseName | |
$contentDatabase.ConcurrentSiteUpgradeSessionLimit = ( $contentDatabase.ConcurrentSiteUpgradeSessionLimit, $maxSiteCollectionUIUpgradeThreads | Measure -Maximum ).Maximum | |
# enumerate all the sites in the database and upgrade them to the 2013 look and feel | |
Get-SPSite -ContentDatabase $databaseName -Limit All | % { | |
try | |
{ | |
$site = $_ | |
# create the site upgrade job | |
$siteCollectionVersionUpgradeJob = New-SiteCollectionVersionUpgradeJob -SiteId $site.Id -RunspacePool $runspacePool | |
# add the job to our upgrade job list and start the job | |
$upgradeJobCollection += New-Object PSObject -Property @{ | |
PowerShell = $siteCollectionVersionUpgradeJob; | |
Runspace = $siteCollectionVersionUpgradeJob.BeginInvoke(); | |
SiteUrl = $site.Url; | |
CompatibilityLevel = $site.CompatibilityLevel; | |
UpgradeJobResult = $null; | |
StartTime = ""; | |
EndTime = ""; | |
} | |
} | |
finally | |
{ | |
$site.Dispose() | |
} | |
} | |
} | |
# wait for all the upgrade jobs to complete | |
while( $upgradeJobCollection | ? { -not $_.Runspace.IsCompleted } ) | |
{ | |
$numberCompleted = $($upgradeJobCollection | ? { $_.Runspace.IsCompleted }).Count | |
if(-not $numberCompleted) { $numberCompleted = 0 } | |
Write-Progress ` | |
-Activity "Site Collection Upgrade" ` | |
-Status "Upgraded $numberCompleted of $($upgradeJobCollection.Count) Site Collections" ` | |
-PercentComplete $(($numberCompleted/$upgradeJobCollection.Count)*100) | |
Start-Sleep -Seconds 1 | |
} | |
Write-Progress -Activity "Site Collection Upgrade" -Completed | |
# reset the default count for maxmium number of concurrent site upgrade session per content database. | |
$databaseUpgradeInfo | % { | |
Get-SPContentDatabase -Identity $_.SP2013ContentDatabaseName | % { $_.ConcurrentSiteUpgradeSessionLimit = 10 } | |
} | |
# collect the job results | |
$upgradeJobCollection | % { $_.UpgradeJobResult = $_.PowerShell.EndInvoke($_.Runspace) } | |
# dispose of used threads | |
$upgradeJobCollection | % { $_.PowerShell.Dispose() } | |
# build an output object collection for display purposes | |
$upgradeJobCollection | % { | |
$databaseUpgradeResults += New-Object PSObject -Property @{ | |
"Site Url" = $_.UpgradeJobResult.SiteUrl; | |
"Errors" = $_.UpgradeJobResult.Errors; | |
"Warnings" = $_.UpgradeJobResult.Warnings; | |
"Log File" = $_.UpgradeJobResult.LogFile; | |
"Error File" = $_.UpgradeJobResult.ErrorFile; | |
"Execution Time" = $_.UpgradeJobResult.ExecutionTime; | |
"Compatibility Level" = $_.UpgradeJobResult.CompatibilityLevel | |
} | |
} | |
# dump the results to the screen | |
$databaseUpgradeResults | FT "Site Url", "Compatibility Level", "Execution Time", Errors, Warnings -AutoSize | |
} | |
############################################################################################################ | |
# Delete all the items from both the admin and end user recycle bin | |
############################################################################################################ | |
Write-Step -CurrentStep $currentStep -TotalSteps $totalSteps -Message "Purging Recyle Bin Items" -Color Green | |
$currentStep++ | |
$databaseUpgradeInfo | % { | |
$databaseName = $_.SP2013ContentDatabaseName | |
# backup each site collection in the database to local disk | |
Get-SPSite -ContentDatabase $databaseName -Limit All | % { | |
# Delete all the items from the first stage recycle bin | |
$_ | Get-SPWeb -Limit All | % { | |
$_.RecycleBin.DeleteAll() | |
$_.Dispose() | |
} | |
# delete all the items from the second stage recycle bin | |
$_.RecycleBin.DeleteAll(); | |
$_.Dispose() | |
} | |
} | |
############################################################################################################ | |
# Converting site collections to HNSC | |
############################################################################################################ | |
if(-not [string]::IsNullOrEmpty( $($databaseUpgradeInfo | SELECT -Unique RootHNSCUrl).RootHNSCUrl) ) | |
{ | |
Write-Step -CurrentStep $currentStep -TotalSteps $totalSteps -Message "Renaming Sites to HNSCs" -Color Green | |
$currentStep++ | |
$databaseUpgradeInfo | % { | |
$databaseName = $_.SP2013ContentDatabaseName | |
$sqlInstance = $_.SP2013TargetSQLInstance | |
$webApplicationUrl = $_.WebApplicationUrl | |
$HNSCRootUrl = $_.RootHNSCUrl | |
# backup each site collection in the database to local disk | |
Get-SPSite -ContentDatabase $databaseName -Limit All | % { | |
$site = Get-SPSite -Identity $_.Url | |
$ErrorActionPreference = "Stop" | |
try | |
{ | |
$serverRelativeUrl = $site.ServerRelativeUrl | |
$relativeUri = New-Object System.Uri($serverRelativeUrl, [System.UriKind]::Relative) | |
# remove any URL suffixes supplied | |
$siteUrlSuffixesToRemove | % { | |
$serverRelativeUrl = $serverRelativeUrl.Replace($_, "") | |
} | |
if(-not [string]::IsNullOrEmpty($moveAllSitesUnderTheFollowingManagedPath)) | |
{ | |
$slashCount = 0 | |
# this will tell us if the sites has custom managed path with multiple levels (/departments/americas/finance, /departments/europe/hr, etc.) | |
foreach($char in $site.ServerRelativeUrl.ToCharArray()) { if($char -eq "/") { $slashCount++} } | |
if($slashCount -gt 2 -or ($_.ServerRelativeUrl -ne "/" -and -not $site.ServerRelativeUrl.StartsWith("/$moveAllSitesUnderTheFollowingManagedPath") -and $site.RootWeb.WebTemplate -ne "SPSPERS")) | |
{ | |
# 2010 relative url | |
$origServerRelativeUrl = $site.ServerRelativeUrl | |
# in case the custom managed path started with /sites/ | |
$newServerRelativeUrl = $origServerRelativeUrl.Replace("/sites/", "") | |
# replace all slashes with dashes | |
$newServerRelativeUrl = $newServerRelativeUrl.TrimStart("/").Replace("/", "-") | |
# build the new relative URL | |
$newServerRelativeUrl = "/$moveAllSitesUnderTheFollowingManagedPath/$newServerRelativeUrl" # prepend the sites managed path to the relative url | |
# before == after | |
# /departments/americas/finance == /sites/departments-americas-finance | |
# /sites/departments/americas/finance == /sites/departments-americas-finance | |
$relativeUri = New-Object System.Uri($newServerRelativeUrl, [System.UriKind]::Relative) | |
} | |
} | |
$baseUri = New-Object System.Uri($HNSCRootUrl) | |
$hnscUri = New-object System.Uri($baseUri, $relativeUri) | |
$hnscUri = Get-UniqueSiteCollectionUrl -Uri $hnscUri | |
# if the farm is patched to the Feb 2014 CU, we can use the new SPSite.Rename(uri) option | |
$farm = Get-SPFarm | |
if( $farm.BuildVersion.Major -g 16 -or ($farm.BuildVersion.Major -eq 15 -and $farm.BuildVersion.Build -ge 4693)) | |
{ | |
# The Feb 2015 CU introducted the ablity to rename a site to a host header | |
Write-Message "`t`t- Renaming $($site.Url)" | |
Rename-SiteCollection -Site $site -NewUri $hnscUri | |
} | |
else | |
{ | |
# farm is not patched to the Feb 2014 CU, we need to do the old backup/delete/restore method | |
# create a random file name to use for the temp backup file | |
$randomFileName = [system.io.path]::GetRandomFileName() | |
# full path to backup file name | |
$backupFilePath = [System.IO.Path]::Combine($localPathToStageBackupFiles, $randomFileName) | |
# backup the site | |
Write-Message "`t`t- Backing Up $($site.Url)" | |
$site | Backup-SPSite -Path $backupFilePath -Force -ErrorVariable siteBackupErrors | |
# delete the site | |
Write-Message "`t`t- Deleting $($site.Url)" | |
$site | Remove-SPSite -Confirm:$false -GradualDelete:$false | |
# restore the site | |
Write-Message "`t`t- Restoring $($hnscUri.ToString())" | |
Restore-SPSite -Identity $hnscUri.ToString() -Path $backupFilePath -HostHeaderWebApplication $webApplicationUrl -ContentDatabase $databaseName -Confirm:$false | |
# delete the temporary backup file name | |
Remove-Item $backupFilePath -Force -ErrorAction SilentlyContinue | |
} | |
} | |
catch | |
{ | |
Write-Message "`t`tAn error occurred converting site collections to HNSC." -ForegroundColor Red | |
Write-Message "`t`tError: $($error[0].ToString())." -ForegroundColor Red | |
#Send-EmailNotification -Subject "Fatal Error: Error upgrading content database" -Body "See attached log for exception details." -AttachConsoleLog | |
#exit | |
} | |
finally | |
{ | |
$site.Dispose() | |
$ErrorActionPreference = "Continue" | |
} | |
} | |
} | |
} | |
############################################################################################################ | |
# Set the warning and max site limits | |
############################################################################################################ | |
Write-Step -CurrentStep $currentStep -TotalSteps $totalSteps -Message "Updating Database Site Count Settings" -Color Green | |
$currentStep++ | |
$databaseUpgradeInfo | % { | |
if(![string]::IsNullOrEmpty($_.MaxSiteCount) -and ![string]::IsNullOrEmpty($_.WarningSiteCount) -and $_.MaxSiteCount -gt 0) | |
{ | |
$contentDatabase = Get-SPContentDatabase -Identity $_.SP2013ContentDatabaseName | |
$maxSiteCount = $_.MaxSiteCount | |
$warningSiteCount = $_.WarningSiteCount | |
# if there are more sites in the db then the requested cap, set the cap to the number of current sites | |
if($contentDatabase.Sites.Count -gt $maxSiteCount) | |
{ | |
$maxSiteCount = $contentDatabase.Sites.Count | |
$warningSiteCount = $maxSiteCount - 1 | |
} | |
Write-Message "`t`t- Updating Max and Warning Site Count on $($_.SP2013ContentDatabaseName)" | |
$contentDatabase | Set-SPContentDatabase -MaxSiteCount $maxSiteCount -WarningSiteCount $warningSiteCount | |
} | |
else | |
{ | |
Write-Message "`t`t$($_.SP2013ContentDatabaseName) site count settings are invalid" -ForegroundColor Yellow | |
} | |
} | |
############################################################################################################ | |
# Grant Data Access role on the newly added content databases | |
############################################################################################################ | |
Write-Step -CurrentStep $currentStep -TotalSteps $totalSteps -Message "Granting Database Permissions to Service Accounts" -Color Green | |
$currentStep++ | |
# BI Service Applications that need perms to read from the databases | |
$serviceApplicationTypes = @( | |
"Access Services Web Service Application" | |
,"PerformancePoint Service Application" | |
,"Visio Graphics Service Application" | |
,"Excel Services Application Web Service Application" | |
,"SQL Server Reporting Services Service Application" | |
,"PowerPivot Service Application" | |
) | |
# enumerate all web apps in the list of databases we upgraded | |
$databaseUpgradeInfo | SELECT WebApplicationUrl -Unique | % { | |
# get the web app | |
Get-SPWebApplication -Identity $_.WebApplicationUrl | % { | |
$wa = $_ | |
# enumerate all the BI services application types | |
$serviceApplicationTypes | % { | |
$typeName = $_ | |
# get the service app | |
Get-SPServiceApplication | ? { $_.TypeName -eq $typeName } | % { | |
# get the app pool account running the service app | |
$processAccount = $_.ApplicationPool.ProcessAccountName | |
# grant the app pool account Data Access to all the content databases in the web app | |
$wa.GrantAccessToProcessIdentity($processAccount) | |
} | |
} | |
} | |
} | |
############################################################################################################ | |
# Add the databases to the specified Availablity Group | |
############################################################################################################ | |
Write-Step -CurrentStep $currentStep -TotalSteps $totalSteps -Message "Adding databases to Availability Group" -Color Green | |
$currentStep++ | |
# the cmdlets to work with SQL AOAG were added to SP2013 in the April 2014 CU | |
if((Get-SPFarm).BuildVersion.Build -ge 4605) | |
{ | |
# enumerate all databases added to the farm | |
$databaseUpgradeInfo | % { | |
$databaseName = $_.SP2013ContentDatabaseName | |
$availabilityGroupName = $_.AvailabilityGroupName | |
$availabilityGroupFileShare = $_.AvailabilityGroupFileShare | |
if($availabilityGroupName) | |
{ | |
Write-Message "`t`t`- Adding $databaseName" | |
Add-DatabaseToAvailabilityGroup -AGName $availabilityGroupName -DatabaseName $databaseName -FileShare $availabilityGroupFileShare | |
} | |
else | |
{ | |
Write-Message "`t`t- No Availability Groups specified." | |
} | |
} | |
} | |
else | |
{ | |
Write-Message "`t`tInsufficient farm build ($((Get-SPFarm).BuildVersion)), please manually add the databases to the SQL AOAG" -ForegroundColor Yellow | |
$currentStep++ | |
} | |
############################################################################################################ | |
# Print a summary of any sites that failed to upgrade | |
############################################################################################################ | |
$upgradeFailures = @() | |
$databaseUpgradeInfo | % { | |
$contentDatabase = Get-SPContentDatabase -Identity $_.SP2013ContentDatabaseName | |
$failedUpdgadeInfo = $contentDatabase | Get-SPSiteUpgradeSessionInfo -ShowFailed # -ShowCompleted | |
if($failedUpdgadeInfo) | |
{ | |
$failedUpdgadeInfo | % { | |
$site = Get-SPSite -Identity $_.SiteId | |
$upgradeFailures += New-Object PSObject -Property @{ | |
SiteUrl = $site.Url | |
Status = $_.Status | |
Errors = $_.Errors | |
Warnings = $_.Warnings | |
LogFile = $_.LogFile | |
ErrorFile = "$($site.Url)/$($_.ErrorFile)" | |
} | |
$site.Dispose() | |
} | |
} | |
} | |
if($upgradeFailures.Count -gt 0) | |
{ | |
Write-Message "`nThe following site collections failed to upgrade successfully." -ForegroundColor Red | |
# write the failures to the console | |
$upgradeFailures | FT SiteUrl, Status, Errors, Warnings, ErrorFile -AutoSize | |
# write the failures to the log file | |
$upgradeFailures | % { Write-Message "Failed Site: $($_.SiteUrl)`tError Count: $($_.Errors)" -LogFileOnly} | |
} | |
############################################################################################################ | |
# Email the upgrade log | |
############################################################################################################ | |
Write-Step -CurrentStep $currentStep -TotalSteps $totalSteps -Message "Emailing the Upgrade Log" -Color Green | |
$currentStep++ | |
Send-EmailNotification -Subject "Migration Complete" -Body "See attached log for execution details." -AttachConsoleLog | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<# | |
Microsoft provides programming examples for illustration only, without warranty either expressed or | |
implied, including, but not limited to, the implied warranties of merchantability and/or fitness | |
for a particular purpose. | |
This sample assumes that you are familiar with the programming language being demonstrated and the | |
tools used to create and debug procedures. Microsoft support professionals can help explain the | |
functionality of a particular procedure, but they will not modify these examples to provide added | |
functionality or construct procedures to meet your specific needs. if you have limited programming | |
experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting | |
line at (800) 936-5200. | |
---------------------------------------------------------- | |
History | |
---------------------------------------------------------- | |
04-22-2015 - Created | |
08-19-2015 - Added the ablity to use backup compression | |
08-24-2015 - Ran into an issue with messed up source logical names, so just hardcoding new logical names to make | |
sure the result is a matching name, logical name and physical | |
09-09-2015 - Replaced Join-Path with [System.IO.Path]::Combine to prevent errors creating file paths | |
https://connect.microsoft.com/PowerShell/feedback/details/779068/join-path-and-split-path-should-not-fail-if-the-path-does-not-exist-on-the-current-machine | |
09-09-2015 - Added some optional verbose logging | |
10-14-2015 - Ran into an issue with messed up source data and log file names, so just hardcoding new data and log file names to make | |
sure the result is a matching name | |
10-14-2015 - Added code the ensure SPDataAccess and SPReadOnly roles are owned by dbo | |
11-19-2015 - Fix to allow non-default sql roles (like SharePoint_Shell_Access) to be deleted as part of 2010 database permission clean up. | |
01-06-2016 - Updated to allow for parallel site UI ugprades | |
02-24-2016 - Fixed a bug that prevented renaming a database that starts with a number | |
09-08-2016 - Updated database restore timeout to a minimum of 30 seconds. | |
05-17-2019 - Updated logic to only attempt claims upgrade on 2013 farms, skip on 2016+ farms. | |
==============================================================#> | |
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue | |
$script:m_executionLogFilePath = $script:m_smtpServerName = $script:m_emailToAddress = $script:m_emailFromAddress = "" | |
$script:m_ULSCategory = New-Object Microsoft.SharePoint.Administration.SPDiagnosticsCategory("SharePoint Upgrade Script", [Microsoft.SharePoint.Administration.TraceSeverity]::Verbose, [Microsoft.SharePoint.Administration.EventSeverity]::Verbose ) | |
$script:m_DiagnosticsService = [Microsoft.SharePoint.Administration.SPDiagnosticsService]::Local | |
function New-ContentDatabaseUpgradeJob() | |
{ | |
[cmdletbinding()] | |
param( | |
[Parameter(Mandatory=$true)][string]$DatabaseName, | |
[Parameter(Mandatory=$true)][string]$DatabaseServer, | |
[Parameter(Mandatory=$true)][string]$WebApplicationUrl, | |
[Parameter(Mandatory=$true)][System.Management.Automation.Runspaces.RunspacePool]$RunspacePool | |
) | |
$ErrorActionPreference = "Stop" | |
try | |
{ | |
# create an upgrade job | |
$upgradeJob = [System.Management.Automation.PowerShell]::Create() | |
# set the runspace pool | |
$upgradeJob.RunspacePool = $RunspacePool | |
# add the script block and the parameters to execute | |
#$powershell = $upgradeJob.AddScript( $MounContentDatabaseScriptBlock2 ) | |
$powershell = $upgradeJob.AddScript( $MountContentDatabaseScriptBlock ) | |
$powershell = $upgradeJob.AddParameter( "DatabaseName", $DatabaseName.ToUpper() ) | |
$powershell = $upgradeJob.AddParameter( "DatabaseServer", $DatabaseServer ) | |
$powershell = $upgradeJob.AddParameter( "WebApplicationUrl", $WebApplicationUrl ) | |
return $upgradeJob | |
} | |
catch | |
{ | |
throw $_.Exception | |
} | |
finally | |
{ | |
$ErrorActionPreference = "Continue" | |
} | |
} | |
function New-SiteCollectionVersionUpgradeJob() | |
{ | |
[cmdletbinding()] | |
param( | |
[Parameter(Mandatory=$true)][Guid]$SiteId, | |
[Parameter(Mandatory=$true)][System.Management.Automation.Runspaces.RunspacePool]$RunspacePool | |
) | |
$ErrorActionPreference = "Stop" | |
try | |
{ | |
# create an upgrade job | |
$upgradeJob = [System.Management.Automation.PowerShell]::Create() | |
# set the runspace pool | |
$upgradeJob.RunspacePool = $RunspacePool | |
# add the script block and the parameters to execute | |
$powershell = $upgradeJob.AddScript( $SiteCollectionVersionUpgradeScriptBlock ) | |
$powershell = $upgradeJob.AddParameter( "SiteId", $SiteId ) | |
return $upgradeJob | |
} | |
catch | |
{ | |
throw $_.Exception | |
} | |
finally | |
{ | |
$ErrorActionPreference = "Continue" | |
} | |
} | |
function New-RunspacePool() | |
{ | |
[cmdletbinding()] | |
param([Parameter(Mandatory=$true)][int]$MaxRunspaces) | |
$ErrorActionPreference = "Stop" | |
try | |
{ | |
$warning = "" | |
# create a shared session state that imports the SharePoint Snap-In | |
$defaultSessionState = [System.Management.Automation.Runspaces.InitialSessionState]::CreateDefault() | |
$snapInInfo = $defaultSessionState.ImportPSSnapIn( "Microsoft.SharePoint.PowerShell", [ref]$warning ) | |
$defaultSessionState.ThreadOptions = [System.Management.Automation.Runspaces.PSThreadOptions]::UseNewThread | |
$defaultSessionState.ApartmentState = [System.Threading.ApartmentState]::MTA | |
# create the runspace pool that will be unique for all of the upgrade jobs | |
$runspacePool = [System.Management.Automation.Runspaces.RunspaceFactory]::CreateRunspacePool($defaultSessionState) | |
$added = $runspacePool.SetMinRunspaces(1) | |
$added = $runspacePool.SetMaxRunspaces($MaxRunspaces) | |
$runspacePool.Open() | |
return $runspacePool | |
} | |
catch | |
{ | |
throw $_.Exception | |
} | |
finally | |
{ | |
$ErrorActionPreference = "Continue" | |
} | |
} | |
function Get-DataTable() | |
{ | |
[cmdletbinding()] | |
param( | |
[Parameter(Mandatory=$true)][string]$DatabaseServer, | |
[Parameter(Mandatory=$true)][string]$DatabaseName, | |
[Parameter(Mandatory=$true)][string]$Query | |
) | |
$ErrorActionPreference = "Stop" | |
$connectionString = "Data Source=$DatabaseServer;Initial Catalog=$DatabaseName;Integrated Security=True;Enlist=False;Connect Timeout=5" | |
try | |
{ | |
Write-Message "$($MyInvocation.MyCommand.Name) - Executing: $Query" -LogFileOnly | |
$dataSet = New-Object System.Data.DataSet | |
$dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($Query, $connectionString) | |
$dataAdapter.Fill($dataSet) | Out-Null | |
return $dataSet.Tables[0] | |
} | |
catch | |
{ | |
Write-Message "$($MyInvocation.MyCommand.Name) - Exception: $($_.Exception.ToString())" -LogFileOnly | |
throw $_.Exception | |
} | |
finally | |
{ | |
if($dataSet) | |
{ | |
$dataSet.Dispose() | |
} | |
if($dataAdapter) | |
{ | |
$dataAdapter.Dispose() | |
} | |
$ErrorActionPreference = "Continue" | |
} | |
} | |
function Get-DatabaseFileMoveSyntax() | |
{ | |
[cmdletbinding()] | |
param( | |
[Parameter(Mandatory=$true)][object[]]$FileNameInfo, | |
[Parameter(Mandatory=$true)][string]$TargetLogFilePath, | |
[Parameter(Mandatory=$true)][string]$TargetDataFilePath | |
) | |
$ErrorActionPreference = "Stop" | |
$sqlSyntax = "" | |
try | |
{ | |
$FileNameInfo | % { | |
$physicalNameInfo = New-Object System.IO.FileInfo($_.PhysicalName) | |
$logicalFileName = $_.LogicalName | |
$filePath = "" | |
switch($_.Type) | |
{ | |
"D" # data file | |
{ | |
$filePath = [System.IO.Path]::Combine( $TargetDataFilePath, $physicalNameInfo.Name ) | |
} | |
"L" # log file | |
{ | |
$filePath = [System.IO.Path]::Combine( $TargetLogFilePath, $physicalNameInfo.Name ) | |
} | |
} | |
$sqlSyntax += "MOVE N'$logicalFileName' TO N'$filePath', " | |
} | |
return $sqlSyntax | |
} | |
catch | |
{ | |
Write-Message "Error creating database move syntax" -ForegroundColor Red | |
Write-Message "$($MyInvocation.MyCommand.Name) - Exception: $($_.Exception.ToString())" -LogFileOnly | |
throw $_.Exception | |
} | |
finally | |
{ | |
$ErrorActionPreference = "Continue" | |
} | |
} | |
function Get-DatabaseBackupFileInfo() | |
{ | |
[cmdletbinding()] | |
param( | |
[Parameter(Mandatory=$true)][string]$DatabaseServer, | |
[Parameter(Mandatory=$true)][string]$BackupFilePath | |
) | |
$ErrorActionPreference = "Stop" | |
try | |
{ | |
$query = "RESTORE FILELISTONLY FROM DISK = N'$BackupFilePath' WITH NOUNLOAD" | |
Write-Message "$($MyInvocation.MyCommand.Name) - Executing: $query" -LogFileOnly | |
$dataTable = Get-DataTable -DatabaseServer $DatabaseServer -DatabaseName "master" -Query $query | |
return $dataTable | SELECT LogicalName, PhysicalName, Type | |
} | |
catch | |
{ | |
Write-Message "$($MyInvocation.MyCommand.Name) - Exception: $($_.Exception.ToString())" -LogFileOnly | |
throw $_.Exception | |
} | |
finally | |
{ | |
$ErrorActionPreference = "Continue" | |
} | |
} | |
function Invoke-NonQuery() | |
{ | |
[cmdletbinding()] | |
param( | |
[Parameter(Mandatory=$true)][string]$DatabaseName, | |
[Parameter(Mandatory=$true)][string]$DatabaseServer, | |
[Parameter(Mandatory=$true)][string]$Query, | |
[int]$CommandTimeout=30 # The default is 30 seconds | |
) | |
$connectionString = "Data Source=$DatabaseServer;Initial Catalog=$DatabaseName;Integrated Security=True;Enlist=False;Connect Timeout=5" | |
try | |
{ | |
Write-Message "$($MyInvocation.MyCommand.Name) - Executing: $Query" -LogFileOnly | |
$ErrorActionPreference = "Stop" | |
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString) | |
$connection.Open() | |
$command = New-Object system.Data.SqlClient.SqlCommand($Query, $connection) | |
$command.CommandTimeout = $CommandTimeout | |
$command.ExecuteNonQuery() | Out-Null | |
} | |
catch | |
{ | |
Write-Message "$($MyInvocation.MyCommand.Name) - Exception: $($_.Exception.ToString())" -LogFileOnly | |
throw $_.Exception | |
} | |
finally | |
{ | |
if($connection) | |
{ | |
[System.Data.SqlClient.SqlConnection]::ClearAllPools() | |
$connection.Close() | |
$connection.Dispose() | |
} | |
} | |
} | |
function Invoke-ScalarQuery() | |
{ | |
[cmdletbinding()] | |
param( | |
[Parameter(Mandatory=$true)][string]$DatabaseName, | |
[Parameter(Mandatory=$true)][string]$DatabaseServer, | |
[Parameter(Mandatory=$true)][string]$Query, | |
[int]$CommandTimeout=30 # The default is 30 seconds | |
) | |
$ErrorActionPreference = "Stop" | |
$connectionString = "Data Source=$DatabaseServer;Initial Catalog=$DatabaseName;Integrated Security=True;Enlist=False;Connect Timeout=5" | |
try | |
{ | |
Write-Message "$($MyInvocation.MyCommand.Name) - Executing: $Query" -LogFileOnly | |
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString) | |
$connection.Open() | |
$command = New-Object system.Data.SqlClient.SqlCommand($Query, $connection) | |
$command.CommandTimeout = $CommandTimeout | |
$result = $command.ExecuteScalar() | |
return $result | |
} | |
catch | |
{ | |
Write-Message "$($MyInvocation.MyCommand.Name) - Exception: $($_.Exception.ToString())" -LogFileOnly | |
throw $_.Expception | |
} | |
finally | |
{ | |
if($connection) | |
{ | |
[System.Data.SqlClient.SqlConnection]::ClearAllPools() | |
$connection.Close() | |
$connection.Dispose() | |
} | |
$ErrorActionPreference = "Continue" | |
} | |
} | |
function Test-DatabaseName() | |
{ | |
[cmdletbinding()] | |
param( | |
[Parameter(Mandatory=$true)][string]$DatabaseName, | |
[Parameter(Mandatory=$true)][string]$DatabaseServer | |
) | |
$ErrorActionPreference = "Stop" | |
try | |
{ | |
$result = Invoke-ScalarQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query "SELECT COUNT(*) FROM sys.databases (nolock) WHERE [Name] = '$DatabaseName'" | |
return [Convert]::ToBoolean($result) | |
} | |
catch | |
{ | |
Write-Message "$($MyInvocation.MyCommand.Name) - Exception: $($_.Exception.ToString())" -LogFileOnly | |
throw $_.Exception | |
} | |
finally | |
{ | |
$ErrorActionPreference = "Continue" | |
} | |
} | |
function Get-DatabaseNameFromBackupFile() | |
{ | |
[cmdletbinding()] | |
param( | |
[Parameter(Mandatory=$true)][string]$DatabaseServer, | |
[Parameter(Mandatory=$true)][string]$BackupFilePath | |
) | |
$databaseName = $null | |
$ErrorActionPreference = "Stop" | |
try | |
{ | |
$query = "RESTORE HEADERONLY FROM DISK = N'$BackupFilePath' WITH NOUNLOAD" | |
Write-Message "$($MyInvocation.MyCommand.Name) - Executing: $Query" -LogFileOnly | |
# pull the original database name from the backup file headers | |
$dataTable = Get-DataTable -DatabaseServer $DatabaseServer -DatabaseName "master" -Query $query | |
$databaseName = $dataTable.DatabaseName | |
} | |
catch | |
{ | |
Write-Message "$($MyInvocation.MyCommand.Name) - Exception: $($_.Exception.ToString())" -LogFileOnly | |
throw $_.Exception | |
} | |
finally | |
{ | |
$ErrorActionPreference = "Continue" | |
} | |
return $databaseName | |
} | |
function Restore-Database() | |
{ | |
[cmdletbinding()] | |
param( | |
[Parameter(Mandatory=$true)][string]$DatabaseServer, | |
[Parameter(Mandatory=$true)][string]$BackupFilePath | |
) | |
$ErrorActionPreference = "Stop" | |
try | |
{ | |
# calculate a timeout value | |
$fi = New-Object System.IO.FileInfo($BackupFilePath) | |
$timeout = $fi.Length/1mb # figure worst case is 1mb/sec. Convert bytes to mb | |
if( $timeout -lt 180 ) { $timeout = 180 } # ran into issues on a very slow network, so jacking up to a crazy slow number | |
Write-Message "$($MyInvocation.MyCommand.Name) - Timeout Value: $timeout" -LogFileOnly | |
$databaseName = Get-DatabaseNameFromBackupFile -DatabaseServer $DatabaseServer -BackupFilePath $BackupFilePath | |
# if the database exists, we can't restore it | |
if(Test-DatabaseName -DatabaseName $databaseName -DatabaseServer $DatabaseServer) | |
{ | |
throw "A database with the name '$databaseName' already exists on server $DatabaseServer" | |
} | |
# get the default log file path | |
$defaultDataFilePath = Invoke-ScalarQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query "SELECT SERVERPROPERTY('INSTANCEDEFAULTDATAPATH')" | |
# get the default data file path | |
$defaultLogFilePath = Invoke-ScalarQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query "SELECT SERVERPROPERTY('INSTANCEDEFAULTLOGPATH')" | |
#$defaultDataFilePath = "E:\Program Files\Microsoft SQL Server\MSSQL11.SHAREPOINT\MSSQL\DATA2\" | |
#$defaultLogFilePath = "F:\Program Files\Microsoft SQL Server\MSSQL11.SHAREPOINT\MSSQL\Data\" | |
Write-Message "$($MyInvocation.MyCommand.Name) - $DatabaseServer Data File Path: $defaultDataFilePath" -LogFileOnly | |
Write-Message "$($MyInvocation.MyCommand.Name) - $DatabaseServer Log File Path: $defaultLogFilePath" -LogFileOnly | |
# pull the logical and physical file names from the backup file | |
$fileNameInfo = Get-DatabaseBackupFileInfo -DatabaseServer $DatabaseServer -BackupFilePath $BackupFilePath | |
# get the sql syntax required to point the data and log files to the appropriate locations | |
$fileMoveSyntax = Get-DatabaseFileMoveSyntax -FileNameInfo $fileNameInfo -TargetLogFilePath $defaultLogFilePath -TargetDataFilePath $defaultDataFilePath | |
$query = "RESTORE DATABASE [$databaseName] FROM DISK = N'$BackupFilePath' WITH RECOVERY, $fileMoveSyntax NOUNLOAD" | |
Write-Message "$($MyInvocation.MyCommand.Name) - Executing: $query" -LogFileOnly | |
Invoke-NonQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query $query -CommandTimeout $timeout | |
} | |
catch | |
{ | |
Write-Message "$($MyInvocation.MyCommand.Name) - Exception: $($_.Exception.ToString())" -LogFileOnly | |
throw $_.Exception | |
} | |
finally | |
{ | |
$ErrorActionPreference = "Continue" | |
} | |
} | |
function Set-DatabaseComptablityLevel() | |
{ | |
[cmdletbinding()] | |
param( | |
[Parameter(Mandatory=$true)][string]$DatabaseName, | |
[Parameter(Mandatory=$true)][string]$DatabaseServer, | |
[ValidateSet(100,110,120)][int]$CompatablityLevel | |
) | |
$ErrorActionPreference = "Stop" | |
try | |
{ | |
if(!$CompatablityLevel) | |
{ | |
$query = "SELECT CONVERT(INT,CAST(@@microsoftversion/ 0x1000000 AS VARCHAR(3)) + '0')" | |
Write-Message "$($MyInvocation.MyCommand.Name) - Executing: $query" -LogFileOnly | |
# query the sql instance to get the highest compatablity level possible | |
$CompatablityLevel = Invoke-ScalarQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query $query | |
} | |
$query = "ALTER DATABASE [$DatabaseName] SET COMPATIBILITY_LEVEL = $CompatablityLevel" | |
Write-Message "$($MyInvocation.MyCommand.Name) - Executing: $query" -LogFileOnly | |
Invoke-NonQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query $query | |
} | |
catch | |
{ | |
Write-Message "$($MyInvocation.MyCommand.Name) - Exception: $($_.Exception.ToString())" -LogFileOnly | |
throw $_.Exception | |
} | |
finally | |
{ | |
$ErrorActionPreference = "Continue" | |
} | |
} | |
function Set-XPCmdShell() | |
{ | |
[cmdletbinding()] | |
param( | |
[Parameter(Mandatory=$true)][string]$DatabaseServer, | |
[switch]$Enabled | |
) | |
$ErrorActionPreference = "Stop" | |
$query = "EXEC sp_configure 'show advanced options', 1; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE WITH OVERRIDE" | |
if(!$Enabled) | |
{ | |
$query = "EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'show advanced options', 0; RECONFIGURE WITH OVERRIDE" | |
} | |
try | |
{ | |
Write-Message "$($MyInvocation.MyCommand.Name) - Executing: $Query" -LogFileOnly | |
Invoke-NonQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query $query | |
} | |
catch | |
{ | |
Write-Message "$($MyInvocation.MyCommand.Name) - Exception: $($_.Exception.ToString())" -LogFileOnly | |
throw $_.Exception | |
} | |
finally | |
{ | |
$ErrorActionPreference = "Continue" | |
} | |
} | |
function Get-XPCmdShell() | |
{ | |
[cmdletbinding()] | |
param( | |
[Parameter(Mandatory=$true)][string]$DatabaseServer | |
) | |
$ErrorActionPreference = "Stop" | |
try | |
{ | |
return Invoke-ScalarQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query "SELECT Value FROM SYS.CONFIGURATIONS WHERE Name = 'xp_cmdshell'" | |
} | |
catch | |
{ | |
Write-Message "$($MyInvocation.MyCommand.Name) - Exception: $($_.Exception.ToString())" -LogFileOnly | |
throw $_.Exception | |
} | |
finally | |
{ | |
$ErrorActionPreference = "Continue" | |
} | |
} | |
function Set-DatabaseAutogrowth() | |
{ | |
[cmdletbinding()] | |
param( | |
[Parameter(Mandatory=$true)][string]$DatabaseName, | |
[Parameter(Mandatory=$true)][string]$DatabaseServer, | |
[Parameter(Mandatory=$true)][string]$DataFileGrowthIncrement, | |
[Parameter(Mandatory=$true)][string]$LogFileGrowthIncrement | |
) | |
$ErrorActionPreference = "Stop" | |
try | |
{ | |
# get the logical data file name | |
$query = "SELECT name FROM sys.master_files WHERE database_id = DB_ID(N'$DatabaseName') and type_desc = 'rows'" | |
Write-Message "$($MyInvocation.MyCommand.Name) - Executing: $Query" -LogFileOnly | |
$dataFileNameTable = Get-DataTable -DatabaseName $DatabaseName -DatabaseServer $DatabaseServer -Query $query | |
# get the logical log file name | |
$query = "SELECT name FROM sys.master_files WHERE database_id = DB_ID(N'$DatabaseName') and type_desc = 'Log'" | |
Write-Message "$($MyInvocation.MyCommand.Name) - Executing: $Query" -LogFileOnly | |
$logFileNameTable = Get-DataTable -DatabaseName $DatabaseName -DatabaseServer $DatabaseServer -Query $query | |
# update all of the data files | |
$dataFileNameTable | % { | |
$query = "ALTER DATABASE [$DatabaseName] MODIFY FILE (NAME = N'$($_.Name)', FILEGROWTH = $DataFileGrowthIncrement)" | |
Write-Message "$($MyInvocation.MyCommand.Name) - Executing: $Query" -LogFileOnly | |
Invoke-NonQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query $query | |
} | |
# update all of the log files | |
$logFileNameTable | % { | |
$query = "ALTER DATABASE [$DatabaseName] MODIFY FILE (NAME = N'$($_.Name)', FILEGROWTH = $LogFileGrowthIncrement)" | |
Write-Message "$($MyInvocation.MyCommand.Name) - Executing: $Query" -LogFileOnly | |
Invoke-NonQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query $query | |
} | |
} | |
catch | |
{ | |
Write-Message "$($MyInvocation.MyCommand.Name) - Exception: $($_.Exception.ToString())" -LogFileOnly | |
throw $_.Exception | |
} | |
finally | |
{ | |
$ErrorActionPreference = "Continue" | |
} | |
} | |
function Test-SysAdminRoleMember() | |
{ | |
[cmdletbinding()] | |
param( | |
[Parameter(Mandatory=$true)][string]$DatabaseServer | |
) | |
$ErrorActionPreference = "Stop" | |
try | |
{ | |
$isSysAdmin = Invoke-ScalarQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query "SELECT IS_SRVROLEMEMBER('sysadmin')" | |
$isServerAdmin = Invoke-ScalarQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query "SELECT IS_SRVROLEMEMBER('serveradmin')" | |
return $isSysAdmin -or $isServerAdmin | |
} | |
catch | |
{ | |
Write-Message "$($MyInvocation.MyCommand.Name) - Exception: $($_.Exception.ToString())" -LogFileOnly | |
throw $_.Exception | |
} | |
finally | |
{ | |
$ErrorActionPreference = "Continue" | |
} | |
} | |
function Rename-Database() | |
{ | |
[cmdletbinding()] | |
param( | |
[Parameter(Mandatory=$true)][string]$DatabaseName, | |
[Parameter(Mandatory=$true)][string]$DatabaseServer, | |
[Parameter(Mandatory=$true)][string]$NewDatabaseName, | |
[Parameter()][System.Management.Automation.PSCredential]$Credential | |
) | |
$ErrorActionPreference = "Stop" | |
try | |
{ | |
# Set database into single user mode | |
$query = "ALTER DATABASE [$DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE" | |
Write-Message "$($MyInvocation.MyCommand.Name) - Executing: $query" -LogFileOnly | |
Invoke-NonQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query $query | |
# rename the database | |
$query = "ALTER DATABASE [$DatabaseName] MODIFY NAME = [$NewDatabaseName]" | |
Write-Message "$($MyInvocation.MyCommand.Name) - Executing: $query" -LogFileOnly | |
Invoke-NonQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query $query | |
# get the logical file name for the data file | |
$query = "SELECT name FROM sys.master_files WHERE database_id = DB_ID(N'$NewDatabaseName') and type_desc = 'rows'" | |
$dataFileLogicalName = Invoke-ScalarQuery -DatabaseName $NewDatabaseName -DatabaseServer $DatabaseServer -Query $query | |
$newDataFileLogicalName = $dataFileLogicalName.Replace($DatabaseName, $NewDatabaseName) | |
# get the logical file name for the data file | |
$query = "SELECT name FROM sys.master_files WHERE database_id = DB_ID(N'$NewDatabaseName') and type_desc = 'log'" | |
$logFileLogicalName = Invoke-ScalarQuery -DatabaseName $NewDatabaseName -DatabaseServer $DatabaseServer -Query $query | |
$newLogFileLogicalName = $logFileLogicalName.Replace($DatabaseName, $NewDatabaseName) | |
# 08/24/2015: Ran into an issue with messed up source logical names, so just hardcoding new logical names to make | |
# sure the result is a matching name, logical name and physical | |
$newDataFileLogicalName = $NewDatabaseName | |
$newLogFileLogicalName = "$($NewDatabaseName)_log" | |
# rename the logical data file name | |
$query = "ALTER DATABASE [$NewDatabaseName] MODIFY FILE (NAME=N'$dataFileLogicalName', NEWNAME=N'$newDataFileLogicalName')" | |
Write-Message "$($MyInvocation.MyCommand.Name) - Executing: $query" -LogFileOnly | |
Invoke-NonQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query $query | |
# rename the logical log file name | |
$query = "ALTER DATABASE [$NewDatabaseName] MODIFY FILE (NAME=N'$logFileLogicalName', NEWNAME=N'$newLogFileLogicalName')" | |
Write-Message "$($MyInvocation.MyCommand.Name) - Executing: $query" -LogFileOnly | |
Invoke-NonQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query $query | |
# get the physical file name for the data file | |
$query = "SELECT physical_name FROM sys.master_files WHERE database_id = DB_ID(N'$NewDatabaseName') and type_desc = 'rows'" | |
$dataFilePhysicalName = Invoke-ScalarQuery -DatabaseName $NewDatabaseName -DatabaseServer $DatabaseServer -Query $query | |
# get the physical file name for the log file | |
$query = "SELECT physical_name FROM sys.master_files WHERE database_id = DB_ID(N'$NewDatabaseName') and type_desc = 'log'" | |
$logFilePhysicalName = Invoke-ScalarQuery -DatabaseName $NewDatabaseName -DatabaseServer $DatabaseServer -Query $query | |
# build the new data file path | |
$dataFileInfo = New-Object System.IO.FileInfo $dataFilePhysicalName | |
$oldDataFileFullName = $dataFileInfo.FullName | |
$oldDataFileName = $dataFileInfo.Name | |
$oldDataBaseName = $dataFileInfo.BaseName | |
#$newDataFileName = $oldDataFileName.Replace($DatabaseName, $NewDatabaseName) | |
#10/14/2015: hack to fix an issue where the legacy data file name does not match the database name, causing the rename not to happen | |
$newDataFileName = "$NewDatabaseName.mdf" | |
$newDataFilePath = [System.IO.Path]::Combine( $dataFileInfo.DirectoryName, $newDataFileName ) | |
# build the new log file path | |
$logFileInfo = New-Object System.IO.FileInfo $logFilePhysicalName | |
$oldLogFileFullName = $logFileInfo.FullName | |
$oldLogFileName = $logFileInfo.Name | |
$oldLogBaseName = $dataFileInfo.BaseName | |
#$newLogFileName = $oldLogFileName.Replace($DatabaseName, $NewDatabaseName) | |
#10/14/2015: hack to fix an issue where the legacy log file name does not match the database name, causing the rename not to happen | |
$newLogFileName = "$NewDatabaseName.ldf" | |
$newLogFilePath = [System.IO.Path]::Combine( $logFileInfo.DirectoryName, $newLogFileName ) | |
# set the database to offline, so we can rename the database files | |
$query = "ALTER DATABASE [$NewDatabaseName] SET OFFLINE" | |
Write-Message "$($MyInvocation.MyCommand.Name) - Executing: $query" -LogFileOnly | |
Invoke-NonQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query $query | |
$useXPCmdShell = $true | |
$disableXPCmdShell = $false | |
if($useXPCmdShell) | |
{ | |
try | |
{ | |
if(!(Get-XPCmdShell -DatabaseServer $DatabaseServer)) | |
{ | |
$disableXPCmdShell = $true | |
Set-XPCmdShell -DatabaseServer $DatabaseServer -Enabled | |
} | |
$query = "xp_cmdshell 'RENAME `"$oldDataFileFullName`" `"$newDataFileName`"'" | |
Write-Message "$($MyInvocation.MyCommand.Name) - Executing: $query" -LogFileOnly | |
Invoke-NonQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query $query | |
$query = "xp_cmdshell 'RENAME `"$oldLogFileFullName`" `"$newLogFileName`"'" | |
Write-Message "$($MyInvocation.MyCommand.Name) - Executing: $query" -LogFileOnly | |
Invoke-NonQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query $query | |
} | |
finally | |
{ | |
if($disableXPCmdShell) | |
{ | |
Set-XPCmdShell -DatabaseServer $DatabaseServer | |
} | |
} | |
} | |
else | |
{ | |
# Rename the files on the remote SQL server using PowerShell Remoting | |
Invoke-RemotePowerShell -ComputerName $DatabaseServer -Credential $Credential -ScriptBlock {param([string]$Path, [string]$NewName ) Rename-Item -Path $Path -NewName $NewName } -ArgumentList $oldDataFileFullName, $newDataFileName | |
Invoke-RemotePowerShell -ComputerName $DatabaseServer -Credential $Credential -ScriptBlock {param([string]$Path, [string]$NewName ) Rename-Item -Path $Path -NewName $NewName } -ArgumentList $oldLogFileFullName, $newLogFileName | |
} | |
# rename the physical name of the data file | |
$query = "ALTER DATABASE [$NewDatabaseName] MODIFY FILE (NAME='$newDataFileLogicalName', FILENAME=N'$newDataFilePath')" | |
Write-Message "$($MyInvocation.MyCommand.Name) - Executing: $query" -LogFileOnly | |
Invoke-NonQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query $query | |
# rename the physical name of the log file | |
$query = "ALTER DATABASE [$NewDatabaseName] MODIFY FILE (NAME='$newLogFileLogicalName', FILENAME=N'$newLogFilePath')" | |
Write-Message "$($MyInvocation.MyCommand.Name) - Executing: $query" -LogFileOnly | |
Invoke-NonQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query $query | |
# set the database to ONLINE | |
$query = "ALTER DATABASE [$NewDatabaseName] SET ONLINE" | |
Write-Message "$($MyInvocation.MyCommand.Name) - Executing: $query" -LogFileOnly | |
Invoke-NonQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query $query | |
$query = "ALTER DATABASE [$NewDatabaseName] SET MULTI_USER WITH ROLLBACK IMMEDIATE" | |
Write-Message "$($MyInvocation.MyCommand.Name) - Executing: $query" -LogFileOnly | |
Invoke-NonQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query $query | |
} | |
catch | |
{ | |
Write-Message "$($MyInvocation.MyCommand.Name) - Exception: $($_.Exception.ToString())" -LogFileOnly | |
throw $_.Exception | |
} | |
finally | |
{ | |
$ErrorActionPreference = "Continue" | |
} | |
} | |
function Update-SharePointDatabaseRoleOwner() | |
{ | |
[cmdletbinding()] | |
param( | |
[Parameter(Mandatory=$true)][string]$DatabaseName, | |
[Parameter(Mandatory=$true)][string]$DatabaseServer, | |
[Parameter(Mandatory=$true)][string]$Role, | |
[Parameter(Mandatory=$true)][string]$Owner | |
) | |
$ErrorActionPreference = "Stop" | |
try | |
{ | |
$query = "IF EXISTS ( SELECT * FROM sys.database_principals WHERE name = N'$Role' AND type = 'R' ) BEGIN ALTER AUTHORIZATION ON ROLE::$Role TO $Owner; END" | |
Write-Message "$($MyInvocation.MyCommand.Name) - Executing $query" -LogFileOnly | |
Invoke-ScalarQuery -DatabaseName $DatabaseName -DatabaseServer $DatabaseServer -Query $query | |
} | |
catch | |
{ | |
Write-Message "$($MyInvocation.MyCommand.Name) - Exception: $($_.Exception.ToString())" -LogFileOnly | |
throw $_.Exception | |
} | |
finally | |
{ | |
$ErrorActionPreference = "Continue" | |
} | |
} | |
function Remove-NonDefaultDatabaseSchemas() | |
{ | |
[cmdletbinding()] | |
param( | |
[Parameter(Mandatory=$true)][string]$DatabaseName, | |
[Parameter(Mandatory=$true)][string]$DatabaseServer | |
) | |
$ErrorActionPreference = "Stop" | |
try | |
{ | |
$query = "SELECT [name] FROM sys.schemas WHERE [name] NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys') AND NAME NOT LIKE 'db_%' AND NAME NOT LIKE 'SP%'" | |
$dataTable = Get-DataTable -DatabaseServer $DatabaseServer -DatabaseName $DatabaseName -Query $query | |
$dataTable | % { | |
$query = "DROP SCHEMA [$($_.Name)]" | |
Write-Message "$(Get-Date): $($MyInvocation.MyCommand.Name) - Executing: $query" -LogFileOnly | |
Invoke-ScalarQuery -DatabaseName $DatabaseName -DatabaseServer $DatabaseServer -Query $query | |
} | |
} | |
catch | |
{ | |
Write-Message "$($MyInvocation.MyCommand.Name) - Exception: $($_.Exception.ToString())" -LogFileOnly | |
throw $_.Exception | |
} | |
finally | |
{ | |
$ErrorActionPreference = "Continue" | |
} | |
} | |
function Remove-NonDefaultDatabaseUsers() | |
{ | |
[cmdletbinding()] | |
param( | |
[Parameter(Mandatory=$true)][string]$DatabaseName, | |
[Parameter(Mandatory=$true)][string]$DatabaseServer | |
) | |
$ErrorActionPreference = "Stop" | |
try | |
{ | |
$query = "SELECT [name], [issqlrole] FROM sys.sysusers WHERE [name] NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys', 'public') AND NAME NOT LIKE 'db_%' AND NAME NOT LIKE 'SP%'" | |
$dataTable = Get-DataTable -DatabaseServer $DatabaseServer -DatabaseName $DatabaseName -Query $query | |
$dataTable | % { | |
# bug fix: 11-19-2015 - fix to allow non-default sql roles to be deleted as part of database clean up. | |
if($($_.issqlrole) -eq "1" ) | |
{ | |
# delete all the users from the role | |
$query = " | |
DECLARE @RoleName sysname | |
SET @RoleName = N'$($_.Name)' | |
IF @RoleName <> N'public' and (select is_fixed_role from sys.database_principals where name = @RoleName) = 0 | |
BEGIN | |
DECLARE @RoleMemberName sysname | |
DECLARE Member_Cursor CURSOR FOR | |
SELECT [name] | |
FROM sys.database_principals | |
WHERE principal_id in ( | |
SELECT member_principal_id | |
FROM sys.database_role_members | |
WHERE role_principal_id in ( | |
SELECT principal_id | |
FROM sys.database_principals where [name] = @RoleName AND type = 'R')) | |
OPEN Member_Cursor; | |
FETCH NEXT FROM Member_Cursor | |
into @RoleMemberName | |
DECLARE @SQL NVARCHAR(4000) | |
WHILE @@FETCH_STATUS = 0 | |
BEGIN | |
SET @SQL = 'ALTER ROLE '+ QUOTENAME(@RoleName,'[') +' DROP MEMBER '+ QUOTENAME(@RoleMemberName,'[') | |
EXEC(@SQL) | |
FETCH NEXT FROM Member_Cursor | |
into @RoleMemberName | |
END; | |
CLOSE Member_Cursor; | |
DEALLOCATE Member_Cursor; | |
END" | |
Write-Message "$($MyInvocation.MyCommand.Name) - Executing: $query" -LogFileOnly | |
Invoke-ScalarQuery -DatabaseName $DatabaseName -DatabaseServer $DatabaseServer -Query $query | |
$query = "DROP ROLE [$($_.Name)]" | |
} | |
else | |
{ | |
$query = "DROP USER [$($_.Name)]" | |
} | |
Write-Message "$($MyInvocation.MyCommand.Name) - Executing: $query" -LogFileOnly | |
Invoke-ScalarQuery -DatabaseName $DatabaseName -DatabaseServer $DatabaseServer -Query $query | |
} | |
} | |
catch | |
{ | |
Write-Message "$($MyInvocation.MyCommand.Name) - Exception: $($_.Exception.ToString())" -LogFileOnly | |
throw $_.Exception | |
} | |
finally | |
{ | |
$ErrorActionPreference = "Continue" | |
} | |
} | |
function Remove-CustomDatabasePermissions() | |
{ | |
[cmdletbinding()] | |
param( | |
[Parameter(Mandatory=$true)][string]$DatabaseName, | |
[Parameter(Mandatory=$true)][string]$DatabaseServer | |
) | |
$ErrorActionPreference = "Stop" | |
try | |
{ | |
# update the SharePoint database role owner to dbo | |
Update-SharePointDatabaseRoleOwner -DatabaseName $DatabaseName -DatabaseServer $DatabaseServer -Role "SPDataAccess" -Owner "dbo" | |
Update-SharePointDatabaseRoleOwner -DatabaseName $DatabaseName -DatabaseServer $DatabaseServer -Role "SPReadOnly" -Owner "dbo" | |
# remove the custom schemas | |
Remove-NonDefaultDatabaseSchemas -DatabaseName $DatabaseName -DatabaseServer $DatabaseServer | |
# remove the custom users | |
Remove-NonDefaultDatabaseUsers -DatabaseName $DatabaseName -DatabaseServer $DatabaseServer | |
} | |
catch | |
{ | |
Write-Message "$($MyInvocation.MyCommand.Name) - Exception: $($_.Exception.ToString())" -LogFileOnly | |
throw $_.Exception | |
} | |
finally | |
{ | |
$ErrorActionPreference = "Continue" | |
} | |
} | |
function Set-DatabaseOwner() | |
{ | |
[cmdletbinding()] | |
param( | |
[Parameter(Mandatory=$true)][string]$DatabaseName, | |
[Parameter(Mandatory=$true)][string]$DatabaseServer, | |
[Parameter(Mandatory=$true)][string]$OwnerLogin | |
) | |
$ErrorActionPreference = "Stop" | |
try | |
{ | |
$query = "EXEC sp_changedbowner [$OwnerLogin]" | |
Write-Message "$($MyInvocation.MyCommand.Name) - Executing: $Query" -LogFileOnly | |
Invoke-ScalarQuery -DatabaseName $DatabaseName -DatabaseServer $DatabaseServer -Query $query | |
} | |
catch | |
{ | |
Write-Message "$($MyInvocation.MyCommand.Name) - Exception: $($_.Exception.ToString())" -LogFileOnly | |
throw $_.Exception | |
} | |
finally | |
{ | |
$ErrorActionPreference = "Continue" | |
} | |
} | |
function Write-Step() | |
{ | |
[cmdletbinding()] | |
param( | |
[Parameter(Mandatory=$true)][int]$CurrentStep, | |
[Parameter(Mandatory=$true)][int]$TotalSteps, | |
[Parameter(Mandatory=$true)][string]$Message, | |
[ConsoleColor]$Color | |
) | |
$format = "`t{0:D2} of {1:D2} - {2}" | |
$msg = $format -f $CurrentStep, $TotalSteps, $Message | |
Write-Message $msg -ForegroundColor $Color | |
} | |
function Set-EmailNotificationProperties() | |
{ | |
[cmdletbinding()] | |
param( | |
[Parameter(Mandatory=$false)][string]$ToAddress, | |
[Parameter(Mandatory=$false)][string]$FromAddress, | |
[Parameter(Mandatory=$false)][string]$SmtpServer | |
) | |
$script:m_emailToAddress = $ToAddress | |
$script:m_emailFromAddress = $FromAddress | |
$script:m_smtpServerName = $SmtpServer | |
Write-Message "Email Notification Properties:" -LogFileOnly | |
Write-Message "`t`tSMTP Server: $($script:m_smtpServerName)" -LogFileOnly | |
Write-Message "`t`tTo Address: $($script:m_emailToAddress)" -LogFileOnly | |
Write-Message "`t`tFrom Address: $($script:m_emailFromAddress)" -LogFileOnly | |
} | |
function Get-SmtpServer() | |
{ | |
return $script:m_smtpServerName | |
} | |
function Get-EmailToAddress() | |
{ | |
return $script:m_emailToAddress.Split(";") | |
} | |
function Get-EmailFromAddress() | |
{ | |
return $script:m_emailFromAddress | |
} | |
function Set-ExecutionLogFile() | |
{ | |
param([Parameter(Mandatory=$true)][string]$FilePath) | |
$script:m_executionLogFilePath = $FilePath | |
Write-Message -Object "Log File Created" -LogFileOnly | |
} | |
function Get-ExecutionLogFile() | |
{ | |
return $script:m_executionLogFilePath | |
} | |
function Write-Message() | |
{ | |
[cmdletbinding()] | |
param( | |
[Parameter(Mandatory=$true)][object]$Object, | |
[Parameter()][System.ConsoleColor]$ForegroundColor, | |
[Parameter()][switch]$LogFileOnly, | |
[Parameter()][switch]$ConsoleOnly | |
) | |
if(!$LogFileOnly) | |
{ | |
if($ForegroundColor) | |
{ | |
$object | Write-Host -ForegroundColor $ForegroundColor | |
} | |
else | |
{ | |
$object | Write-Host | |
} | |
} | |
# log the message to our custom log file | |
if($(Get-ExecutionLogFile) -and !$ConsoleOnly) | |
{ | |
$logMessage = "{0} - {1}" -f (Get-Date).ToString(), $Object | |
$logMessage | Out-File $(Get-ExecutionLogFile) -Append -Force | |
# log the message to ULS | |
if($script:m_DiagnosticsService) | |
{ | |
$script:m_DiagnosticsService.WriteTrace(0, $script:m_ULSCategory, [Microsoft.SharePoint.Administration.TraceSeverity]::High, $Object.ToString()) | |
} | |
} | |
} | |
function Send-EmailNotification() | |
{ | |
[cmdletbinding()] | |
param( | |
[Parameter()][string]$Body, | |
[Parameter()][string]$Subject, | |
[Parameter()][switch]$AttachConsoleLog | |
) | |
$ErrorActionPreference = "Stop" | |
try | |
{ | |
if($(Get-SmtpServer) -and $(Get-EmailToAddress) -and $(Get-EmailFromAddress)) | |
{ | |
$params = @{ | |
To = Get-EmailToAddress; | |
From = Get-EmailFromAddress; | |
SmtpServer = Get-SmtpServer; | |
Subject = $Subject; | |
Body = $Body; | |
} | |
# attach the console log, if it exists | |
if($AttachConsoleLog -and $script:m_executionLogFilePath -and (Test-Path -Path $script:m_executionLogFilePath -PathType Leaf)) | |
{ | |
$params.Add("Attachments", $script:m_executionLogFilePath) | |
} | |
Send-MailMessage @params | |
} | |
else | |
{ | |
Write-Message "`t`tEmail could not be sent. Message Details:" -ForegroundColor Yellow | |
Write-Message "`t`tSubject: $Subject" -ForegroundColor Yellow | |
Write-Message "`t`tBody: $Body" -ForegroundColor Yellow | |
} | |
} | |
catch | |
{ | |
Write-Message "$($MyInvocation.MyCommand.Name) - Exception: $($_.Exception.ToString())" -LogFileOnly | |
Write-Message "`n`tError: Error Sending Email Message" -ForegroundColor Red -ConsoleOnly | |
Write-Message "`tDetail: $($_.Exception.Message)" -ForegroundColor Red -ConsoleOnly | |
} | |
finally | |
{ | |
$ErrorActionPreference = "Continue" | |
} | |
} | |
function Write-UpgradeDatabaseInfoToLogFile() | |
{ | |
[cmdletbinding()] | |
param([Parameter()][object[]]$DatabaseInformation) | |
Write-Message "Database Upgrade Details:" -LogFileOnly | |
Write-ObjectArraryPropertiesToLogFile -ObjectArray $DatabaseInformation | |
} | |
function Write-DatabaseUpgradeResultsToLogFile() | |
{ | |
[cmdletbinding()] | |
param([Parameter()][object[]]$DatabaseUpgradeResults) | |
Write-Message "Database Upgrade Results:" -LogFileOnly | |
Write-ObjectArraryPropertiesToLogFile -ObjectArray $DatabaseUpgradeResults | |
} | |
function Write-ObjectArraryPropertiesToLogFile() | |
{ | |
[cmdletbinding()] | |
param([Parameter()][object[]]$ObjectArray) | |
if($ObjectArray) | |
{ | |
$ObjectArray | % { | |
$obj = $_ | |
$obj | Get-Member -MemberType *Property | % { | |
$formattedInfo = "`t`t{0}: {1}" -f $_.Name, $obj.($_.Name) | |
Write-Message $formattedInfo -LogFileOnly | |
} | |
} | |
} | |
} | |
function Get-UniqueSiteCollectionUrl | |
{ | |
[cmdletbinding()] | |
param([Parameter(Mandatory=$true)][Uri]$Uri) | |
$site = Get-SPSite -Identity $Uri.ToString() -ErrorAction SilentlyContinue | |
if(-not $site) | |
{ | |
return $Uri | |
} | |
$index = 1 | |
do | |
{ | |
$uniqueUrl = "$($Uri.ToString())$index" | |
$site = Get-SPSite -Identity $uniqueUrl -ErrorAction SilentlyContinue | |
if(-not $site) | |
{ | |
return New-Object System.Uri($uniqueUrl) | |
break | |
} | |
$index++ | |
} | |
while($true) | |
} | |
function Rename-SiteCollection | |
{ | |
[cmdletbinding()] | |
param | |
( | |
[Parameter(Mandatory=$true)][Microsoft.SharePoint.SPSite]$Site, | |
[Parameter(Mandatory=$true)][Uri]$NewUri | |
) | |
$currentUri = New-Object Uri($site.Url) | |
if($site.HostHeaderIsSiteName) | |
{ | |
$managedPaths = $site.WebApplication.WebService.HostHeaderPrefixes | |
} | |
else | |
{ | |
$managedPaths = $site.WebApplication.Prefixes | |
} | |
# different URL segment lengths means the old URL might be under a custom managed path | |
if($currentUri.Segments.Length -ne $NewUri.Segments.Length ) | |
{ | |
if($managedPaths.Contains($currentUri.AbsolutePath)) | |
{ | |
# need to remove the matching managed path | |
if($site.HostHeaderIsSiteName) | |
{ | |
Remove-SPManagedPath -Identity $currentUri.AbsolutePath -HostHeader -Confirm:$false | |
} | |
else | |
{ | |
Remove-SPManagedPath -Identity $currentUri.AbsolutePath -WebApplication $site.WebApplication -Confirm:$false | |
} | |
} | |
try | |
{ | |
# need to rename the site to a bogus root site url, then rename again to a HNSC path based site | |
$randomNumber = Get-Random -Minimum 1000 -Maximum 9999 | |
$tempUri = New-Object System.Uri("$($NewUri.Scheme)://$randomNumber.upgrade.local") | |
Write-Message "Renaming site with temp URL: $($NewUri.ToString())" -LogFileOnly | |
$site.Rename( $NewUri ) | |
} | |
catch | |
{ | |
Write-Message "Failed to Rename Site" -ForegroundColor Red | |
Write-Message "`tOriginal Site URL: $($currentUri.ToString())" -ForegroundColor Red | |
Write-Message "`tCurrent URL: $($site.Url)" -ForegroundColor Red | |
Write-Message "`tRequested URL: $($tempUri.ToString())" -ForegroundColor Red | |
return | |
} | |
#re-fetch the site object and rename again | |
$site = Get-SPSite -Identity $site.ID | |
try | |
{ | |
Write-Message "Renaming site with permanent URL: $($NewUri.ToString())" -LogFileOnly | |
$site.Rename( $NewUri ) | |
} | |
catch | |
{ | |
Write-Message "Failed to Rename Site" -ForegroundColor Red | |
Write-Message "`tOriginal Site URL: $($currentUri.ToString())" -ForegroundColor Red | |
Write-Message "`tCurrent URL: $($site.Url)" -ForegroundColor Red | |
Write-Message "`tRequested URL: $($tempUri.ToString())" -ForegroundColor Red | |
return | |
} | |
} | |
else | |
{ | |
# check if anything but the last segment is different | |
$missMatchSegments = $false | |
for($x=0; $x -lt $NewUri.Segments.Length-1; $x++) | |
{ | |
if([string]::CompareOrdinal( $NewUri.Segments[$x], $currentUri.Segments[$x] )) | |
{ | |
$missMatchSegments = $true | |
break | |
} | |
} | |
if($missMatchSegments) | |
{ | |
Write-Message "Mismatched segments found for rename operation." -LogFileOnly | |
if($managedPaths.Contains($currentUri.AbsolutePath)) | |
{ | |
# need to remove the matching managed path | |
if($site.HostHeaderIsSiteName) | |
{ | |
Remove-SPManagedPath -Identity $currentUri.AbsolutePath -HostHeader -Confirm:$false | |
} | |
else | |
{ | |
Remove-SPManagedPath -Identity $currentUri.AbsolutePath -WebApplication $site.WebApplication -Confirm:$false | |
} | |
} | |
# need to rename the site to a bogus root site url, then rename again to a HNSC path based site | |
$randomNumber = Get-Random -Minimum 1000 -Maximum 9999 | |
try | |
{ | |
$tempUri = New-Object System.Uri("$($NewUri.Scheme)://$randomNumber.upgrade.local") | |
Write-Message "Renaming site with temp URL: $($tempUri.ToString())" -LogFileOnly | |
$site.Rename( $tempUri ) | |
} | |
catch | |
{ | |
Write-Message "Failed to Rename Site" -ForegroundColor Red | |
Write-Message "`tException: $($_.Exception)" -ForegroundColor Red | |
Write-Message "`tOriginal Site URL: $($currentUri.ToString())" -ForegroundColor Red | |
Write-Message "`tCurrent URL: $($site.Url)" -ForegroundColor Red | |
Write-Message "`tRequested URL: $($tempUri.ToString())" -ForegroundColor Red | |
return | |
} | |
#re-fetch the site object and rename again | |
$site = Get-SPSite -Identity $site.ID | |
try | |
{ | |
Write-Message "Renaming site to permanent URL: $($NewUri.ToString())" -LogFileOnly | |
$site.Rename($NewUri) | |
} | |
catch | |
{ | |
Write-Message "Failed to Rename Site" -ForegroundColor Red | |
Write-Message "`tException: $($_.Exception)" -ForegroundColor Red | |
Write-Message "`tOriginal Site URL: $($currentUri.ToString())" -ForegroundColor Red | |
Write-Message "`tCurrent URL: $($site.Url)" -ForegroundColor Red | |
Write-Message "`tRequested URL: $($NewUri.ToString())" -ForegroundColor Red | |
if( $NewUri.ToString().Length -gt $site.Url.Length ) | |
{ | |
Write-Message "`tThe requested URL may be too long, please try a site url with a shorter relative path." -ForegroundColor Red | |
} | |
} | |
} | |
else | |
{ | |
try | |
{ | |
Write-Message "Starting simple rename." -LogFileOnly | |
# simple rename | |
$Site.Rename($NewUri) | |
} | |
catch | |
{ | |
Write-Message "Failed to Rename Site" -ForegroundColor Red | |
Write-Message "`tException: $($_.Exception)" -ForegroundColor Red | |
Write-Message "`tOriginal Site URL: $($currentUri.ToString())" -ForegroundColor Red | |
Write-Message "`tCurrent URL: $($site.Url)" -ForegroundColor Red | |
Write-Message "`tRequested URL: $($NewUri.ToString())" -ForegroundColor Red | |
} | |
} | |
} | |
} | |
$MountContentDatabaseScriptBlock = { | |
param( | |
[string]$DatabaseName, | |
[string]$DatabaseServer, | |
[string]$WebApplicationUrl) | |
try | |
{ | |
# start a fresh ULS log | |
New-SPLogFile | |
$claimsUpgradeSeconds = 0 | |
$claimsUpgradeResult = $databaseUpgradeResult = "Completed" | |
$upgradeErrorMessage = $upgradeWarningMessage = "" | |
# we need to stagger start time to prevent upgrades from starting at the same time | |
# because they will have a session naming conflict and cause the upgrade to fail | |
Start-Sleep -Seconds $( Get-Random -Minimum 5.0 -Maximum 60.0 ) | |
$databaseUpgradeStopWatch = Measure-Command { | |
$upgradedDatabase = Mount-SPContentDatabase ` | |
-Name $DatabaseName ` | |
-DatabaseServer $DatabaseServer ` | |
-WebApplication $WebApplicationUrl ` | |
-ErrorVariable upgradeErrorMessage ` | |
-WarningVariable upgradeWarningMessage ` | |
-ErrorAction SilentlyContinue ` | |
-AssignNewDatabaseID ` | |
-ClearChangeLog | |
if($upgradedDatabase.NeedsUpgrade) | |
{ | |
$upgradedDatabase | Upgrade-SPContentDatabase -Confirm:$false | |
} | |
} | |
# refresh the database object | |
$upgradedDatabase = Get-SPContentDatabase -Identity $DatabaseName -ErrorAction SilentlyContinue | |
# 05/17/2019 - added build version detection | |
# only attempt a claims migration if the code is running on a 2013 farm. We will assume that if we are 2016+ the database | |
# has already have it's users upgraded to claims format. | |
if( $(Get-SPFarm).BuildVersion.Major -eq 15 ) | |
{ | |
# if the web app is Windows Claims enabled, migrated the user accounts to claims | |
if($upgradedDatabase -and $upgradedDatabase.WebApplication.UseClaimsAuthentication) | |
{ | |
try | |
{ | |
$claimsMigrationStopWatch = Measure-Command { | |
# added on 02/08/2018 | |
$arguments = New-Object Microsoft.SharePoint.Administration.SPWebApplication+SPMigrateUserParameters | |
$arguments.AddDatabaseToMigrate( $upgradedDatabase ) | |
$result = $upgradedDatabase.WebApplication.MigrateUsersToClaims( | |
[System.Security.Principal.WindowsIdentity]::GetCurrent().Name, | |
$true <# removePermissionsAfter #>, | |
$arguments ) | |
<# | |
# removed on 02/08/2018 | |
Convert-SPWebApplication ` | |
-Identity $WebApplicationUrl ` | |
-Database $upgradedDatabase ` | |
-To Claims ` | |
-From Legacy ` | |
-Force | |
#> | |
} | |
} | |
catch | |
{ | |
$claimsUpgradeResult = $_.Exception.Message | |
} | |
} | |
} | |
# start a fresh ULS log | |
New-SPLogFile | |
if($upgradeWarningMessage) | |
{ | |
$databaseUpgradeResult = $upgradeWarningMessage | |
} | |
if($upgradeErrorMessage) | |
{ | |
$databaseUpgradeResult = $upgradeErrorMessage | |
} | |
if($claimsMigrationStopWatch) | |
{ | |
$claimsUpgradeSeconds = $claimsMigrationStopWatch.TotalSeconds.ToString("N1"); | |
} | |
$result = New-Object PSObject -Property @{ | |
ContentDatabaseName = $DatabaseName; | |
DatabaseUpgradeResult = $databaseUpgradeResult; | |
DatabaseUpgradeExecutionTime = $databaseUpgradeStopWatch.TotalSeconds.ToString("N1"); | |
ClaimsUpgradeResult = $claimsUpgradeResult; | |
ClaimsUpgradeExecutionTime = $claimsUpgradeSeconds; | |
} | |
} | |
catch | |
{ | |
$upgradeErrorMessage = $_.Exception.ToString() | |
$result = New-Object PSObject -Property @{ | |
ContentDatabaseName = $DatabaseName; | |
DatabaseUpgradeResult = $upgradeErrorMessage; | |
DatabaseUpgradeExecutionTime = 0; | |
ClaimsUpgradeResult = $claimsUpgradeResult; | |
ClaimsUpgradeExecutionTime = 0; | |
} | |
} | |
return $result | |
} | |
$SiteCollectionVersionUpgradeScriptBlock = { | |
param | |
( | |
[Guid]$SiteId | |
) | |
$siteUpgradeError = "" | |
$result = $null | |
try | |
{ | |
$siteCollection = Get-SPSite -Identity $SiteId -ErrorAction SilentlyContinue | |
if($siteCollection) | |
{ | |
# we need to stagger start time to prevent upgrades from starting at the same time | |
# because they will have a session naming conflict and cause the upgrade log to contain | |
# multiple site data | |
Start-Sleep -Seconds $( Get-Random -Minimum 5.0 -Maximum 20.0 ) | |
$upgradeStopWatch = Measure-Command { | |
$siteCollection | Upgrade-SPSite -VersionUpgrade -Unthrottled -ErrorVariable siteUpgradeError -ErrorAction SilentlyContinue | |
} | |
$siteCollection.Dispose() | |
$siteCollection = Get-SPSite -Identity $SiteId -ErrorAction SilentlyContinue | |
$result = New-Object PSObject -Property @{ | |
SiteId = $siteCollection.Id; | |
SiteUrl = $siteCollection.Url; | |
Errors = $siteCollection.UpgradeInfo.Errors | |
Warnings = $siteCollection.UpgradeInfo.Warnings | |
LogFile = $siteCollection.UpgradeInfo.LogFile | |
ErrorFile = $siteCollection.UpgradeInfo.LogFile | |
CompatibilityLevel = $siteCollection.CompatibilityLevel | |
ExecutionTime = $upgradeStopWatch.ToString("hh\:mm\:ss"); | |
} | |
} | |
} | |
catch | |
{ | |
$result = New-Object PSObject -Property @{ | |
SiteId = $SiteId; | |
SiteUrl = "UNKNOWN"; | |
Errors = 1 | |
Warnings = 0 | |
LogFile = "UKNOWN"; | |
ErrorFile = "UKNOWN"; | |
CompatibilityLevel = "UKNOWN"; | |
ExecutionTime = $upgradeStopWatch.ToString("hh\:mm\:ss"); | |
} | |
} | |
finally | |
{ | |
if($siteCollection) | |
{ | |
$siteCollection.Dispose() | |
} | |
} | |
return $result | |
} | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment