SQL Server Migration Assistant Problem Solved

Risk the Game of Global DominationHaving 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.

  1. 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.
  2. 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!

Advertisement

About SQLGator

Microsoft Data Platform MVP, Florida Gator, Star Wars fanatic and is there anything else...oh yeah PS4! I am a geek and SQL Server Business Intelligence Consultant, there are other technologies greater than these? Not so fast my friend! I also love to travel to new and exotic places.

Posted on July 22, 2013, in Maintenance and tagged , . Bookmark the permalink. 1 Comment.

  1. Thanks for sharing. Exactly what I needed to get SSMA to “refresh” the SQL Server target list.

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 )

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: