What are the challenges you face when working across database platforms? Take the survey

Exteneded stored procedure syntax for multiple files

astreetastreet Posts: 28
edited April 5, 2007 12:56PM in SQL Backup Previous Versions
What is the syntax to backup database to three different files? When I do the following code, I get an error.


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],


  • Options
    peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    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.
  • Options
    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.

  • Options
    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.


    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
Sign In or Register to comment.