If I run the 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 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?
You can use above script while creating the T-SQL job and schedule it accordingly.
Best Answer
Everything needs to be on one line...not multiple so we humans can read it....same as if you were typing it into SQLCMD. The second you hit return it starts executing from there.