SQL Server On Linux / Ola Hallengren Scripts

Background

It is silly to install a database software and not have automated backup in place.

Let us address that.

Ola Hallengren

Outline

  1. Please download Ola Hallengren script
  2. Apply against SQL Server Instance

Processing

Download

Please download Ola Hallengren from here.

Apply

Please apply downloaded scripts against targeted SQL Server Instance.

Issues

Outline

  1. SQL Server Agent
    • Jobs
      • JobStep
        • Please change Step Type from “Operating System (CmdShell)” to “Transact SQL Script (T-SQL)
  2. Stored Procedure
    • dbo.DatabaseBackup
      • Parameters
        • @CleanupTime
          • Make sure it is not set

SQL Server Agent

Jobs

JobStep
Outline
  1. Job Step Type
    • In Linux, the lone Job Step type is “Transact SQL Script (T-SQL)
  2. Change Step from Command Shell verbiage to SQL
    • Backup
      • Verbiage
        • OS
          • sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d AdminDB -Q “EXECUTE [dbo].[DatabaseBackup] @Databases = ‘SYSTEM_DATABASES’, @BackupType = ‘FULL’, @Verify = ‘Y’, @CleanupTime = 72, @CheckSum = ‘Y’, @LogToTable = ‘Y'” -b
        • SQL
          • EXECUTE AdminDB.[dbo].[DatabaseBackup] @Databases = ‘SYSTEM_DATABASES’, @Directory = NULL, @BackupType = ‘FULL’, @Verify = ‘Y’, @CheckSum = ‘Y’, @LogToTable = ‘Y’
Images
Before

SQLServerAgent.step.01.before.20181128.1215PM.PNG

After

SQLServerAgent.step.01.after.20181128.1219PM.PNG

Parameters

CleanupTime

Finding

When cleanupTime is specified SQL Server utilizes [master].dbo.xp_delete_file to remove dated files.

While sanitizing its input, xp_delete_file returns

Msg 22049, Level 15, State 0, Line 0
Error executing extended stored procedure: Invalid Parameter
Msg 50000, Level 16, State 1, Line 1
Error deleting files.

It appears that because the Linux File System uses / and MS Windows uses \ , the system barks.

Workaround

Please skip Timestamp for filename


@FileName nvarchar(max) = '{ServerName}${InstanceName}_{DatabaseName}_{BackupType}_{Partial}_{CopyOnly}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}'

 


@FileName nvarchar(max) = '{ServerName}${InstanceName}_{DatabaseName}_{BackupType}_{FileNumber}.{FileExtension}'

Cross Reference
  1. Microsoft
    • feedback.azure.com
      • xp_delete_file on SQL Server 2017/Linux
        Link
  2. Ola Hallengren
    • ola hallengren/sql-server-maintenance-solution
      • DatabaseBackup
        Link