Simple Windows Powershell to Ease Preparing for Upgrade

The more I dig into PreUpgradeCheck, the more I’m finding everyday value.  As well, the more I use Test-SpContentDatabase the windows powershell commandlet designed for testing databases prior to attaching them, the more I see differences between these tools.  I see a big mistake being made by customers who see these tools as the same, or see them as providing the same data.  It is a mistake.  Let me show you how.

First if we look at the default preupgrade check you’ll see it’s focus on features and site definitions.  There is no mention of missing references to themes, XSL, Images, or web part packages.  This is the richness I’m seeing in the powershell test-SpContentDatabase.  This difference is huge, but the output is overwhelming… Windows Powershell to the rescue!!!

What you don’t see in the examples on the Windows Powershell is the enhancements to the output.  It’s the output that’s overwhelming.  First let’s look at default output then I’ll show you some tips.

PS > get-help test-spcontentdatabase

NAME
    Test-SPContentDatabase

SYNOPSIS
    Tests a content database.

SYNTAX
    Test-SPContentDatabase [-Identity] <SPContentDatabasePipeBind> [-AssignmentCollection <SPAssignmentCollection>] [-D
    atabaseCredentials <PSCredential>] [-ServerInstance <SPDatabaseServiceInstancePipeBind>] [-ShowRowCounts <SwitchPar
    ameter>] [<CommonParameters>]

    Test-SPContentDatabase -Name <String> -WebApplication <SPWebApplicationPipeBind> [-AssignmentCollection <SPAssignme
    ntCollection>] [-DatabaseCredentials <PSCredential>] [-ServerInstance <SPDatabaseServiceInstancePipeBind>] [-ShowRo
    wCounts <SwitchParameter>] [<CommonParameters>]

DESCRIPTION
    Use the Test-SPContentDatabase cmdlet to test a content database against a Web application to verify all customizations referenced within the content database are also installed in the web application. This cmdlet can be issued against a content database currently attached to the farm, or a content database that is not connected to the farm. It can be used to test content databases from SharePoint 2010 Products and from SharePoint Products and Technologies
    .

    The Test-SPContentDatabase cmdlet does not change any of the data or structure of the content database, but can cause load on the database while the checks are in progress, which could temporarily block use of the content database
    . This cmdlet should only be used against a content database that is currently under low or no usage.

    For permissions and the most current information about Windows PowerShell for SharePoint Products, see the online documentation (http://go.microsoft.com/fwlink/?LinkId=163185).

RELATED LINKS
    Get-SPContentDatabase
    New-SPContentDatabase
    Set-SPContentDatabase
    Remove-SPContentDatabase
    Mount-SPContentDatabase
    Upgrade-SPContentDatabase

REMARKS
    To see the examples, type: "get-help Test-SPContentDatabase -examples".
    For more information, type: "get-help Test-SPContentDatabase -detailed".
    For technical information, type: "get-help Test-SPContentDatabase -full".

PS >get-help Test-SPContentDatabase -examples

——————-

C:PS>Test-SPContentDatabase -name WSS_Content_DB -webapplication http://sitename

This example tests the WSS_Content_DB content database against the sitename Web application, and returns a list of
issues.

—————

Here’s what I get when I run the default command.

test-spcontentdatabase –name Databasename –webapplication http://webapp –serverinstance SQLServerName

It scrolls and scrolls and scrolls with hundreds of lines of output. You’ll notice the  already different output than PreUpgradeCheck which clearly puts the features all together and never mentions missing XSL.

After watching Dan Holme’s powershell I was noticing the output options for formatting the output from Windows Powershell.  I got some great help from Nickholas who gave me one of his new awesome SharePoint books on Powershell.  I simply pipe | (with the pipe character the one with the two verticle lines that looks like a colon) the output to ConvertTo-Html and pipe that to and Output file by using Out-file then specifying the location of the file, then opening that in explorer.

Output to HTML

PS > Test-SpContentDatabase -name WSS_Content_SP2 –webapplication http://webapp -serverinstance SQLServer | ConvertTo-Html | Out-file c:filesTestSPContent_db2.htm

 

 

It’s incredible how much easier it is to read the output in nice columns.  Now in HTML it can easily be opened in SharePoint designer where you could add formatting to make it even easier to read.

I’m also a fan of the CSV and XML formats

Output to CSV

PS > test-spcontentdatabase -name WSS_Content_SP2 -webapplication http://spjoel -serverinstance SQLServer | ConvertTo-CSV | Out-file c:TestSPContentdb2.csv

Open up the file in Notepad and you have each line of the output in a CSV that is much much easier to read.  Throw the CSV into Excel and you can manipulate the content to your hearts content by formatting the data with default formatting and adding an extra column for how critical it is then format the rows in green yellow and red.

 

This might be easier to illustrate what I’m seeing, but with wrapping turned off it’s even easier to see.

#TYPE Microsoft.SharePoint.Upgrade.SPContentDatabaseTestResult
"Category","Error","UpgradeBlocking","Message","Remedy"
"MissingFeature","True","False","Database [WSS_Content_SP2] has reference(s) to a missing feature: Id = [bbe9def7-2fe9-a0b1-d712-aa128c837ebe].","The feature with Id bbe9def7-2fe9-a0b1-d712-aa128c837ebe is referenced in the database [WSS_Content_SP2], but is not installed on the current farm. The missing feature may cause upgrade to fail. Please install any solution which contains the feature and restart upgrade if necessary."
"MissingSetupFile","True","False","File [FeaturesCKS EBE ThemesThemesCleanAdminLinks.xsl] is referenced [1] times in the database [WSS_Content_SP2], but is not installed on the current farm. Please install any feature/solution which contains this file.","One or more setup files are referenced in the database [WSS_Content_SP2], but are not installed on the current farm. Please install any feature or solution which contains these files."
"MissingSetupFile","True","False","File [FeaturesCKS EBE ThemesThemesCleanCategories.xsl] is referenced [1] times in the database [WSS_Content_SP2], but is not installed on the current farm. Please install any feature/solution which contains this file.","One or more setup files are referenced in the database [WSS_Content_SP2], but are not installed on the current farm. Please install any feature or solution which contains these files."
"MissingSetupFile","True","False","File [FeaturesCKS EBE ThemesThemesCleanComments.xsl] is referenced [1] times in the database [WSS_Content_SP2], but is not installed on the current farm. Please install any feature/solution which contains this file.","One or more setup files are referenced in the database [WSS_Content_SP2], but are not installed on the current farm. Please install any feature or solution which contains these files."
"MissingSetupFile","True","False","File [FeaturesCKS EBE ThemesThemesCleanCurrentDate.xsl] is referenced [1] times in the database [WSS_Content_SP2], but is not installed on the current farm. Please install any feature/solution which contains this file.","One or more setup files are referenced in the database [WSS_Content_SP2], but are not installed on the current farm. Please install any feature or solution which contains these files."
"MissingSetupFile","True","False","File [FeaturesCKS EBE ThemesThemesCleanHead.xsl] is referenced [1] times in the database [WSS_Content_SP2], but is not installed on the current farm. Please install any feature/solution which contains this file.","One or more setup files are referenced in the database [WSS_Content_SP2], but are not installed on the current farm. Please install any feature or solution which contains these files."
"MissingSetupFile","True","False","File [FeaturesCKS EBE ThemesThemesCleanimagesdelicious.png] is referenced [1] times in the database [WSS_Content_SP2], but is not installed on the current farm. Please install any feature/solution which contains this file.","One or more setup files are referenced in the database [WSS_Content_SP2], but are not installed on the current farm. Please install any feature or solution which contains these files."

 

Output to XML

PS C:Usersjoleson> test-spcontentdatabase -name ContentDatabase -webapplication http://WebApp -serverinstance SQLServer | Export-CLIXML c:filesTestSPContentdb.xml

Note the XML output pipes to Export-CLIXML and then an output filename is provided.

<Objs Version="1.1.0.1" xmlns="http://schemas.microsoft.com/powershell/2004/04">
  <Obj RefId="0">
    <TN RefId="0">
      <T>Microsoft.SharePoint.Upgrade.SPContentDatabaseTestResult</T>
      <T>System.Object</T>
    </TN>
    <ToString>Microsoft.SharePoint.Upgrade.SPContentDatabaseTestResult</ToString>
    <Props>
      <S N="Category">MissingFeature</S>
      <B N="Error">true</B>
      <B N="UpgradeBlocking">false</B>
      <S N="Message">Database [WSS_Content_SP2] has reference(s) to a missing feature: Id = [bbe9def7-2fe9-a0b1-d712-aa128c837ebe].</S>
      <S N="Remedy">The feature with Id bbe9def7-2fe9-a0b1-d712-aa128c837ebe is referenced in the database [WSS_Content_SP2], but is not installed on the current farm. The missing feature may cause upgrade to fail. Please install any solution which contains the feature and restart upgrade if necessary.</S>
    </Props>
  </Obj>
  <Obj RefId="1">
    <TNRef RefId="0" />
    <ToString>Microsoft.SharePoint.Upgrade.SPContentDatabaseTestResult</ToString>
    <Props>
      <S N="Category">MissingSetupFile</S>
      <B N="Error">true</B>
      <B N="UpgradeBlocking">false</B>
      <S N="Message">File [FeaturesCKS EBE ThemesThemesCleanAdminLinks.xsl] is referenced [1] times in the database [WSS_Content_SP2], but is not installed on the current farm. Please install any feature/solution which contains this file.</S>
      <S N="Remedy">One or more setup files are referenced in the database [WSS_Content_SP2], but are not installed on the current farm. Please install any feature or solution which contains these files.</S>
    </Props>
  </Obj>
  <Obj RefId="2">
    <TNRef RefId="0" />
    <ToString>Microsoft.SharePoint.Upgrade.SPContentDatabaseTestResult</ToString>
    <Props>
      <S N="Category">MissingSetupFile</S>
      <B N="Error">true</B>
      <B N="UpgradeBlocking">false</B>
      <S N="Message">File [FeaturesCKS EBE ThemesThemesCleanCategories.xsl] is referenced [1] times in the database [WSS_Content_SP2], but is not installed on the current farm. Please install any feature/solution which contains this file.</S>
      <S N="Remedy">One or more setup files are referenced in the database [WSS_Content_SP2], but are not installed on the current farm. Please install any feature or solution which contains these files.</S>
    </Props>
  </Obj>

Summary

Don’t just look to PreUpgradeCheck for checking your data.  Use Test-SpContentDatabase as well.  Don’t get overwhelmed with the output.  Decide what format is easiest to read and use for verifying and fixing.  By default YOU WILL be overwhelmed.  Use Output or Export to CSV, XML, or HTML and open it up in your browser or in Excel so you can much more easily consume, track and fix these issues.  In the example there are tons of lines of output, but ultimately by getting it into a format that’s readable I found most of these issues relate to a single feature that’s missing a number of themes that were referenced.  Looking at it on the command prompt it would be nearly impossible to make the same conclusion.  One last myth… Test-SpContentDatabase isn’t only for Database attach.  Use it with InPlace upgrade as well.  Setup a single install box and use the commandlet to check the source databases before any upgrade.  It will *Really* simplify your life.

I really appreciate the help of Niklas Goude who I ran into in the speaker room at SEF SharePoint Exchange Forum.  He handed me a his book which had great insights into using powershell with SharePoint.  Little about the Book: http://sharepointandpowershell.com/ Check out his blog www.powershell.nu and the co-author’s Mattias Karlsson’s Blog http://mysharepointofview.com/.  It’s hot off the shelves.