Ola Hallengren Striped Backup Job

  • BU69

    SSCrazy

    Points: 2783

    Not sure is we have any experts on the Ola Hallengren jobs here or if anyone can recommend a better way of doing this, If I run this backup in a job this works:

    sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d LL_DB -Q "EXECUTE [dbo].[DatabaseBackup] @databases = 'AVAILABILITY_GROUP_DATABASES', @Directory = N'\backup\SQL\', @BackupType = 'FULL', @ChangeBackupType = 'Y', @Verify = 'Y', @CleanupTime = 48, @checksum = 'Y', @LogToTable = 'Y'" -b

    If I stripe the backup in a job like so:

    sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d LL_DB -Q "EXECUTE [dbo].[DatabaseBackup] @databases = 'AVAILABILITY_GROUP_DATABASES', @Directory = N'\backup\SQL\Drive1\, \backup\SQL\Drive2\, \backup\SQL\Drive3\, \backup\SQL\Drive4\, \backup\SQL\Drive5\, \backup\SQL\Drive6\, \backup\SQL\Drive7\, \backup\SQL\Drive8\, \backup\SQL\Drive9\, \backup\SQL\Drive10\', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 24, @checksum = 'Y', @LogToTable = 'Y'" -b

    I get the following error:

    Msg 105, Level 15, State 1, Server SQLSERVER, Line 1 Unclosed quotation mark after the character string 'EXECUTE [dbo].[DatabaseBackup] @databases = 'AVAILABILITY_GROUP_DATABASES', '.

    I can execute the same code as T-SQL and it works, also if I put the T-SQL in a job it works (although it gives me error 50000, but still completes!), I've been over it a few times and can't see what I might be missing, do I need to put extra quotation marks in the SQLCMD job?  Olga recommends that the job be run as a sqlcmd else if there is an error the job will not continue.

    Thanks

  • Eirikur Eiriksson

    SSC Guru

    Points: 182321

    IIRC, there should be a double backslash for the share names
    😎

    sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d LL_DB -Q "EXECUTE [dbo].[DatabaseBackup] @databases = 'AVAILABILITY_GROUP_DATABASES', @Directory = N'\\backup\SQL\Drive1\, \\backup\SQL\Drive2\, \\backup\SQL\Drive3\, \\backup\SQL\Drive4\, \\backup\SQL\Drive5\, \\backup\SQL\Drive6\, \\backup\SQL\Drive7\, \\backup\SQL\Drive8\, \\backup\SQL\Drive9\, \\backup\SQL\Drive10\', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 24, @checksum = 'Y', @LogToTable = 'Y'" -b

  • BU69

    SSCrazy

    Points: 2783

    There is, my copying it into here must have removed that somehow.  It executes fine as T-SQL outside of the sqlcmd wrapper, it's just when I try to put it in a job as a sqlcmd it gives me the error.

  • BU69

    SSCrazy

    Points: 2783

    Fixed it, the whole thing needed to be on one line and not the way I broke it up into different lines like a SQL command.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply