Wednesday, March 29, 2017

SQL Server on Linux. Set default backup directory in three steps.

Continue to get through SQL Server on Linux Configuration documentation:
https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-mssql-conf#backupdir

It is pretty clear, but still I want to prove it works and that it works MY way.

First step: You need to create a new folder for your backup directory.
I'll do it under default SQL Server folder and I'll do it just in ONE step:
sudo -u mssql mkdir /var/opt/mssql/backup
IMPORTANT:
1. To use command "mkdir" (make directory) you have to use "sudo" account, otherwise system will not allow you to do that.
2. New "backup" folder must be under ownership of "mssql" account. In order to do so we are using parameter "-u mssql".

Second step: Setting default backup directory to the newly created folder:
sudo /opt/mssql/bin/mssql-conf set defaultbackupdir /var/opt/mssql/backup

Third step: Restart SQL Server service:
sudo systemctl restart mssql-server

If there was no mistyping there shouldn't be any errors.

Backuping!

Lets check how it works. Run "sqlcmd" and do a backup of my test database:
sqlcmd -S localhost -U sa
Then enter "sa" password to the prompt and run database backup:
1> BACKUP DATABASE [LinuxTest] TO DISK = N'LinuxTest.bak';
2> GO

Again, if everything was done correctly, there shouldn't be any error.
To prove that backup was done will run the following command:
sudo ls -l /var/opt/mssql/backup

It should return newly created backup filename and its properties.

Here is how all of that worked in my virtual box:

No comments:

Post a Comment