Total Pageviews

Thursday, March 15, 2018

SQL Server on Linux - Database backup directly to a Windows Network shared folder using samba


We may want to use a Windows Network shared location to redirect SQL Server database backup files, either permanently or temporarily, from the SQL Server Linux instance. Whenever a database happens, the backup files will go directly to the Network shared folder which is \\192.168.0.50\linuxshare, where the Network shared username and password are the following:

username=megauser
password=verysecret

Step-by-step guide:

1.      If samba and cifs packages have not been installed, then install them:
sudo yum -y install samba-client samba-common cifs-utils

2.      Create a directory on the linux Server:
sudo mkdir /var/opt/mssql/sqlbackup

3.      Assign permission to mssql user:
sudo chown mssql:mssql /var/opt/mssql/sqlbackup

4.      Review the permission:
sudo ls -la /var/opt/mssql/sqlbackup

5.      Retrieve the mssql user id (uid):
id mssql

If we need to use the Windows network backup location permanently, then follow the following steps:

1.      Create a hidden file such as .sharedcrd. The file name can be anything:
sudo vi /etc/samba/.sharedcrd

2.      Add the following to the .sharedcrd:
            username=megauser
            password=verysecret

3.      Assign permission to the root user only and make it secured:
sudo chown roor:root /etc/samba/.sharedcrd
sudo chmod 600 /etc/samba/.sharedcrd

4.      Edit the fstab file:
vi /etc/fstab

5.      Add the following lines to the newly created file:
//192.168.0.50/linuxshare /var/opt/mssql/sqlbackup cifs defaults,credentials=/etc/samba/.sharedcrd,uid=991 0 0

6.      Reload the fastab file:
sudo mount -a

7.      Reconfigure the default location of the backup files for SQL Server.
sudo /opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /var/opt/mssql/sqlbackup

8.      Restart the SQL Server Service:
sudo service mssql-server restart

If we would like to use Windows Network shared temporarily, then execute the following line on the bash shell:
sudo mount.cifs //192.168.0.50/linuxshare /var/opt/mssql/sqlbackup -o user=megauser userid=991

Once the database backup is done, it can be dismounted with the umount command:
umount /var/opt/mssql/sqlbackup

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.