Friday, May 25, 2012 6:51 PM
Hello, I have been trying to migrate an Access DB (.accdb) to SQL server 2008 and things are failing for various reasons. In one of these migration attempts tables were migrated and SSMA created on the Access side a bunch of SSMA$...$local tables, which seem to be some kind of indirection link tables. However, because the access db was not fully migrated, we decided to undo the operation by converting to local table every linked table. We completed that step and then deleted all the SSMA$...$local link tables and finally we fixed a series of queries where the SSMA$...$local tables had been replaced by the original tables, reverting these to their original expression.
So far, so good, the original access db works and we have now attempted to address some of the original problems that prevented a successful full migration. So, now we are ready to do new attempts of migration, however, when I bring up the SSMA tool, once I add the AccessDB, the Access Metadata Explorer, reports all my "local tables" as "link tables" and refuses to migrate them.
If I try to unlink any of the link tables, I get log messages like the following:
Starting Phase #0
Processing table '[SAMPLETABLE]' in file 'MyAccessDB'... ...
Rolling back the table SAMPLETABLE from the database MyAccessDB...
Table 'SSMA$SAMPLETABLE$local' was not found in the Access database.
Errors: Table 'SSMA$SAMPLETABLE$local' was not found in the Access database.
Rollback operation complete.
Based on the above, it seems that my revert operation was not complete and although the Access db works, there was some state left in there by SSMA which causes the tables to still be perceived as linked.
I have tried several blind techniques to clean that leftover state, like saving the .accdb to another format like .mdb, but the bad state is copied along to the new table. If at all possible I want to avoid going to an old backup because this database is different enough from the starting point of our migration efforts.
So, here is my question: Is there a way, without resorting to a backup, that I can clean up that leftover state such that I can retry migrations?
- Edited by Coffee_fan Friday, May 25, 2012 6:54 PM readability
Monday, May 28, 2012 3:23 AMModerator
Before doing migration the best practice is to make a full back-up of you current database. I would like to suggest restoring the latest one and retrying migration by using SSMA or import and export wizard in SQL Server.
Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
- Marked As Answer by Peja TaoModerator Thursday, May 31, 2012 8:28 AM
Monday, July 30, 2012 1:28 AMDId you ever figure this out? I am having almost the exact same issue except without the errors. Now when I try to migrate SSMA only migrates the newest tables