我有一個 SQL Server 2008 R2，我想從 MS Access 遷移資料，但是當我嘗試使用 SSMA 時，選擇 SQL Server 2008 R2 的選項不可用。
請提供任何幫助，SSIS 在我的情況下有用嗎？
uj5u.com熱心網友回復：
好吧，這實際上歸結為您在 access 資料庫中有多少表和多少關系。
例如，您可以在作業站上安裝免費版的 sql server express，然后在本地進行遷移（比如 sql 2017 格式）。
那時，您可以選擇撰寫資料庫腳本 - 更高版本的 SSMS（sql 管理作業室）當然支持 2008。例如：
所以，不要右鍵單擊資料庫并選擇“腳本資料庫為”，而是從任務選單中選擇“生成腳本”。
由此，我們看到 2008 年仍然可以撰寫腳本。
While of course even in the latest version of sql server, you CAN create a older version database. However, when you create a "previous" version of a database (say in the latest edition of sql server, that only limits compatibility features, but DOES NOT permit you to use or make a back up file made to be restored in previous versions of sql server. (sql server kind of nasty that way - you can ONLY go forward with databases - great capability, but once you move, you can't use nor create backup files that can be consumed by previous versions. But you CAN script out to previous versions.
And part of this comes down to how much time you spent using SSMAA.
And if you do go down the above road, then make sure you tweak the column mappings. So for example, by default datetime in access gets converted to datetime2, and I don't quite remember if 2008 supports datetime2. (regardless, I would stick to datetime anyway).
So, even using the latest migration assistant, you can set (tweak) the column mappaings, and it so at most, you need 1 or 2 changes (such as defaulting to datetime in place datetime2 column types).
And getting and setting up sql express local is a great way to test the migration over and over, since often the first few goes might fail, or not produce the resutls you want. This in fact is another HUGE reason to adopt SSMAA, since you can spend a date tweaking and changing some things, and run it again, and even again.
Now, if you script out a whole database (and data), such text files are RATHER large, and in most cases, you can't use the SSMS (sql management studio) to load such a script, so use the command line sqlcmd.exe to import (process) that scripted database you create by using the scripting wizard I outline above. It spits out a "big" text file, and that can be imported into 2008.
I mean, even with 2008 and that era of SSMS, it does have a import option, and you can import from access databases. However, when you do this, the PK's are dropped, indexes are dropped, and so are relatonships dropped.
So, for 2-5 tables - gee, just import using ssms.
However, if you have 40 tables, boatloads of relationships? Then yes, that is a big job, and I would then 100% recommend you use the Access migration assistant tool. (it takes a bit of time to learn - bit confusing at first, but once you pass the learning curve, I high recommend this tool if you wanting to move up many tables, keep the PK settings, and of course keep related data intact.
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/446907.html