Ola Hallengren - Backupdatabase Task

Question

When running Ola's full SQL Server Maintenance Solution or the individual databasebackup script I get the following error:

MSG 207, Level 16, State 1, Procedure DatabaseBackup, Line 1545 Invalid column name 'schemaName'.

This is part of the @databasesInParallel section which it appears to be trying to run even though this variable is set to 'N' at the beginning of the script. I haven't altered anything and this script works perfectly on other servers.

Answer 1

The dbo.Queue table exists, but does not have the SchemaName column. This can be due to a custom dbo.Queue table that has nothing to do with Ola's solution. You could rename the table in the maintenance plan or drop the existing tables.

Recreating the error

Step 1: Recreate the maintenance solution Procedure

Step 2 Create the dbo.Queue table without the SchemaName

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Queue]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Queue](
  [QueueID] [int] IDENTITY(1,1) NOT NULL,
  --  [SchemaName] [sysname] NOT NULL,
  [ObjectName] [sysname] NOT NULL,
  [Parameters] [nvarchar](max) NOT NULL,
  [QueueStartTime] [datetime] NULL,
  [SessionID] [smallint] NULL,
  [RequestID] [int] NULL,
  [RequestStartTime] [datetime] NULL,
 CONSTRAINT [PK_Queue] PRIMARY KEY CLUSTERED
(
  [QueueID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO

Step 3: Create the dbo.QueueDatabase table

Step 4: Try to take a backup

USE [MNGDB]
GO

EXEC  [dbo].[DatabaseBackup]
        @Databases = N'MNGDB',
        @Directory = N'G:\Data',
        @BackupType = N'FULL'

Error:

Msg 207, Level 16, State 1, Procedure dbo.DatabaseBackup, Line 1545 [Batch Start Line 5] Invalid column name 'SchemaName'. Msg 207, Level 16, State 1, Procedure dbo.DatabaseBackup, Line 1555 [Batch Start Line 5] Invalid column name 'SchemaName'. Msg 207, Level 16, State 1, Procedure dbo.DatabaseBackup, Line 1561 [Batch Start Line 5] Invalid column name 'SchemaName'.

---

Why

The query fails because the table exists but the schemaname column does not. If table does not exist, it succeeds.

---

Testing the part that causes the error & resolving it

  DECLARE @DatabasesInParallel char(1)  = 'N', @SchemaName varchar(255) = 'test',  @ErrorMessage nvarchar(max)
  IF @DatabasesInParallel = 'Y'
  BEGIN

    BEGIN TRY

      SELECT  QueueID
      FROM dbo.[Queue]
      WHERE SchemaName = @SchemaName


    END TRY

    BEGIN CATCH
      IF XACT_STATE() <> 0
      BEGIN
        ROLLBACK TRANSACTION
      END
      SET @ErrorMessage = 'Msg ' + CAST(ERROR_NUMBER() AS nvarchar) + ', ' + ISNULL(ERROR_MESSAGE(),'') + CHAR(13) + CHAR(10) + ' '
      RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
    END CATCH

  END

Error message

Msg 207, Level 16, State 1, Line 1892
Invalid column name 'SchemaName'.

Dropping the two previously created tables

drop table dbo.[QueueDatabase]; 
drop table dbo.[Queue];

and rerunning the statement produces no error:

Commands completed successfully.