---
title: SQL Server 2016 Upgrade Advisor Automation
description: SQL Server 2016 Upgrade Advisor Automation
---

[The Coeo Blog](https://blog.coeo.com)

# [SQL Server 2016 Upgrade Advisor Automation](https://blog.coeo.com/andrewjones/2016/08/26/sql-server-2016-upgrade-advisor-automation)

 Written by [Andy Jones](https://blog.coeo.com/author/andy-jones) | 26-Aug-2016 11:18:49

The SQL Server 2016 Upgrade Advisor is a great graphical user interface for analysing your existing SQL Server database to discover any features or syntax you are using that are not compatible with a 2016 upgrade.

This tool produces a great HTML report that lists all the items you will need to fix prior to upgrading. This works fine, but the issue is running this tool manually can become onerous if your requirements are to analyse tens or hundreds of databases. This is where the command line interface for the tool, SqlAdvisorCmd.exe can greatly assist.

The following switches are available

- /Scenario:Upgrade|StretchDB Specifies the scenario. Supported scenarios are Upgrade and StretchDB (short form /S)
- /ConnectionStrings:<string>\[,<string>,<string>,...\] ConnectionStrings (short form /C)
- /Server:<string> Specifies server (short form /s)
- /Database:<string> Specifies database to run analysis on (short form /d)
- /UserId:<string> UserId for server (short form /u)
- /Password:<string> Password for server (short form /p)
- /IntegratedAuthentication\[+|-|:true|:false\] Integrated Authentication option (short form /I)
- /ExportType:Html|Csv Specify format to export results to: CSV or HTML (short form /E)
- /Individual\[+|-|:true|:false\] Specify individual reports instead of an aggregated report
- /exportFolderPath:<string> Folder path to export results to (short form /FolderPath)
- /MicrosoftAssessmentAndPlanningToolkit\[+|-|:true|:false\] Run analysis on databases inventoried by Microsoft Assessment and Planning Toolkit (MAP). UserID, password, and MAP inventory database must also be  
   given when using this option (short form /MAP)
- /MicrosoftAssessmentandPlanningToolkitDatabase:<string> Database with results from running Microsoft Assessment and Planning Toolkit (MAP) (short form /MAPdatabase)
- @<file> Read response file for more options

As we are able to script the upgrade analysis, we can take advantage of Powershell to automate this process for any amount of databases we desire. Firstly, I created a CSV file at C:\\UpgradeAdvisor\\Servers.csv with two columns for the servers and databases to be included in the analysis: -

Server,Database  
 OFFICE,AdventureWorks2012  
 OFFICE,DBA  
 OFFICE\\SQLEXPRESS,AdventureWorks2014

Then run the following Powershell script to produce an upgrade report for each database within the CSV

 Import-CSV "C:\\UpgradeAdvisor\\Servers.csv"  | Foreach-Object{

    $Server = $\_ . Server

    $Database = $\_ . Database

    $Path = "C:\\UpgradeAdvisor\\ $Database "

    Write-Host $Database

    New-Item -ItemType Directory -Path $Path -Force

    Start-Process -FilePath 'C:\\Program Files\\Microsoft SQL Server 2016 Upgrade Advisor\\SqlAdvisorCmd.exe' -ArgumentList "/Scenario:Upgrade /Server: $Server /Database: $Database /IntegratedAuthentication:true /ExportType:Html /exportFolderPath: $Path "

}

 

This will write the report to a separate sub-directory of C:\\UpgradeAdvisor\\ per database.

One limitation of the command line interface appears in the /Scenario switch. The GUI exposes four options as shown in the following graphic

1. Migrate to Azure SQL Database
2. Run In-Memory OLTP and Columnstore Advisor
3. Run Stretch Database Advisor
4. Analyze and Migrate to SQL Server

 

<https://blog.coeo.com/hubfs/Imported_Blog_Media/SQL-Server-2016-Upgrade-Advisor-1.jpg>

 

Unfortunately, only the latter two of these options are available via the command line interface via the /Scenario switch, namely

1. Run Stretch Database Advisor, and
2. Analyze and Migrate to SQL Server

 

[View full post](https://blog.coeo.com/andrewjones/2016/08/26/sql-server-2016-upgrade-advisor-automation)

```json
{
  "@context" : "http://schema.org",
  "@type" : "BlogPosting",
  "author" : {
    "@type" : "Person",
    "name" : "Andy Jones"
  },
  "dateModified" : "2019-01-23T16:32:29.592Z",
  "datePublished" : "2016-08-26T11:18:49Z",
  "headline" : "SQL Server 2016 Upgrade Advisor Automation",
  "image" : {
    "@type" : "ImageObject",
    "height" : 69,
    "url" : "https://cdn2.hubspot.net/hubfs/3356718/Imported_Blog_Media/SQL-Server-2016-Upgrade-Advisor-300x69-1.jpg",
    "width" : 300
  },
  "mainEntityOfPage" : "https://blog.coeo.com/andrewjones/2016/08/26/sql-server-2016-upgrade-advisor-automation",
  "publisher" : {
    "@type" : "Organization",
    "logo" : {
      "@type" : "ImageObject",
      "height" : 60,
      "url" : "/hs/hsstatic/content_shared_assets/static-1.4092/img/default-amp-logo.png",
      "width" : 60
    },
    "name" : "The Coeo Blog"
  }
}
```