SQL Server on Linux - Adding dedicated backup disk for SQL Server

The default backup location of SQL Server during installation on Linux is /var/opt/mssql/data. This is also the default location of all data and log files. We should add a new disk and reconfigure the SQL Server default backup location. Additionally, we also need three separate directories for Full, Differential and Transaction Log backups for edge of administration.

The SQL Server configuration file is mssql.conf and is located in the following directory /var/opt/mssql/mssql.conf which needs to be adjusted after adding the disk.

In this task, we will perform the following:

1.      Add a 50GB dedicated backup disk.

2.      Prepare (partition, format, mount) the disk.

3.      Create multiple directories.

4.      Assign read/write permission to the SQL Server process.

5.      Reconfigure SQL Server configuration (mssql.conf file).

6.      Test the database backup using SSMS.

Environment: VMWare Esxi 6.5

Linux Edition: CentOS 7.4

SQL Server: linux05 (192.168.0.159)

Linux Terminal Emulator: Putty

Step-by-step guide:

Part one: Adding a 50GB new disk to SQL Server Box.

1.      From the Windows desktop, logon to the Linux server as a root user.

2.      Check the current disk status with Linux lsblk command.

3.      Add a 50GB disk to Linux Server and check the status again. The newly added disk name is sdb.

4.      Create partition on the disk sdb as follows:

sudo parted -s -a optimal /dev/sdb mklabel gpt

sudo parted -s -a optimal /dev/sdb mkpart primary 1M 100%

5.      Create the physical volume sdb1:

sudo pvcreate /dev/sdb1

6.      Create a volume group as vg_sqlbackup:

sudo vgcreate vg_sqlbackup /dev/sdb1

7.      Create a logical volume as lv_sqlbackup:

sudo lvcreate -n lv_sqlbackup --extents 100%FREE vg_sqlbackup

8.      Format the drive:

sudo mkfs.xfs /dev/vg_sqlbackup/lv_sqlbackup

9.      Create the mount directory in /var/opt/mssql:

sudo mkdir /var/opt/mssql/sqlbackup

10. Change the user and group ownership:

sudo chown mssql:mssql /var/opt/mssql/sqlbackup

11. Edit the fstab file, create a permanent mount point and add the mount point entry:

vi /etc/fstab

/dev/vg_sqlbackup/lv_sqlbackup  /var/opt/mssql/sqlbackup xfs   defaults 0 0  

12. Reload the fstab:

sudo mount -a

13. Check the disk status:

sudo df -h

14. Edit or reconfigure the SQL Server configuration file:

Method one – directly edit the conf file:

sudo vi /var/opt/mssql/mssql.conf

Add the following parameter:

[filelocation]

defaultbackupdir = /var/opt/mssql/sqlbackup

Alternatively, method two:

sudo /opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /var/opt/mssql/sqlbackup

15. Restart the SQL Server Service:

sudo service mssql-server restart

Part two: Creating directory for Full, Differential and Transaction Log backup:

1.      Check the newly created default backup location:

ll /var/opt/mssql/sqlbackup/

2.      Create three directories: full, diff and tlog respectively:

sudo mkdir /var/opt/mssql/sqlbackup/full

sudo mkdir /var/opt/mssql/sqlbackup/diff

sudo mkdir /var/opt/mssql/sqlbackup/tlog

3.      Assign permission to the mssql user and review the change:

sudo chown mssql:mssql /var/opt/mssql/sqlbackup/full

sudo chown mssql:mssql /var/opt/mssql/sqlbackup/diff

sudo chown mssql:mssql /var/opt/mssql/sqlbackup/tlog

ll /var/opt/mssql/sqlbackup/

Testing the backup in SSMS:

1.      Connect to the SQL Server on Linux using SSMS and execute the following commands:

BACKUP DATABASE TestDB TO DISK='/var/opt/mssql/sqlbackup/full/testdb_full_backup.bak'

BACKUP DATABASE TestDB TO DISK='/var/opt/mssql/sqlbackup/diff/testdb_diff_backup.bak'

WITH DIFFERENTIAL

BACKUP LOG TestDB TO DISK='/var/opt/mssql/sqlbackup/tlog/testdb_transaction_01.trn'

2.      Review the backup locations in Linux server:

ll -RL /var/opt/mssql/sqlbackup/

Some Screenshots:

Default database backup location:

Single disk on SQL Server:

A new 50 GB disk has been added:

Steps performed on Linux to add the new disk:

Added mount point entry to fstab:

New database backup location:

Three directories for different types of backup:

Verifying existence of database backup files:

Using SQL Monitoring Tool: