Share via

SharePoint 2013 Upgrading: SQL and PowerShell Scripts to help resolve non-UpgradeBlocking missing dependencies

  • Article
This topic is a work in progress. Please abstain from editing it right now. You're welcome to contribute here when its done!

Introduction

Provided here are the T-SQL scripts that I run against SharePoint content databases to help resolve various non-UpgradeBlocking missing dependencies during upgrades. These retrieve critical information needed for developing the appropriate PowerShell scripts that actually resolve those missing dependencies. These scripts work when upgrading from 2010 to 2013 and from 2013 to 2016. I have not yet tested them for upgrades from 2016 to 2019, not having had the chance yet. I'll update this posting when that happens. Note that these scripts only interrogate the content database. They don't perform any write operations. These scripts are used as part of the usual routine resolution of missing dependencies. Here's the typical process I engage in:

  1. Backup the content database on the 2010 prod farm hosting the site collection
  2. Restore the content database to the SQL server backend for the 2013 dev farm
  3. Do test-spcontentdatabase to discover missing dependency issues
  4. Extract the necessary information from each missing dependency category
  5. Execute the SQL scripts against the content database to retrieve information on instances of the missing dependency
  6. Develop the PowerShell scripts based upon that information 
  7. Mount the content database to a web application
  8. Execute the PowerShell scripts
  9. Run Test-SPContentDatabase again to verify problem resolved
  10. Backup the content database
  11. Restore to the content database to the SQL server backend for the 2013 prod farm
  12. Mount the content database to the target web application

SQL & PowerShell Scripts to help Resolve MissingFeature Dependencies

After executing Test-SPContentDatabase, you'll see something like the following in the report (I've left out the last two columns since the don't add additional useful information):

Category Error UpgradeBlocking Message
MissingFeature TRUE FALSE Database [SP2010_Content_CONTOSO] has reference(s) to a missing feature: Id = [50e31660-5eaa-4691-9f44-04552b127ad5]
MissingFeature TRUE FALSE Database [SP2010_Content_CONTOSO] has reference(s) to a missing feature: Id = [ad739f9e-1525-4dec-a25e-10821ca70c95]

For resolving missing features, the only item of information needed for each MissingFeature item in the Test-SPContentDatabase report is the missing feature ID. List the IDs for convenience:

50e31660-5eaa-4691-9f44-04552b127ad5
ad739f9e-1525-4dec-a25e-10821ca70c95

The feature IDs are used to help discover the actual feature instances in the content database's Features table. This SQL script finds them:

USE SP2010_Content_CONTOSO
SELECT SiteID,WebID,FeatureID
FROM Features WITH (NoLOCK)
WHERE FeatureID IN 
    ('50e31660-5eaa-4691-9f44-04552b127ad5',
    'ad739f9e-1525-4dec-a25e-10821ca70c95')

If there were more missingFeature dependencies listed in the Test-SPContentDatabase report, just add their IDs to the IN set. When you execute this SQL script, it will return just a few or even hundreds of feature instances. Each row indicates an instance of that feature in a site collection or web. You can tell if it's site collection feature instance since the WebID for that instance will be all zeros ("0"). It's important to identify which are site collection feature instances and which are web feature instances since the method used to remove them is different. For site features, use this:

$site=Get-SPSite -Identity "[SiteID]"; $Site.Features.Remove("[FeatureID",$true)

and for web features, use this:

(Get-SPWeb -Identity "[WebID]" -Site "[SiteID]").Features.Remove("[FeatureID]")

You will need to create one statement like these for each feature reference returned from executing the SQL script. As you execute a statement to remove a web feature instance, you may experience an error like the following:

Get-SPWeb : Cannot find an SPWeb object with Id or Url:  and site Url https://contoso.com/sites/site1

This is a pretty good indication that the web containing the feature instance was deleted by the user, and the web is currently sitting in the 1st or 2nd stage Recycle Bin. Because the web still exists, the feature instance it contained was discovered by Test-SPContentDatabase; but because it's no longer actually in the site collection's web collection, no web URL was found for it (see the highlight above). To remove that reference permanently, just flush the Recycle Bins and try again.

After executing the PowerShell statements to remove the features, re-execute the SQL script to verify that the missing feature references were in fact removed from the Features table. If some continue to show up, re-execute the appropriate PowerShell statement that removes it and try again. If re-executing the SQL script doesn't return anything, then re-execute Test-SPContentDatabase. If the MissingFeature entries no longer appear in the report, then this particularly missing dependency category has been resolved and you can move on to the next one. If one or more MissingFeature entries still appear in the report, it's likely that the web contains lists and/or document libraries that are associated with the feature. Therefore, to completely remove the MissingFeature entry you will need to locate each of these lists and libraries so that you can delete them. This SQL script gets that location information for you:

USE SP2010_Content_CONTOSO
SELECT tp_SiteID, tp_WebID, tp_ID, tp_FeatureID, tp_Title, tp_BaseType, AllWebs.FullUrl,tp_Description
FROM AllLists WITH (NoLOCK)
    LEFT JOIN AllWebs ON AllWebs.ID = AllLists.tp_WebID
WHERE tp_FeatureID IN
    ('50e31660-5eaa-4691-9f44-04552b127ad5',
    'ad739f9e-1525-4dec-a25e-10821ca70c95')

The list returned from this SQL script contains all of the information you need. Use the following PowerShell to remove each one:

(Get-SPSite -Identity "[SiteID]").OpenWeb("/[AllWebs.FullUrl]]").Lists["[tp_Title]"].Delete()

You'll need to create one statement for each list and library that needs to be removed. After removing all of these lists and libraries, re-execute Test-SPContentDatabase. The MissingFeature dependency in the report should be gone.

SQL & PowerShell Scripts to help Resolve MissingSetupFile Dependencies

After executing Test-SPContentDatabase, you may find instances of MissingSetupFile dependencies in the report, like the following:

Category Error UpgradeBlocking Message
MissingSetupFile TRUE FALSE File [Features\Bamboo.AlertPlus\WebParts\ Bamboo.AlertPlus.dwp] is referenced [1] times in the database [SP10_Content_CONTOSO], but is not installed on the current farm. Please install any feature/solution which contains this file.
MissingSetupFile TRUE FALSE File [Features\Bamboo.AlertSubscriberWebPart\WebParts\ Bamboo.AlertSubscriberWebPart.dwp] is referenced [1] times in the database [SP10_Content_CONTOSO], but is not installed on the current farm. Please install any feature/solution which contains this file.
MissingSetupFile TRUE FALSE File [pages\Bamboo.DiscussionBoardPlus\ Bamboo.CustomViewPage.aspx] is referenced [8] times in the database [SP10_Content_CONTOSO], but is not installed on the current farm. Please install any feature/solution which contains this file.
MissingSetupFile TRUE FALSE File [Features\Bamboo.RatingWebPart\WebParts\ Bamboo.RatingWebPart.webpart] is referenced [2] times in the database [SP2010_Content_CONTOSO], but is not installed on the current farm. Please install any feature/solution which contains this file.

For resolving missing setup file dependencies, the only item of information you need for each one from this report is the setup file path. List them for convenience:

Features\Bamboo.AlertPlus\WebParts\Bamboo.AlertPlus.dwp
Features\Bamboo.AlertSubscriberWebPart\WebParts\Bamboo.AlertSubscriberWebPart.dwp
pages\Bamboo.DiscussionBoardPlus\Bamboo.CustomViewPage.aspx
Features\Bamboo.RatingWebPart\WebParts\Bamboo.RatingWebPart.webpart

Several different kinds of missing setup file dependencies were found by Test-SPContentDatabase: web parts and actual files. The resolution for these is the same. The first thing to do is to discover the locations of these missing setup file references and remove them. This is accomplished using the following SQL script:

USE SP2010_Content_CONTOSO
SELECT SetupPath, id, SiteID, DirName, LeafName, WebId, ListId
FROM AllDocs (NOLOCK) where SetupPath IN
    ('Features\Bamboo.AlertPlus\WebParts\Bamboo.AlertPlus.dwp',
    'Features\Bamboo.AlertSubscriberWebPart\WebParts\Bamboo.AlertSubscriberWebPart.dwp',
    'pages\Bamboo.DiscussionBoardPlus\Bamboo.CustomViewPage.aspx',
    'Features\Bamboo.RatingWebPart\WebParts\Bamboo.RatingWebPart.webpart')

This returns a listing of instances of this missing setup file that includes location information. You'll then use some PowerShell script to remove them. You'll need to use different PowerShell scripts to remove them depending on whether it is a web part file or a web page, image or other file. Use the following PowerShell script to remove web part files located in webs:

$list=(Get-SPWeb -Identity "[WebID]" -Site "[SiteID]").lists["Web Part Gallery"];$item=$list.items | Where {$_.File.Name -eq "[LeafName]"};$item.delete()

and use this PowerShell script to remove files located in ordinary web document libraries:

$web=Get-SPWeb -Identity "[WebID]" -Site "[SiteID]";$path="[DirName]"&"/"&"[LeafName]";$file=$web.GetFileOrFolderObject($path);$file.Delete()

The second statement may return an error if the web page being deleted also happens to be the web's home page. If that's the case, you'll need to create a new home page for the web and then delete that file. Once all of the statements have been successfully executed, re-execute the SQL script and then re-execute the Test-SPContentDatabase commandlet to verify the missing setup file dependencies have been removed.

SQL & PowerShell Scripts to help Resolve MissingAssembly Dependencies

After executing Test-SPContentDatabase, you may find instances of missing assembly dependencies appearing in the report, such as:

Category Error UpgradeBlocking Message
MissingAssembly TRUE FALSE Assembly [Microsoft.Office.Project.Server.PWA, Version=14.0.0.0,Culture=neutral,PublicKeyToken=71e9bce111e9429c] is referenced in the database [SP10_Content_CONTOSO], but is not installed on the current farm. Please install any feature/solution which contains this assembly.
MissingAssembly TRUE FALSE Assembly [Microsoft.ReportingServices.SharePoint.UI.ServerPages, Version=10.50.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91] is referenced in the database [SP10_Content_CONTOSO], but is not installed on the current farm. Please install any feature/solution which contains this assembly.

NOTE: I added a space in the first entry just after the "," to enable an automatic line break and let this display on the web page. Otherwise, it would have extended off the page. When you use the assembly name in your SQL script, do not use that space but remove it or otherwise your SQL script will return zero results. The second one had the space naturally. To be certain, copy the assembly name exactly as it appears in the Test-SPContentDatabase report.

For resolving missing assembly dependencies, the only item of information you need for your SQL scripts is the assembly name. List them for convenience:

Microsoft.Office.Project.Server.PWA,Version=14.0.0.0,Culture=neutral,PublicKeyToken=71e9bce111e9429c
Microsoft.ReportingServices.SharePoint.UI.ServerPages, Version=10.50.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91

As previously, to resolve these, you need to first discover their locations. The following SQL script does this:

USE SP10_Content_CONTOSO
Select SiteID, WebID, HostType, hostId, ID
FROM EventReceivers (NOLOCK)
WHERE Assembly IN
    ('Microsoft.Office.Project.Server.PWA,Version=14.0.0.0,Culture=neutral,PublicKeyToken=71e9bce111e9429c',
    'Microsoft.ReportingServices.SharePoint.UI.ServerPages, Version=10.50.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91')

This may return hundreds of event receiver instances if your users previously activated the Project and Reporting Services features in their sites. To remove these from each web, use the following PowerShell script:

$web=Get-SPWeb -Identity "[WebID]" -Site "[SiteID]";$list=$web.lists | ?{$_.id -eq "[HostID]"};($list.EventReceivers | ?{$_.id -eq "[ID]"}).Delete()

You'll need to create this script for each and every result returned from the SQL script. If any statement returns an error, ensure that there are no typos and then re-execute. When done, re-execute the SQL scripts for all event receiver instances so as to verify that all of the entries have been removed from the EventReceivers table. If any persist, check for typos. As a final check, re-execute Test-SPContentDatabase.

SQL and PowerShell scripts to resolve missingWebPart dependencies

After executing Test-SPContentDatabase, you may find instances of missing assembly dependencies appearing in the report, such as:

Category Error UpgradeBlocking Message
MissingWebPart TRUE FALSE WebPart class [dc8d37bf-5afb-657e-e673-6c9328f9c912] (class [Microsoft.SharePoint.WebPartPages.BlogYearArchive] from assembly [Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c]) is referenced [2] times in the database [SP10_Content_CONTOSO], but is not installed on the current farm. Please install any feature/solution which contains this web part.
MissingWebPart TRUE FALSE WebPart class [d45f64e5-e285-b089-dae5-0e8a47b75972] (class [Microsoft.Office.Server.WebControls.ChartWebPart] from assembly [Microsoft.Office.Server.Chart, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c]) is referenced [6] times in the database [SP10_Content_CONTOSO], but is not installed on the current farm. Please install any feature/solution which contains this web part.
MissingWebPart TRUE FALSE WebPart class [7919f194-1a06-0aff-3d2a-f44a5bc2e217] is referenced [5] times in the database [SP10_Content_CONTOSO], but is not installed on the current farm. Please install any feature/solution which contains this web part.

The key item of information you need from the Upgrade Report is the GUID. Using it, you can query a JOIN of the AllWebParts and AllDocs lists to find the URLs to all of the pages these web parts are located in. First, compile a list of the GUIDs you need to search for:

  • dc8d37bf-5afb-657e-e673-6c9328f9c912
  • d45f64e5-e285-b089-dae5-0e8a47b75972
  • 7919f194-1a06-0aff-3d2a-f44a5bc2e217

Next, use those GUIDs in this SQL:

USE SP10_Content_CONTOSO
SELECT SiteID, WebID, ListID, DirName, LeafName, tp_WebPartTypeId, tp_WebPartIDProperty, tp_DisplayName
FROM AllDocs WITH (NOLOCK) 
INNER JOIN AllWebParts ON AllDocs.Id = AllWebParts.tp_PageUrlID
WHERE AllWebParts.tp_WebPartTypeID IN
    ('dc8d37bf-5afb-657e-e673-6c9328f9c912',
    'd45f64e5-e285-b089-dae5-0e8a47b75972',
    '7919f194-1a06-0aff-3d2a-f44a5bc2e217')

The key columns in this listing are the DIRNAME and LEAFNAME columns. These provide the URL to the page. Another column to look at is the tp_WebPartIDProperty, This presents the ID of the particular instance of that web part on page. If it is NULL, that page doesn't actually present the web part in the active version of the page but in a previous version.

Now when the web part is experinecing an error, it will present without a title. You can use that to find all web parts on the page that are presenting an error condition because their dependencies are missing. Execute the following in an elevated shell:

$WebAppURL="[webappurl]"
$WebURL = $WebAppURL + "/" + "[DIRNAME]"
$spWeb = Get-SPWeb $WebURL -ErrorAction Stop
$PagePath = $WebURL + "/" + "[LEAFNAME]"
$spWebPartManager = $spWeb.GetLimitedWebPartManager($pageUrl, '
           [System.Web.UI.WebControls.WebParts.PersonalizationScope]::Shared)
$spWebPartManager.Webparts | ?{$_.Title -eq ""}  | ft ID, Title, WebBrowsableObject -auto

This returns a listing with columns ID, Title and WebBrowsableObject. The Title property will be null for all. To to delete them, execute the following PowerShell

$spWebPart1 = $spWebPart=$spWebPartManager.Webparts | ?{$_.ID -eq "[ID]"};
$spWebPartManager.DeleteWebPart($spWebPart1)

where ID is the earlier tp_WebPartIDProperty. For lengthy lists of missing web part dependencies, use the spreadsheet method for rapidly generating complete commands that can be pasted at the prompt. If there are only a few instances, it's just as simple to go the each page and delete it using the pages WebPartMode, or appending "?Contents=1". If the tp_WebPartIDProperty property returned for a web part after executing the SQL is null, you will need to review the page's previous versions to find what page version contains the offending web part, and then delete that version. Or you can simply flush all previous versions. When you do, be sure to also flush the 1st and 2nd stage recycle bins.

tbd

References

Notes

  • tbd