Migration Access to SQL server 2012

Question

For an project, I need to migrate an Access database to SQL server 2012 express, I found some ways to do this, like using Microsoft up-sizing, but this question is not very technical but just to find out alternative ways, the database in access contains confidential data, so im not allowed to see data and I want to find out is there is any procedure that can be useful for me to achieve migration with out seeing the database itself and how to verify the data is exact in both. I don't have database background so specific directions and resources is useful. I don't know how to search for specific methods(for migration) so throw me some methods so i can continue my research.(

DB: Access 2007 target: SQL server express 2012. IS there is any tool in SQL server 2012 to achieve this? How to check the data(tables, query, relationships, data itself are migrated correct and how to handle data format issues) without seeing the data (not allowed) Im looking for an automatic way.

Thanks for the help and consideration

Answer 1

Microsoft provides a migration assistant for Microsoft Access 97 and higher to SQL Server 2005-2012 and Azure. See http://www.microsoft.com/en-us/download/confirmation.aspx?id=28763.

Answer 2

Create a database on the SQL 2012 instance. Right click on that database and choose tasks/import data. Choose an access database as the source. edit the mappings so that it fits what you like, and import. The easiest way by far.

Answer 3

When You want to create/Edit a database diagram or Edit Table from a SQL SERVER 2008 R2 Client(SSMS 2008) to a SQL SERVER 2012 Instance, You will get the error shown as given below:

This backend version is not supported to design database diagrams or tables. (MS Visual Database Tools)

It seems that SQL Server 2008 R2 does not support SQL Server 2012 Database Diagram Table Modify. Solution: install SQL SERVER 2012 to access the functionality from the link below

http://www.microsoft.com/en-us/download/details.aspx?id=29062

Answer 4

1. SQL server Migration Assistant is helpful.
2. SQL server Import Data selecting Source as MS Access(MS Jet Database engine) And selecting target as SQL Server works with mapping for individual table columns if required. I have tried both and it works.