How to convert VistaDB embedded database to MS SQL Server

This needs to be done manually.

1. First, you need to create an empty database in SQL Server and then you need to create the schema of the database (tables vs.). For creating the schema, I recommend letting FileVista do this for you. I am not pasting the full sql script here as the script can change from version to version so it's better to let FileVista create it automatically. Open App_Data\FileVista.config file and empty the value of this setting:

 <DbConnectionString>Data Source=|DataDirectory|FileVista.vdb5;  Open Mode=NonExclusiveReadWrite</DbConnectionString>

like this:

 <DbConnectionString></DbConnectionString>

For older versions:

 <add key="ConnectionString" value="Data Source=|DataDirectory|Database/FileVista.vdb5;  Open Mode=NonexclusiveReadWrite" />

like this:

 <add key="ConnectionString" value="" />

and save the file. When this value is empty, it will force FileVista to run Configuration Wizard (configuration.aspx). You may also need to touch (open/save) Web.config file so the change is recognized. Now when the configuration wizard is opened, in step 2 choose your SQL Server database and enter correct connection details, then click Next. It will tell you if the connection is successful and create the database structure automatically behind the scenes. Now do not complete the last step where it asks for admin user name etc. We need the database to be empty so leave it as it is for now.

2. You will now need to import data from your existing VistaDB database (.vdb6, .vdb5 or .vdb4 file). You will use SQL Server Management Studio for this but first you need VistaDB provider installed on your machine. Download a trial version of VistaDB and then install it on the machine that you have the SQL Server Management Studio. This will install and register the necessary provider for you.

For .vdb5 file download and install VistaDB-6.4.2.exe
For .vdb5 file download and install VistaDB-5.8.7.exe
For .vdb4 file download and install VistaDB-4.3.3.34.exe

After you complete the import, uninstall VistaDB trial version from your machine.

3. In SQL Server Management Studio, you will need to repeat this step several times:

Right click the empty FileVista database that you have created in first step and then click Tasks -> Import Data in the opened menu.
Now in the opened wizard, choose VistaDB X ADO.NET Provider for .Net 4 as the data source (provider) and then below choose the VistaDB database file (.vdb6, .vdb5 or .vdb4 file) to be imported (in Misc -> Data Source field) and click Next
Choose the destination as your empty FileVista SQL Server database and click next.
Choose Write a query to specify the data to transfer option and click Next
Now paste the below sql statement:
```
select * from [User]
```
We can not enter all the sql statements at once as this wizard does not allow it. So we have to do it one by one. Click Next after you enter the statement.

Now choose the corresponding table in Destination column. For example, for the above query, you should select [dbo].[User]. Then click Edit Mappings button check Enable identity insert option.
Now after you click Next and then Finish, this wizard will import the table (in this case User table). However you need to repeat this step (step 3) for each table, below are all the remaining sql statements that you need to enter each time (in this exact order):
```
select * from [Group] select * from [RootFolder] select * from [Membership] select * from [AccessControlList] select * from [AccessControlEntry] select * from [Setting] select * from [PublicLink] select * from [Event]        
```

Article ID: 33, Created: 10/19/2024 at 9:22 PM, Modified: 10/19/2024 at 9:25 PM

How to convert VistaDB embedded database to MS SQL Server

Was this article helpful?

Share this article