How to backup and restore MSSQL Server database in Ubuntu

Dung Do Tien Mar 23 2021 277

I have a database MSSQL in Linux Ubuntu. How can I back up and restore it to my server?

I usually open port publish and I can access it anywhere from the local computer with the MSSQL Management tool. But my server often down because someone attacks my database. So now I want to backup DB to some folder in the server and I can download that file anytime.

Thanks for any answer.

Have 1 answer(s) found.
  • N

    Nam Vo Mar 23 2021

    We can use sqlcmd command to help backup & restore a database.

    1. To backup a database

    Use the syntax below:

    sqlcmd -S localhost -U SA -Q "BACKUP DATABASE [DB_Name_Existed] TO DISK = N'/var/opt/mssql/data/[DB_Name_File].bak' WITH NOFORMAT, NOINIT, NAME = '[DB_Name_Full]', SKIP, NOREWIND, NOUNLOAD, STATS = 10"

    In that:

    [DB_Name_Existed] : Its name of your database exists in your server and you want to backup.

    [DB_Name_File] : Backup database file name.

    [DB_Name_Full]: Database name of .bak file when you restore. It should same with [DB_Name_Existed]

    For example:

    sqlcmd -S localhost -U SA -Q "BACKUP DATABASE [TestBackupdateDb] TO DISK = N'/var/opt/mssql/data/TestBackupdateDb.bak' WITH NOFORMAT, NOINIT, NAME = 'TestBackupdateDb', SKIP, NOREWIND, NOUNLOAD, STATS = 10"

    And below is the result of the process:

    Password:
10 percent processed.
21 percent processed.
32 percent processed.
40 percent processed.
51 percent processed.
61 percent processed.
72 percent processed.
80 percent processed.
91 percent processed.
Processed 296 pages for database 'demodb', file 'demodb' on file 1.
100 percent processed.
Processed 2 pages for database 'demodb', file 'demodb_log' on file 1.
BACKUP DATABASE successfully processed 298 pages in 0.064 seconds (36.376 MB/sec).

    2. To restore a database

    Use the syntax below:

     sqlcmd -S localhost -U SA -Q "RESTORE DATABASE [DB_Name] FROM DISK = N'[Path .bak file]' WITH FILE = 1, NOUNLOAD, REPLACE, NORECOVERY, STATS = 5"

    In that:

    [DB_Name] : Name of the database will be created.

    [Path .bak file] : Link path .bak file existed in your server.

    For example:

    sqlcmd -S localhost -U SA -Q "RESTORE DATABASE [TestBackupdateDb] FROM DISK = N'/var/opt/mssql/data/TestBackupdateDb.bak' WITH FILE = 1, NOUNLOAD, REPLACE, NORECOVERY, STATS = 5"

    I hope this info helpful for you.

Related Q&A May You Like

  1. MySql throw no database selected, select the default DB to be used by double-clicking its name
  2. How to generate whole sql database script in MySql Workbench
  3. Error querySrv ENODATA _mongodb._tcp.456d in MongoDB Compass
  4. What is best way to convert datetime to date in Sql Server?
  5. How to compare date without time with now() in PostgreSQL?
  6. MySQL Error Code: 1175. safe update mode update a table without a WHERE
  7. ERROR 1819 (HY000): Your password does not satisfy the current policy requirements in MySQL
  8. ERROR: column must appear in the GROUP BY clause or be used in an aggregate function in PostgreSQL
  9. Calculating distance between two positions(lat, long) in SQL Server
  10. MySQL Error: 'Access denied for user 'root'@'localhost'
  11. Error: /opt/lampp/bin/mysql.server: 264: kill: No such process
  12. How to get the total of current connection in PostgreSQL?
  13. ERROR: OFFSET must not be negative in PostgreSQL
  14. How to update top N records for a table in PostgreSQL?
  15. PostgreSQL Error: Terminating connection due to administrator command
Leave An Answer
* NOTE: You need Login before leave an answer

* Type maximum 2000 characters.

* All comments have to wait approved before display.

* Please polite comment and respect questions and answers of others.

Most Quizs Popular

Start Test

Javascript - Functions
Start Test

Asp.Net Core - Exam test 01
Start Test

Asp.Net Core - Exam test 02
Start Test

C# - Level Beginner

Popular Tips

This site uses cookies so that we can remember you and understand how you interact with our website. This allows us to improve and customize your browsing experience. See our Cookies Policy.
X Close