Exteneded stored procedure syntax for multiple files

astreet

What is the syntax to backup database to three different files? When I do the following code, I get an error.

Ex.

master..sqlbackup N'-SQL "BACKUP DATABASE [wslogdb63_1] TO DISK = [\\SQL-INFRA02\J$\MSSQL$INFRA02\BACKUP\DIFF\wslogdb63_1_FULL_20070403_1508_1.bak],
DISK = [\\SQL-INFRA02\J$\MSSQL$INFRA02\BACKUP\DIFF\wslogdb63_1_FULL_20070403_1508_2.bak],
DISK = [\\SQL-INFRA02\J$\MSSQL$INFRA02
\BACKUP\DIFF\wslogdb63_1_FULL_20070403_1508_3.bak]"'

petey

The syntax is correct. What was the reported error?

You might want to use another file extension instead of .bak, as that is usually associated with native SQL Server backup files.

astreet

Receive the following error when code is broken into seperate lines.

SQL Backup v4.6.0.815
Syntax error: '
DISK' after ','

name value
exitcode 850
sqlerrorcode 0

Do not see error when statement is one line. Can you verify.

Jason Cook

Hi,
This is indeed an issue that exists in version 4.6 - the same occurs with other formatting such as using the tab key in the middle of a statement. Unfortunately the only solution is to ensure the command remains on one line, without newline or tabbing characters.

For reference, this issue has been resolved in the soon to be released version 5.0, where newline and tab characters can be used in the middle of a SQL Backup expression without problems.

Thanks,
Jason

astreet

Search the string for carriage return which is char(13) and replace with spaces seems to work. Also, can do the same for tab key.

Ex>



SET @sqlstring = 'master..sqlbackup N' + '''-SQL '+ '"'+ 'BACKUP DATABASE ' +
' TO DISK = ' + '' + 'WITH DIFFERENTIAL,COMPRESSION=1' + '"' + ''''

SET @sqlstring = REPLACE(@sqlstring,CHAR(13),'')

EXEC @rtn = sp_executesql @sqlstring