Dynamically generate the command line DMA statement for each database

Leave a comment

June 21, 2017 by Kenneth Fisher

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,135 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: