Running Upgrade Advisor (Command Prompt)

Article
08/20/2010

Use the UpgradeAdvisorWizardCmd utility to run Upgrade Advisor from the command prompt. You can choose to receive results in XML format or in a file with comma-separated values.

Syntax

UpgradeAdvisorWizardCmd [ -? ]  | 
    [ -ConfigFilefilename | <server_info> ]
    [ -SqlUserlogin_id-SqlPasswordpassword ]
    [ -NsSqlUserlogin_id-NsSqlPasswordpassword ]
    [ -CSV ]

where <server_info> is any combination of the following:
        -Serverserver_name-Instanceinstance_name-ASInstance AS_instance_name-RSInstance RS_instance_name-NSInstanceNS_instance_name

Arguments

-?
Displays the command syntax.
-ConfigFilefilename
Is the path name and file name of an XML file that contains settings to use when you run the UpgradeAdvisorWizardCmd command.
<server_info>
Specifies which computer and instance to analyze. Use these options if you are not using a configuration file.

<server_info> can be any combination of the following three arguments:
- -Serverserver_name
  Specifies the name of the computer to analyze. This can be the local computer, which is the default value, or a remote computer.
- -Instanceinstance_name
  Specifies the name of the instance to analyze. There is no default value. If you do not specify this parameter, the Database Engine is not scanned. The value for a default instance of SQL Server is MSSQLSERVER. For a named instance, use the instance name.
- -NSInstanceNS_instance_name
  Specifies the name of the instance of Notification Services to analyze. There is no default value. If you do not specify this value, Notification Services is not analyzed.
  
  Note
  
  If you are running Upgrade Advisor on your SQL Server 2008 instance, the -NSInstance parameter is not applicable as SQL Server 2008 does not have notification services.
- -ASInstance AS_instance_name
  Specifies the name of the instance of Analysis Services to analyze. There is no default value. If you do not specify this value, Analysis Services is not scanned. The value for a default instance of Analysis Services is MSSQLServerOLAPService. For a named instance, use the instance name.
- _RSInstance RS_instance_name
  Specifies the name of the instance of Reporting Services to analyze. There is no default value. If you do not specify this value, Reporting Services is not scanned. The value for a default instance of Reporting Services is ReportServer. For a named instance, use the instance name.
-SqlUserlogin_id
If you are using SQL Server Authentication, this value is the SQL Server login that Upgrade Advisor will use to connect to the instance of SQL Server. If you do not specify a login, Windows Authentication is used to connect to the SQL Server instance.
-SqlPasswordpassword
If you use the -SqlUser argument, use this argument to specify the password for the SQL Server login.
-NsSqlUserlogin_id
If using SQL Server Authentication, this value is the SQL Server login that Upgrade Advisor will use to connect to the instance of Notification Services by using SQL Server. If you do not specify a login, Windows Authentication is used to connect to the Notification Services instance.
-NsSqlPasswordpassword
If you use the -NsSqlUser argument, use this argument to specify the password for the SQL Server login.
-CSV
Specifies to provide results as comma-separated values to a .csv file in addition to the standard XML results. Results are written to the My Documents\SQL Server 2008 R2 Upgrade Advisor folder.

Return Values

The following table shows the values that UpgradeAdvisorWizardCmd returns:

Value	Description
0	Analysis succeeded, no upgrade issues found.
positive integer	Analysis succeeded, upgrade issues found.
negative integer	Analysis failed.

Remarks

All information that is required to run the analysis, other than SQL Server Authentication user names and passwords, can be provided in an XML configuration file. This XML configuration file is documented in the template. If you do not use a configuration file, you can analyze all installed components in an instance of SQL Server using default settings by specifying computer names and instance names. See the "Element Descriptions" table later in this topic for a description of the default configuration file settings.

Configuration File Template

Use the following XML as a template for creating your own configuration files. You can modify the template to meet the needs for your organization.

<Configuration>
    <Server> </Server>
    <Instance></Instance>
    <Components>
        <SQLServer>
            <Databases>
                <Database></Database>
            </Databases>
            <TraceFiles>
                <TraceFile></TraceFile>
            </TraceFiles>
            <BatchFiles>
                <BatchFile></BatchFile>
            </BatchFiles>
            <BatchSeparator></BatchSeparator>
        </SQLServer>
        <AnalysisServices>
            <ASInstance></ASInstance>
            <Databases>
                <Database></Database>
            </Databases>
        </AnalysisServices>
        <ReportingServices>
            <RSInstance></RSInstance>
        </ReportingServices>
        <DataTransformationServices>
            <PackagePath></PackagePath>
        </DataTransformationServices>
        <IntegrationServices>
            <PackagePath></PackagePath>
        </IntegrationServices>
        <NotificationServices>
            <NSInstance></NSInstance>
        </NotificationServices>
    </Components>
</Configuration>

Element Descriptions

Tag	Definition	Occurrence
Configuration	Parent element for Upgrade Advisor configuration file.	Required once per configuration file.
Computer	Name of the computer to analyze.	Optional once per configuration file. The default value is the local computer.
Instance	Name of the Database Engine instance to analyze.	Optional once per configuration file. The default value is the default instance.
Components	Contains elements that specify which components to analyze.	Required once per configuration file.
SQLServer	Contains analysis settings for an instance of the Database Engine.	Optional once per configuration file. If not specified, Database Engine databases are not analyzed.
Databases for SQLServer element	Contains a list of databases to analyze.	Optional once per SQLServer element. If this element is not present, all databases in the instance will be analyzed.
Database for SQLServer element	Specifies the name of a database to analyze.	Required once or more if the Databases element is present. If a Database element contains the value "*", all databases in the instance will be analyzed. There is no default value.
TraceFiles	Contains a list of trace files to analyze.	Optional once per SQLServer element.
TraceFile	Specifies the path and name of a trace file to analyze.	Required once or more if the TraceFiles element is present. There is no default value.
BatchFiles	Contains a list of batch files to analyze.	Optional once per SQLServer element.
BatchFile	Specifies a batch file to analyze. Can be multiple.	Required once or more if the BatchFiles element is present. There is no default value.
BatchSeparator	Specifies the batch separator used in your SQL Server batch files.	Optional once per SQLServer element. The default value is GO.
AnalysisServices	Contains analysis settings for Analysis Services.	Optional once per configuration file. If not specified, Analysis Services databases are not analyzed.
ASInstance	Specifies the name of an instance of Analysis Services.	Required once per AnalysisServices element. There is no default value.
Databases for Analysis Services element	Contains a list of databases to analyze.	Optional once per AnalysisServices element. If this element is not present, all databases in the instance will be analyzed.
Database for AnalysisServices element	Specifies the name of a database to analyze.	Required once or more if the Databases element is present. If a Database element contains the value "*", all databases in the instance will be analyzed. There is no default value.
ReportingServices	Specifies to run analysis against Reporting Services.	Optional once per configuration file. If not specified, Reporting Services is not analyzed.
RSInstance	Specifies the name of an instance of Reporting Services.	Required once per ReportingServices element. There is no default value.
DataTransformationServices	Contains analysis settings for Data Transformation Services (DTS).	Optional once per configuration file. If not specified, DTS is not analyzed.
IntegrationServices	Contains analysis settings for SQL Server Integration Services.	Optional once per configuration file. If not specified, Integration Services is not analyzed.
PackagePath	Specifies the path of a set of DTS or Integration Services packages.	Optional once per DTS or Integration Services element. If this element is not present, analysis will occur on the SQL Server instance, and no externally-stored packages will be analyzed. There is no default value.
NotificationServices	Contains analysis settings for Notification Services.	Optional once per configuration file. If this element is not present, Notification Services is not analyzed.
NSInstance	Specifies the name of a Notification Services instance.	Required once per NotificationServices element. There is no default value.

Examples

A. Run Upgrade Advisor Using a Configuration File

The following example shows how to run Upgrade Advisor from the command line by using a configuration file. This specifies what to analyze. This example uses Windows Authentication to connect to SQL Server.

UpgradeAdvisorWizardCmd -ConfigFile "C:\My Documents\UpgradeConfig1.xml"

B. Run Upgrade Advisor Using Default Configuration Settings

The following example shows how to run Upgrade Advisor from the command line by using default configuration settings and Windows Authentication. This command will analyze all components except Notification Services because the command does not specify a Notification Services instance name.

UpgradeAdvisorWizardCmd -Server MyServer -Instance MyInst

C. Run Upgrade Advisor Using SQL Server Authentication

The following example shows how to run Upgrade Advisor from the command line by using a configuration file. This example specifies a SQL Server user name and password to connect to the instance of SQL Server.

UpgradeAdvisorWizardCmd -ConfigFile "C:\My Documents\UpgradeConfig1.xml" 
    -SqlUser "MyUserName" -SqlPassword "QweRTy-55"

D. Analyzing a Notification Services Instance and Receiving CSV Output

The following example shows how to analyze an instance of Notification Services by using SQL Server Authentication, generating comma-separated output that shows the results of the analysis:

UpgradeAdvisorWizardCmd -NSInstance MyNSInst -NSSqlUser "MyUserName"
    -NSSqlPassword "QweRTy-55" -CSV