SQL Server is a versatile database system being used world widely. Customers switching from their current database system to SQL Server face the issue of migrating data from existing system to the new database. Hence SQL Server facilitates this requirement to import data from several different sources. In this tutorial, we will learn how to convert an access database to SQL Server database.
Step#1: SQL Server Migration Tool
SQL Server provides a migration tool called SQL SERVER MIGRATION ASSISTANT to facilitate the customers to import data from different sources. You can download it from Microsoft’s official website. Simply click on the download button and specify the location where you wish to save it. Next, extract and open up the setup file to begin the installation.
Just follow the step by step procedure to install and the application. In case if it prompts you for the license key, you can easily download it for free by registering yourself to Microsoft.
Step#2: Initializing Migration Process
Once you open up the migration tool, a wizard will open up. Just hit next on the welcome screen and create a new project. Here you can name your project, specify the location and then choose the version of SQL SERVER you want to migrate the database to. In our case, we will convert Access to SQL Server 2008.
Step#3: Adding Access Database
To convert Access to SQL, we need to add the database here. Click the ADD DATABSE button and locate your database file. Press NEXT to continue.
Step#4: Defining SQL Server
Next, select the objects to migrate. For this tutorial, let’s leave this to default and hit NEXT, since we will be migrating the whole database.
In the next window, we will have to create a connection with the SQL SERVER. Provide the Server’s name, which in this case will be local since it is installed on the same machine, and specify the database name. If you want to create a new database, just type its name over here and the wizard will automatically create it. Click on NEXT to continue.
Step#5: Migrating Access Database
The conversion process will begin, and it would take a few seconds for the processing to complete.
Step#6: Verification of Data Migration
Once completed, close the migration wizard and open up the SQL server. You will notice a new database has appeared over here. Expand the folder, right click on any of its table and choose the “select top 1000 rows” option to verify the contents of the database.
Finally, we have successfully shown in this tutorial how to convert an access database to SQL Server database.