VistaDB is a great option for an embedded database for your application. But if you ever want to migrate your VistaDB database to SQL Server you will quickly come to the discovery that there are no clear guides on how to achieve this. This guide will walk you through all the steps of fully converting a VistaDB database to your SQL Server instance:

1. Pre-requirements for the migration.
2. Exporting the SQL schema from VistaDB.
3. Importing the SQL schema into SQL Server.
4. Migrating the data within the tables.

1. Pre-requirements for the migration

For this migration to work you'll need a working SQL Server instance, SSMS and VistaDB Data Builder (which comes with VistaDB itself).

I also recommend you to create a new database in SQL Server which will be used for this guide and upgrade your VistaDB database to the latest version using the VistaDB Data Builder upgrade tool. You can find a guide on how to execute an upgrade here.

2. Exporting the SQL Schema from VistaDB

To migrate your database you are going to need the data schema from your existing VistaDB database. You can export this using the VistaDB Data Builder tool.

Open your VistaDB database in the Data Builder tool, open the "SQL Script" menu and click "Generate Schema Deployment Script". Then select the "SQL File (*.sql)" file type to save it as a SQL deployment script.

3. Importing the SQL schema into SQL Server.

Now you can deploy the exported SQL schema into your SQL Server database. The easiest way to achieve this is to open up SSMS and connect with your SQL Server instance. Then open up the database you want import the data into and open the SQL Schema script in SSMS.

Make sure that your database is selected in the top left of SSMS and then run the SQL Schema script.
You can see in the "Messages"-tab when the query is done running.

4. Migrating the data within the tables.

Because you have VistaDB installed on your computer, you will also be able to connect to existing VistaDB databases using SSMS and the installed driver.

Select the database on which you deployed the SQL schema and right-click it.
From there on out select "Tasks" and then "Import Data".

The import wizard:

1. Click "Next" on the welcome page.
2. Select "VistaDB" as your Data Source and click "Data Source" under "Misc" to select your VistaDB database file and then click "Next".
3. Select "Microsoft OLE DB Provider for SQL Server" as your Destination and fill in the information to connect to your SQL Server database.
4. Select "Write a query to specify the data to transfer" and click "Next".
5. Type in the query to select your required data from the VistaDB database. For example SELECT * FROM my_test_table; and click "Next".
6. Click "Destination" and select the table to which the data should be copied and click "Next".
7. Click "Next" again and then "Finish". The import should now run.
8. Make sure that the data is imported correctly by reading the logs.
   

You can repeat this process for all the tables in your database.

Finishing up

After migrating all tables you can change your database configuration in your application or code to start using the SQL Server database! You have now successfully migrated/copied your data from VistaDB's embedded database to a fully developed SQL Server database.