February 8, 2018 at 1:52 am
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
February 8, 2018 at 2:19 am
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
February 8, 2018 at 2:24 am
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.
February 9, 2018 at 12:27 am
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply