Prashanth Jayaram

Backup Linux SQL Server databases using PowerShell and Windows task scheduler

May 22, 2018 by

This article is an in-depth guide on how PowerShell can be used to maintain and manage SQL backup on Linux and Windows machines.

Here’s an outline of what this article is all about:

  • Introduction
  • Technical specifications
  • How to load SQL Server modules on Windows machine
  • Security – Credential Management
  • The objectives of Backup and Restore
  • Library Linkage
  • How SQL Server 2017 backup feature is used on Linux
  • And more …

Introduction

Tools to take SQL database backups, such as sqlcmd, SSMS, and PowerShell, are common to all platforms supported by SQL Server. In this post, I’m going to show how you can backup your SQL Server 2017 databases on a Linux machine using PowerShell.


Pre-requisites

  • RedHat Server 7.3 or CentOS 7.0 or higher
  • SQL Server 2017 or higher
  • SQL Server Management Studio (SSMS) 16.5 or higher

Flow Diagram

Install the newest version of SQL PowerShell on Windows

The latest version of SSMS is optimized to work efficiently with SQL Server 2017 on Linux. To download and install the latest version, see Download SQL Server Management Studio.

Launch PowerShell and import the sqlserver module

Let’s start by launching PowerShell on Windows. Open a command prompt on your Windows computer, and type PowerShell to launch a new Windows PowerShell session.

PowerShell

SQL Server provides a Windows PowerShell module named SqlServer that can be used to import the SQL Server components (SQL Server provider and cmdlets) into a PowerShell environment. (The name of the SQL PowerShell module for SSMS has changed from SQLPS to SQLServer.)

SSMS uses the new wrapper EXE to instantiate the SQL PowerShell environment. If you do not have the module, use the Install-Module cmdlet to install the SqlServer module. This will add new functionality and cmdlets to your PowerShell session. The new module will be installed to “%Program Files%\WindowsPowerShell\Modules\SqlServer”.

Copy and paste the command below at the PowerShell prompt to import the SqlServer module into your current PowerShell session:

Type the command below at the PowerShell prompt to verify that the SqlServer module was imported correctly:


How to find what assemblies are loaded in windows

Based on the version of PowerShell, load the associated assembly. In most cases, the following command should be able to find and load the SQL server SMO assembly.

The AppDomain.GetAssemblies method gets the assemblies that have been loaded into the execution context of this application domain.


If you know the full assembly name use the .Load() method.


or,

Remember that the function, LoadWithPartialName has been deprecated. The recommended solution for PowerShell is to use Add-Type or load the specific library.

How to handle Credentials in Powershell

The three ways of managing credentials are as follows

  1. Hard code the login credentials
  2. Using Get-Credential cmdlets
  3. Using a Secured file

Hard code the login credentials

Often, we encounter a case where a password is hard coded in the script. Of course, the problem with this is that your password will be exposed to anyone with access to the script file.

The below sample code depicts the use of hard coded credential in the script

The output is given below

Using Get-Credential cmdlet – Pop a dialog box

Get-Credential displays a window to enter credential details. This will appear every time you run the script. The $credential variable stores the username and password. The credentials are then fed to the respective queries for further processing.

The below sample code depicts the use of Get-Credential cmdlet


Using a Secured file

First, the password has to be written to a file.

Second, the credentials are read from the file using PSCredential class. You don’t need to re-enter the password over and over again.

Save the credentials as XML that can only be used on that computer with only your account.

Pop the credential dialog and input your credentials. This is a one-time thing.

Now, make the script,

Backup

Designing an effective backup and restore strategy requires careful planning, implementation, and testing. This section talks about using SQL Server Management Objects (SMO) to back up an SQL database hosted on a Linux Machine.

Let’s start this section by instantiating the SMO class library

After connecting to the Linux SQL Instance, the properties of the databases can be listed using the below call:

The below call lists only the user-defined databases.


Prepare the backup file to back up the AdventureWorks database. The following code provides the metadata of the backup file and its backup directory location


Define the backup object that will be used for the database backup operation

Let’s retrieve the backup type information using an enumeration. The action property defines the type of backup.


Member name Description
Database Backs up all the data files in the database.
Files Backs up specified data files.
This option is recommended only when the transactions that have occurred since the last backup are isolated to the file or group of files that are being backed up.
Log Backs up the transaction log.

For a full backup, the Action property is set to a database, whereas for a differential backup, the Action property is set to a database along with having the incremental property set to $TRUE. For transaction log backup, on the other hand, we just change the Backup Action to Log.

Specify the backup description so that we know what this backup is and when it was taken.

We can configure what type of backup device is going to be used for the backup. In this case, it’s a file. To add a backup file to a device, use AddDevice() method.


Member name Description
File Specifies a disk file.
LogicalDevice Specifies a logical device.
Pipe Specifies a named pipe.
Tape Specifies a tape device.
Url Specifies a URL.
VirtualDevice Specifies a virtual device.

Now, the configuration is set; let’s initiate the database backup.

Let’s perform a dry run by combining all the pieces of above code:

That gives us the sample output like below:

The following sample script can be used to back up a database with the following schedule:

Every Sunday Evening at 10 PM – Full Backup
Every [Mon-Sat] Evening at 10 PM – Differential Backup
Every hour – Transaction Log

You can run the script on multiple Linux machines by passing the associated IP addresses as a parameter to the calling function Get-SQLinuxBackup. The same script can also be used to run backups for SQL instances on the Windows machines.

Now, copy and save the PowerShell script as C:\BackupLinuxInstance.ps1.

In the script, it is necessary to make changes to the following variables

  1. BackupDirectory
  2. Credentials

Task Scheduler 

How to create an event using the Task Scheduler:

  •  Open “Task Scheduler” (Go to START—Run. Type “Tasks”, and hit Enter)
  •  Click on “Create task”
  •  Pick a name for the task, and choose “Run whether user is logged on or not”

  • Choose the “Triggers” Tab, Click “New”

  • Specify the option you like, and then click “OK” to create a trigger

  • Choose “Actions” tab, Click “New”

  • Copy following command to “Program/script” textbox C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
  • Enter the path of the saved script file in “Add arguments (optionally)” textbox

As per the screenshot, I saved the file under C:\BackupJob_SQL2017_Linux.PS1. Therefore, in the add arguments text box, I entered: C:\ BackupJob_SQL2017_Linux.PS1;exit

  • Right click and run the job.

  • Verify the output

That completes the guide to creating a backup of a database hosted on a Linux machine.

Table of contents

Database Backup and Restore process in SQL Server – series intro
An overview of the process of SQL Server backup-and-restore
Understanding the SQL Server Data Management Life Cycle
Understanding SQL Server database recovery models
Understanding SQL Server Backup Types
Backup and Restore (or Recovery) strategies for SQL Server database
Discussing Backup and Restore Automation using SQLCMD and SQL Server agent
Understanding Database snapshots vs Database backups in SQL Server
SqlPackage.exe – Automate SQL Server Database Restoration using bacpac with PowerShell or Batch techniques
Smart database backup in SQL Server 2017
How to perform a Page Level Restore in SQL Server
Backup Linux SQL Server databases using PowerShell and Windows task scheduler
SQL Server Database backup and restore operations using the Cloud
Tail-Log Backup and Restore in SQL Server
SQL Server Database Backup and Restore reports
Database Filegroup(s) and Piecemeal restores in SQL Server
In-Memory Optimized database backup and restore in SQL Server
Understanding Backup and Restore operations in SQL Server Docker Containers
Backup and Restore operations with SQL Server 2017 on Docker containers using SQL Operations Studio
Interview questions on SQL Server database backups, restores and recovery – Part I
Interview questions on SQL Server database backups, restores and recovery – Part II
Interview questions on SQL Server database backups, restores and recovery – Part III
Interview questions on SQL Server database backups, restores and recovery – Part IV

References


See more

To manage SQL Server backups, consider ApexSQL Backup, a tool that offers automation of backup, restore, and log shipping jobs, stores details of all backup activities and enables easy cross server backup management and maintenance.


Prashanth Jayaram

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram
Prashanth Jayaram
Backup and restore, PowerShell

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB. View all posts by Prashanth Jayaram

636 Views