Options
Exteneded stored procedure syntax for multiple files
astreet
Posts: 28
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]"'
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]"'
Comments
You might want to use another file extension instead of .bak, as that is usually associated with native SQL Server backup files.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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.
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
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