SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server on Linux Series: Backing up over the network

One of the challenges with any SQL Server business continuity strategy is backing up your databases and logs on a frequent basis. With Windows, we’ve known how to accomplish this for years. But, with SQL Server on Linux, you can accomplish the same task with just a few different twists. It is similar to mounting a network share as a new driver letter in Windows. Let’s explore how to back up your databases to a network share with Linux!

As of SQL Server 2017 RC2, we’ll want to accomplish it in a way that is transparent to SQL Server. (Depending on the RTM version whenever it is released, I might change the recommendation on this.) To do this, we’ll want to create a folder on the local file system that actually maps to a remote network share for SQL Server backups.

SSH into your server without elevated privileges at this point.

The network share is presented from a Windows server with the SMB protocol. Linux can connect to this using a compatible protocol called CIFS, or Common Internet File System. We’ll need to install the packages so we can natively connect. On Ubuntu and other Linux distros, the easiest is with the cifs-utils package. To install from the package manager is as simple as this.

My personal preference is to create a folder under the /mnt directory for this new mounted network share. I’ll be backing this up to a share for DB backups on my Synology NAS, so let’s call it syn1_dbbackup01. You’ll need elevated privileges to create this folder or you’ll get a permission denied error.

We need to find a way to authenticate against the remote Windows share with Windows-based credentials, because you are securing the share against unauthorized users, aren’t you? We can’t just put the credentials for your SQL Server service account in plain text where anyone can see them. We’ll put them in a spot under your user where only something with root access can access it. The easiest way is to put them in your home directory under a file called .smbcredentials. Using vi or any other editor of your choice, let’s edit the new file.

The entries are in a simple format:

username=yourusername
password=yourpassword

Enter your credentials and save.

Change the permissions on this new file so that other folks cannot see the contents with chmod.

We now go back to our file system table entries, located in the file /etc/fstab. Add an entry with elevated privileges resembling the following.

//servername/share/folder/subfolder /mnt/yournewfolder cifs credentials=/home/youruser/.smbcredentials,iocharset=utf8,sec=ntlm 0 0

Exit the text editor and save your changes.

To test your changes, mount the new folder with sudo mount /mnt/yourbackupshare.

Anything in that Windows shared folder you can now see if you cd into the directory and view contents.

Anything mounting properly should come back after full OS restarts. Test and verify.

Set permissions on this new folder so that SQL Server can read and write to it with a recursive chown.

Now we want to get the syntax correct for our backups and restores. Let’s restore a backup from this location first as part of a database migration. Copy the database backup from its source into this folder. List the folder contents to verify that the file is in the right spot.

Note the folder path and structure. We’ll want to restore this into the new drives that we added in the previous post.

The new database data and log files are now set up in their correct locations!

Let’s now back up this database.

FYI – You might get the following error if you try to overwrite the existing file. File sector sizes might be different between platforms.

Either remove the file already sitting there, rename the new backup file, or overwrite it with a new backup set. Problem solved!

FYI – If you do not set the permissions on the mountpoint folder, you’ll get the following permission denied error.

You’ve now got working backups from SQL Server on Linux to a network share!

Technobabble by Klee from @kleegeek

David Klee is all around geek who loves data - including the platform it resides on, virtualizing it, improving performance, availability, and disaster recoverability, and data presentation and visualization. He frequently advises organizations on the techniques of migrating their business-critical physical SQL Servers to the VMware infrastructure in his day job as Solutions Architect. David speaks at many national SQL Saturday events and SQL Server User Group meetings, as well as writes technical columns on SQL Server and virtualization topics on various blogs. He is on Twitter (https://twitter.com/kleegeek), LinkedIn (http://www.linkedin.com/in/davidaklee), and blogs frequently (http://www.davidklee.net).

Comments

Leave a comment on the original post [www.davidklee.net, opens in a new window]

Loading comments...