Sql-server – Ola Hallengren – Backupdatabase Task

ola-hallengrensql serversql-server-2012

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.

Best Answer

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.

Related Question