SQL Server Migration Assistant Problem Solved
Having used SQL Server Migration Assistant for a major Oracle conversion project over the last year, I quickly grew aggravated with its project limitation involving a single database. In other words, if you setup the project file in SSMA for staging and then you are unable to point it to an acceptance environment to migrate to that environment. That can be very frustrating especially if you have customized many of the project settings. I have discovered two workarounds to resolve this problem.
- Backup (or copy) the project folder (c:\Users\<<Username>>\Documents\SSMAProjects\<<Project Name>>). Close SSMA and then delete the target-metabase.mb file in the project folder. Now open SSMA and now you can connect to a new SQL Server and/or database. You would want to copy the folder if you want to retain the settings for a different environment such as staging, acceptance, production, etc. If you rename the folder for a different environment, make sure to rename the *.o2ssproj file to match the folder name. This is the file that opens inside of SSMA.
- The second option is to create a new project using SSMA and then copying in the object-containers.mappings, project-container.mappings, and finally preferences.prefs. These are the project settings that you have painstakingly setup in your original project.
I hope this helps. Enjoy!