Backup SQL Server on Linux Databases using SQL Server Agent on Windows

By: Pablo Echeverria | Updated: 2023-12-14 | Comments | Related: > SQL Server on Linux

Problem

In this tip, we're going to look at the steps to backup SQL Server on Linux databases and copy the backups to a Windows server. The backups could just be moved for storing on a different server or restored to SQL Server running on Windows.

Solution

Follow these steps to implement how to backup SQL Server on Linux databases.

Create the SQL Server Login on Linux Server

We will create a SQL Server login on the Linux server and give it permission to backup certain databases.

Create a SQL Server login named backupuser with a strong password but without a password expiration:

We will give this login the db_backupoperator role for all databases that we want it to backup. In this example we are setting this role for the master and msdb databases since these are the two databases we will use for this example, but you would want to do this for all databases.

Other permissions needed for the login are VIEW SERVER STATE and VIEW ANY DEFINITION, so we grant them manually:

GRANT VIEW SERVER STATE TO backupuser;
GRANT VIEW ANY DEFINITION TO backupuser;

The user is ready to take backups, but it will create them in a folder where the default Linux user, "mssql," has permissions.

Configure the Linux Folder and User

Next we will make sure that the SQL Server login has Linux folder permissions to create the database backups.

In Linux, there are three levels of permissions: owner, group, and others. We don't want to use the default "mssql" user because it is the SA. Thus, we will create a folder where the default Linux user "mssql" has owner permissions, and a new user named "backupuser" will have group permissions.

First, let's create the new group and user:

groupadd backupgroup
useradd -g backupgroup -rm backupuser
passwd backupuser

The first command creates a new group named "backupgroup."
The second command creates the user named "backupuser," who belongs to the group "backupgroup." The "-rm" flag is to create it as a service account but to create the home directory, which will be necessary.
The third command sets the user password, and you will be prompted to write it two times.

Now, we're going to create the backup folder with the appropriate permissions:

mkdir /var/opt/backup
chown -R mssql:backupgroup /var/opt/backup
chmod -R 760 /var/opt/backup
chmod g+srwx /var/opt/backup

The first command creates the folder.
The second command sets the owner as the default Linux user "mssql" and the group owner as the new group "backupgroup."
The third command changes the folder permissions so that the owner has all permissions, the group has read/write only, and no one else has permissions.
The last command sets newly created file permissions so that the file owner will always be the group and not the creator.

Now, make sure OpenSSH is installed in Linux. If not, follow these instructions to install and configure it, making sure "PubkeyAuthentication" is enabled.

Setup the Necessary Permissions in Windows

Next, we will work on the Windows server and setup the necessary components to read from the Linux server.

First, make sure you have OpenSSH installed, open a PowerShell console, and type "ssh"; if it's installed, it will show you the command usage. If it's not installed, it will tell you. Follow the instructions on how to install it in Windows 11 on your local machine, and follow the instructions on how to install it in Windows Server 2016 on the server where the scheduled job will be created.

Once installed, you need to create a keypair using this command:

ssh-keygen -t ecdsa

It will prompt you for the file location and passphrase. Press "enter" to accept the defaults, and note the location of the files, in my case: "C:\users\pecheverria\.ssh\".

Once generated, you need to connect to the Linux server with the following command (replace this IP with the one off your Linux server):

ssh [email protected]

It will show you the server fingerprint. Once confirmed, enter "yes" and press "enter." Then type the password of the newly created user "backupuser":

At this point, in the user's ssh folder, you will have three files, as follows:

These files need to be copied from your user's folder, in my case, "pecheverria," to the service account which runs the SQL Server Agent. So, if your service account is named "agent.mssql," go to "C:\users\agent.mssql". Windows will prompt you that you don't have access permission to this folder and to click "continue" to gain permanent access to it. Click "continue" and paste the whole ".ssh" folder from your username here.

SSH is a strong, secure protocol that won't allow the service account to connect if someone else has access to this ".ssh" folder. So, you must:

Change ownership of each individual file; instead of your user, it must be the service account.
Change ownership of the .ssh folder; instead of your user, it must be the service account.
Remove your user from the list of users with permission to the ".ssh" folder and all its child items.

These three steps are explained in this link. Once done, you can continue in the Linux console opened previously.

Setup Passwordless Authentication to Linux from Windows

Now that you're logged in, generate the keypair similarly to how it was done locally:

Once complete, you will have a ".ssh" folder with appropriate permissions. Here, you need to create a new file. Navigate to this folder with the command:

cd .ssh

Open the file with Notepad named "id_ecdsa.pub" on your local machine. It will start with something like "ecdsa-sha2-nistp256" followed by a key. Copy everything, and then, in Linux, type this command:

vi authorized_keys

Then press the "i" key to insert the text and paste the text you copied earlier, making sure nothing is missed. Then, press the "Esc" key to stop inserting text. Type ":x" and press "enter" to save the file.

Now, the permissions need to be set securely, so type the following command:

chmod 600 authorized_keys

Once done, you won't have to type the password of the user "backupuser" every time you ssh into this Linux machine.

Create the SQL Server Agent Job

The next step is to setup a SQL Server Agent Job on the Windows server that will do the following:

Delete old backup files on the Linux server
Create the backups on the Linux machine
Copy the backups from the Linux machine to the Windows server.

The SQL Server Agent job will have three steps, which are described below.

Step 1: Delete Old Files

PowerShell code to delete old files:

powershell.exe -command "cd C:\Windows\System32\OpenSSH\; .\ssh [email protected] 'rm -rf /var/opt/backup/*.bak'"

If you installed OpenSSH manually, change the location to "C:\Progra~1\OpenSSH-Win64\" or the folder where you installed it. Also, change the IP to the one of your Linux server.

Step 2: Backup All Databases

PowerShell code to backup all databases:

sqlcmd -S'192.168.1.2' -Ubackupuser -P'yourpassword' -Q @"
DECLARE @cmd VARCHAR(MAX)
DECLARE @path VARCHAR(128)
DECLARE @date VARCHAR(10)
SET @cmd = ''
SET @path='/var/opt/backup/'
SET @date=CONVERT(NVARCHAR(20),GETDATE(),112)
SELECT @cmd = @cmd + 'BACKUP DATABASE '+[name]+' TO DISK='''+@path+'Linux_'+[name]+'_'+@date+'.bak'';'
  FROM [sys].[databases]
 WHERE [name] NOT IN ('model', 'tempdb');
EXEC (@cmd)
"@

Change the IP to the one of your Linux server and the password to the one you assigned in the database server to the "backupuser."

This is intended to do a full backup once every day, so it only appends the date to the backup filename. If you will be doing hourly log backups, change the @date assignment to the following:

SET @date=replace(convert(varchar, getdate(), 101),'/','') + replace(convert(varchar, getdate(),108),':','')

And change the @cmd assignment to the following:

SELECT @cmd = @cmd + 'BACKUP LOG '+[name]+' TO DISK='''+@path+'Linux_'+[name]+'_log_'+@date+'.bak'';'
  FROM [sys].[databases]
 WHERE [name] NOT IN ('model', 'tempdb');

Step 3: Copy Remote Files

PowerShell code to copy remote files:

powershell.exe -command "cd C:\Windows\System32\OpenSSH\; .\scp [email protected]:/var/opt/backup/*.bak D:\BackUp\Full"

If you installed OpenSSH manually, change the location to "C:\Progra~1\OpenSSH-Win64\" or the folder where you installed it. Also, change the destination of the files to the folder where you will keep them.

Next Steps

About the author

Pablo Echeverria is a talented database administrator and C#.Net software developer since 2006. Pablo wrote the book "Hands-on data virtualization with Polybase". He is also talented at tuning long-running queries in Oracle and SQL Server, reducing the execution time to milliseconds and the resource usage up to 10%. He loves learning and connecting new technologies providing expert-level insight as well as being proficient with scripting languages like PowerShell and bash. You can find several Oracle-related tips in his LinkedIn profile.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-12-14