FilesExpand file tree

 master

/

DynamicallyGenerateDMAStatement.sql

Copy path

Blame

More file actions

Blame

More file actions

Latest commit

 

History

History

History

17 lines (15 loc) · 845 Bytes

 master

/

DynamicallyGenerateDMAStatement.sql

Top

File metadata and controls

• Code
• Blame

17 lines (15 loc) · 845 Bytes

Raw

Copy raw file

Download raw file

Open symbols panel

Edit and raw actions

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

/*

Author: Kenneth Fisher

Original link: https://sqlstudies.com/2017/06/21/query-to-run-command-line-dma-on-each-database

*/

-- Dynamically generate the command line DMA statement for each database

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