1

I have a .sql file containing code to create a database. This file has been created using Sequel Pro SQL dump Version 4096, a software running on Mac. However, I am on Windows and I just can't find out how to run this file successfully.

I have tried with SQLite 3 and DBeaver 7.1.0 without success.This post suggest to use the same software: "So to be certain, you need to run the script in the same database engine software that was used to create it" but as you can imagine this is not possible here.

Do you know a software that could open it and allow me to save this .sql file in another format (at least something BDeaver can read...)

The dataset is open source, available here: https://datadryad.org/stash/dataset/doi:10.5061/dryad.mf42f To make this example fully reproducible, what should I include?

This is my 1st time dealing with .sql files and databases' world. Please let me know if I informations are lacking or if I am doing things wrong.

Paul
  • 2,850
  • 1
  • 12
  • 37

1 Answers1

2

A .sql file is just plain text file with a different extension. So normally, you can open it with Notepad or any text editor such as Geany, Atom or Notepad++.

If you wish to execute the SQL script, in DBeaver, you first have to create the connection to the target database. Then, in the database navigator on the left, right-click on your database and select "SQL editor". A new empty SQL editor opens, linked to your database. Then click on "SQL Editor" in the top bar, and click on "import SQL script":

Import SQL script menu in DBeaver

Navigate to the .sql file and select it.

Just in case, DBeaver also exists on Mac so if you need to use several OS it might be convenient to install DBeaver on all your computers and so keep the same tool everywhere.

Steps to follow to create a database from a .sql file

If you have an SQL script to create mySQL database, it will not run fine in a SQLite or any other database. For example, mySQL CREATE TABLE would mention something like ENGINE=InnoDB. SQLite will not understand this option. And it is generally true : SQL script are very similar from one database system to another, but not exactly identical. So unfortunately not interchangeable.

So to transfer your database from one computer (let's say the "source") to another (let's say the "target"), you need first to "dump" it i.e. to put all SQL instructions to create it into one file. As I understand you have already done it.

Then you will have to reinstall the same database engine (so mySQL in your case I guess) on the target computer. Then connect DBeaver (or whatever client : mySQL Workbench, Datagrip, even command line...) to this database and execute the script as shown above.

The SQL script is NOT the database, it is just the steps to create it. The database engine will read your SQL script and recreate your database accordingly.

FloT
  • 745
  • 1
  • 7
  • 17
  • 1
    Many thanks for your tutorial. I have beend through the steps but the script still won't run... It keeps running into many `SQL Error: Syntax error: `. This is why I thought it was needed to use the original software. The .sql file I am using has commands to create a database from scratch so I thought it was not necessary to connect it to an existing database. – Paul Jun 17 '20 at 09:11
  • Well, you are correct, but the "original software" in this case rather refers to the database engine (mySQL, SQLite...) than to the database client i.e. the software used to connect to it (DBeaver, Sequel Pro...). I have edited my answer to (I hope) further help you. – FloT Jun 17 '20 at 09:52
  • 1
    Your explanation is so clear! I will try to make it work with it. Many many thanks :) – Paul Jun 17 '20 at 10:47