1

I need to copy this Ms. Access database that we have to test it in SQL Server 2008 R2, is the Import\Export wizard will let me copy all tables, queries, forms... without affecting the live database? I mean once I click "Finish" at the end of the wizard, is this just moving everything from origional data source or just copying it?

Any links that I should check on what kind of obstacles that I may face with large databases, much appreciated.

1

There are several good options for migrating data from Microsoft Access into SQL Server:

  1. SQL Server Migration Assistant

    SQL Server Migration Assistant for Microsoft Access provides a reliable method for migrating database objects from Access into SQL Server. Optionally, it provides a way to link SQL Server tables into an Access back-end to enable you to continue to use an Access-based front-end with the SQL Server back-end data.

  2. Import/Export Data Wizard in SQL Server Management Studio

    Importing data via SQL Server Management Studio is a straight-forward method for copying data from an Access database into a SQL Server database. Running the wizard allows you to pick an Access database, then choose individual objects in Access to import into SQL Server. Tables and Views (queries) can be copied from Access into SQL Server. The source data is not modified in any way.

  3. Microsoft Access Upsizing Wizard.

    The Upsizing Wizard moves database objects and the data they contain from an Access database to a new or existing SQL Server database.

    There are three ways to use the Upsizing Wizard:

    • Upsize all database objects from an Access database to an Access project so that you can create a client/server application. This approach requires some additional application changes and modification to code and complex queries.

    • Upsize only data or data definitions from an Access database to a SQL Server database.

    • Create an Access database front-end to a SQL Server database back-end so that you can create a front-end/back-end application. This approach requires very little application modification since the code is still using the Access database engine (ACE).

If you are simply importing one or two tables, it is likely easiest to use the Import Data wizard in SSMS (option 2, above), whereas if you are attempting to migrate an entire application's data into SQL Server, I'd recommend using the SQL Server Migration Assistant (option 1, above).

For very large databases, migration can take a significant amount of time, and can use significant temporary space in SQL Server; ensure you have enough free space, at least temporarily, on the SQL Server to store 2 copies of your Access data. You'll want to plan to prevent users from updating the data in the Access database while doing the migration, and directly afterwards, in order to ensure the data stored in SQL Server is the most current version. Do this migration during a period of planned outage. Plan the outage based on tests you perform of this migration in a test environment.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.