How to import .bak file to using mssql on vscode?

Question

I use Linux. But I work on sql, mssql. So i only find a way to use mssql on linux with vscode's sql extension. So in this extension i can find how to create db or table etc. But i can not find how to import an exist db. How can i handle this? Thanks in advance.

Answer 1

Restoring databases in MS SQL is covered extensively in the documentation and even on SO:

• How to: Restore Files and Filegroups (Transact-SQL)
• RESTORE Statements (Transact-SQL) - SQL Server
• Import .bak file to a database in SQL server
• Migrate a SQL Server database from Windows to Linux using backup and restore

If you are using vscode then you are only looking for the SQL syntax/commands to complete the task, so ignore the solutions that use management studio and the UI.

The complicating factor for many in SQL Backup/Restore operations is that the paths that you specify in the SQL commands to perform the operations is relative to the server, NOT to your workstation where you are executing the command from.

So the first step is to copy the backup file to a location that the database engine has file system level access to, then use that path in the SQL scripts.

This is an example:

RESTORE DATABASE YourDB
FROM DISK = '/var/opt/mssql/backup/YourDB.bak'
WITH MOVE 'YourDB' TO '/var/opt/mssql/data/YourDB.mdf',
MOVE 'YourDB_Log' TO '/var/opt/mssql/data/YourDB_Log.ldf'

But read through the docs for the specific restoration sequence and parameters for your needs.

---

If you are unsure of the current filepaths in use, you can query them from the database:

SELECT 
    MDF.database_id, 
    MDF.name, 
    MDF.physical_name as data_file, 
    LDF.physical_name as log_file, 
    db_size_mb = CAST((MDF.size * 8.0)/1024 AS DECIMAL(8,2)), 
    log_size_mb = CAST((LDF.size * 8.0 / 1024) AS DECIMAL(8,2))
FROM (SELECT * FROM sys.master_files WHERE type_desc = 'ROWS' ) MDF
JOIN (SELECT * FROM sys.master_files WHERE type_desc = 'LOG' ) LDF ON MDF.database_id = LDF.database_id

database_id	name	data_file	log_file	db_size_mb	log_size_mb
1	master	C:\Program Files\Microsoft SQL Server\MSSQL15.TEST\MSSQL\DATA\master.mdf	C:\Program Files\Microsoft SQL Server\MSSQL15.TEST\MSSQL\DATA\mastlog.ldf	5.38	2.00
2	tempdev	C:\Program Files\Microsoft SQL Server\MSSQL15.TEST\MSSQL\DATA\tempdb.mdf	C:\Program Files\Microsoft SQL Server\MSSQL15.TEST\MSSQL\DATA\templog.ldf	8.00	8.00
3	modeldev	C:\Program Files\Microsoft SQL Server\MSSQL15.TEST\MSSQL\DATA\model.mdf	C:\Program Files\Microsoft SQL Server\MSSQL15.TEST\MSSQL\DATA\modellog.ldf	8.00	8.00
4	MSDBData	C:\Program Files\Microsoft SQL Server\MSSQL15.TEST\MSSQL\DATA\MSDBData.mdf	C:\Program Files\Microsoft SQL Server\MSSQL15.TEST\MSSQL\DATA\MSDBLog.ldf	19.69	28.81
5	MyApp	D:\SQL Server\MyApp.mdf	L:\SQL Server\MyApp_log.ldf	392.00	19912.00