0

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.

Yunus Emrah Uluçay
  • 129
  • 9

1 Answers1

1

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

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
Chris Schaller
  • 13,704
  • 3
  • 43
  • 81
  • Thank you for your answer. As you said there is a path problem i faced. But how can i solve that. As you said again i need to create a C:\ .... path but how can i create it in linux ? – Yunus Emrah Uluçay Jun 29 '21 at 06:42
  • No you don't need a `c:\` path, you need to use a path that is local to the database though, I've updated my answer with a linux relative version. – Chris Schaller Jun 29 '21 at 06:56
  • I tried it. But i got an error. I tried to write here the error but it was very long so i put [this photo](https://ibb.co/L0vgMpS) here. What is wrong ? Do i need to put for example etrade.bak file to "/var/opt/mssql/backup/etrade.bak" ? – Yunus Emrah Uluçay Jun 29 '21 at 10:18
  • That screenshot isn't showing the correct error message, that is the response from your first attempt. Make sure the paths that you use are from the perspective of the database itself, it doesn't matter where the file is, as long as the account the DB executes under can access it. – Chris Schaller Jun 30 '21 at 02:31
  • @YunusEmrahUluçay I've updated my post with a query you can run to list the current paths in use. That might help clarify things, the example output in the post is from my test server, your paths will of course be different. – Chris Schaller Jun 30 '21 at 02:49