If you are upgrading your instance to 2016 (or 2017 soon) then you probably are going to want to run the DMA (Database Migration Assistant). If you are doing a BUNCH of upgrades at once (say 100 instances or something) then you probably don’t want to use the GUI. Fortunately, there is a command line version. Even better you can quickly and easily generate the command line statements to create a CSV of the DMA output for each database (I prefer them separately rather than all in one big file).

SELECT name, '"C:\Program Files\Microsoft Data Migration Assistant\DmaCmd.exe" ' + 
'/AssessmentName="DMA_Output" ' +
'/AssessmentDatabases="Server=' + @@ServerName +
';Initial Catalog=' + sys.databases.name +
';Integrated Security=true" ' + 
'/AssessmentEvaluateCompatibilityIssues /AssessmentOverwriteResult ' + 
'/AssessmentResultCSV="\\PathToSaveTo\'+REPLACE(@@ServerName,'\','_')+'\'+sys.databases.name+'.CSV"' +
' > "\\PathToSaveTo\'+REPLACE(@@ServerName,'\','_')+'\'+sys.databases.name+'.LOG"'
FROM sys.databases WHERE state <> 6 -- exclude offline databases
  and database_id > 4 -- Exclude system databases

A couple of important points.

• I’m assuming that your install of the DMA is in the C:\Program Files\Microsoft Data Migration Assistant directory.
• I have the output going to the URL \\PathToSaveTo\ServerName. Obviously, you will need to replace the PathToSaveTo part to reflect where you want the output to go.
• And last but not least that last line can be removed if you want. It puts the output of the DMACMD (which is pretty verbose) into a log file just in case.
• Oh, and one more last but not least. The URL for the log file is also a \\PathToSaveTo\ so if you keep it you’ll need to change that one too.

This link has the breakdown of the parameters for the DMACMD in case you have different preferences. JSON output for example.

Filed under: Dynamic SQL, Microsoft SQL Server, SQLServerPedia Syndication, Upgrades