DBeaver restore SQL Server .bak file

Question

I am just trying to restore a SQL Server .bak file in my DBeaver UI. But I have no idea how to do this - can someone help please?

I created a database, but when I right click on it, there are no restore options.

Quan

I don't know DBeaver - but in SQL Server Management Studio, the approach is to click on the Databases node and click on "Restore" - and not create a separate database at all, the database will be created by the restore operatoin – Tiertza

it is in the task category – Kelci

Answer 1

This answer is for someone who has their sql server running on docker desktop and they are using dbeaver Community edition.

step 1. copy your .bak file from your mac to the docker container. This is because your mac and the docker are treated as two different machines. For this step you need to know your docker container ID. You can find it by running

docker ps

on your terminal. Once you get your container ID, run

docker cp '/Users/yourname/Downloads/mydb.bak' cotainerID:/var/backups

please replace the path locations to what applies for you.

step 2. go to DBeaver SQL script window, run

RESTORE FILELISTONLY FROM DISK = N'/var/backups/mydb.bak'

This should list the names of the databases the backup file contains. You need the exact names of the databases for the final step.

step 3. copy the db names (logical names) from the table below the script window in Dbeaver. Then run this

RESTORE DATABASE mydb FROM DISK = N'/var/backups/mydb.bak' WITH MOVE 'mydb' TO '/var/opt/mssql/data/mydb.mdf' , MOVE 'mydb_log' TO '/var/opt/mssql/data/mydb_log.ldf'

please replace the paths and database names to what applies for you. This basically restores the db from the backup file.

Once you refresh your connection to the server on the dbeaver UI, you should be able to see your restored db. Hope it helps.

Maribeth

Answer 2

I do not see that option either in my version of DBeaver (6.1.3 - community). What you could do is initiate a restore with TSQL (by clicking on SQL Editor while you are connected to your database). Then in the Script area, use the below, modified for your own environment/database name.

USE [master] RESTORE DATABASE [DatabaseName] FROM 
DISK = N'C:\FolderName\BackupName.bak' WITH  FILE = 1, NOUNLOAD, REPLACE, STATS = 10

For full Tsql options to restore a database, see this: RESTORE Statements (Transact-SQL)

Spheroidicity

Answer 3

In my case the database migration was done on windows environment so there was some issue restoring backup in Ubuntu 18.04 which was fixed from below command.

• backup file path: /var/opt/mssql/data/
• move file path: /var/opt/mssql/data/

Restoring database backup in Ubuntu using DBeaver

RESTORE DATABASE MYDB_API FROM DISK = N'/var/opt/mssql/data/mydb.bak'
WITH MOVE 'MYDB_API' TO '/var/opt/mssql/data/mydb.mdf'
, MOVE 'MYDB_API_log' TO '/var/opt/mssql/data/mydb_log.ldf'
REPLACE

Note: if above does not work for you then remove REPLACE from above and make sure MYDB_API does not exist on SQL Server.

Manutius

It's work for me, thank! – Attis

thanks, it helps me a lot at midnight :) – Convocation

Answer 4

It looks like you are using a macOS which is a system not supported by SQL Server Management Studio (SSMS).

So I assume you need to restore SQL Server database with .bak file under macOS GUI.

There is no GUI option to retore .bak file with DBeaver.

If you want absolutely a restoration with GUI and not TSQL, an alternative on macOS is Azure Data Studio (from Microsoft) which is an open source data management tool that runs on Windows, macOS, and Linux.

You can easily restore a bak file in GUI with Azure Data Studio.

Here is the tutorial: https://learn.microsoft.com/en-us/sql/azure-data-studio/tutorial-backup-restore-sql-server

Restore a database from a backup file (With Azure Data Studio)

1. Open the SERVERS sidebar (CTRL+G), right-click your server, and select Manage.

2. Open the Restore database dialog (click Restore on the Tasks widget).

3. Select Backup file in the Restore from field.

4. Click the ellipses (...) in the Backup file path field, and select the latest backup file for TutorialDB.

5. Type TutorialDB_Restored in the Target database field in the Destination section to restore the backup file to a new database.

6. Click Restore

7. To view the status of the restore operation, press CTRL+T to open the Task History sidebar.

Bixler

I believe this solution doesn't really work on MacOS, so if you end up here trying to restore BAK files into your mssql database, ignore this answer – Chomp