未加星标

SQL Server on Linux Series: Backing up over the network

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二05 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏

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.


SQL Server on Linux Series: Backing up over the network

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.


SQL Server on Linux Series: Backing up over the network

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.


SQL Server on Linux Series: Backing up over the network

The entries are in a simple format:

username=yourusername

password=yourpassword

Enter your credentials and save.


SQL Server on Linux Series: Backing up over the network

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


SQL Server on Linux Series: Backing up over the network

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.


SQL Server on Linux Series: Backing up over the network

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.


SQL Server on Linux Series: Backing up over the network

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


SQL Server on Linux Series: Backing up over the network
SQL Server on Linux Series: Backing up over the network

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


SQL Server on Linux Series: Backing up over the network

Let’s now back up this database.


SQL Server on Linux Series: Backing up over the network

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


SQL Server on Linux Series: Backing up over the network

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.


SQL Server on Linux Series: Backing up over the network

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

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQLLinuxWindowsSQL ServerUbuntuCIF
分页:12
转载请注明
本文标题:SQL Server on Linux Series: Backing up over the network
本站链接:http://www.codesec.net/view/561248.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(14)