How to backup and restore MSSQL Server database in Ubuntu
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.
Nam Vo Mar 23 2021
We can use
sqlcmdcommand 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"
[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]
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"
[DB_Name]: Name of the database will be created.
[Path .bak file]: Link path .bak file existed in your server.
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.