SQL Data Migration Assistant, what actually happens during migration?

Bill Clark 181 Reputation points
2022-04-12T21:57:43.443+00:00

Migrating in-house SQL 2014 databases to a new, in-house SQL 2019 server. Reading all the documentation I don't see where it explains what happens to the source databases during the migration process. Are they backed up, then taken offline/detached and then copied to the specified shared folder? Then the offline databases stay on the source to be manually deleted or left? Similar question with the logon accounts, are they copied and then left alone on the source server or are they copied, then disabled or deleted? Curious as to what the intended action is before I run any migration using this assistant.

SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
503 questions
0 comments No comments
{count} votes

Accepted answer
  1. Martin Cairney 2,246 Reputation points
    2022-04-12T23:37:02.467+00:00

    There is a combination of BACKUP/RESTORE and scripting.

    As described here, the movement of the databases is by an initial FULL and then subsequent TRANSACTION LOG backups and restores. The source databases are not changed during the process - they can stay online until YOU decide to switch to the new copy.

    Items such as LOGINS and associated permissions are actually scripted out and the scripts executed against the target server. Again, the source is not changed and is there until YOU decide the migration is completed and then you can decommission that server as required.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful