Help with Parameters

• 
  Hi David,
  
  The syntax for SQL Backup is very similar to native, the main difference being that since the command is contained entirely in a quoted string, you have to pad any singlr quotes by doubling up.
  
  For example:
  

  EXEC master..sqlbackup '-SQL "BACKUP DATABASE ['+@iDataBaseName+'] TO DISK='''+@iBackUpLocation+''' WITH INIT, NAME='''+@vBackupName+'''"'
  

  
  Some options are not supported, for instance STATS and NOUNLOAD. NOUNLOAD only applies to tape backup, which SQL Backup does not support, and stats can't be used. SQL Backup has an extended stored procedure called sqbstatus which can possibly fulfill that requirement.

  Brian Donahue November 29, 2006 09:08

  - 0 +
• 
  Bryan,
  
  Thanks for Replying. However using your code sample only gives me
  Incorrect syntax near '+'.
  
  I keep fiddling and chaning quotes but nothing is helping.
  
  Here I stripped everything but the one variable:
  

  EXEC master..sqlbackup '-SQL " BACKUP DATABASE Staging_116 TO DISK='''+@iBackUpLocation+''' WITH INIT " '
  

  One last additional comment
  If I take the following:
  

  BACKUP DATABASE Staging_116 TO DISK=''' + @iBackUpLocation + ''' WITH INIT
  

  
  highlight it and check syntax I get no errors.

  Dsteyer November 29, 2006 14:51

  - 0 +
• 
  Try this:
  

  DECLARE @iDatabaseName varchar(12)
  DECLARE @iBackUpLocation varchar(260)
  DECLARE @vBackupName varchar(128)
  
  DECLARE @command nvarchar(1024)
  
  SET @idatabaseName = 'pubs'
  SET @iBackUpLocation = 'c:\temp\pubs.sqb'
  SET @vBackupName = 'Backup pubs'
  
  SET @command = '-SQL "BACKUP DATABASE [' + @iDataBaseName + '] TO DISK=''' + @iBackUpLocation+''' WITH INIT, NAME=''' + @vBackupName+'''"' 
  
  EXEC master..sqlbackup @command
  

  petey November 29, 2006 22:22

  - 0 +
• 
  Hi,
  
  I am a new user of Sql Backup, andhave the same problem as the poster. I need to stuff a variable list of DB names into the backup command. You have not explained why this:
  

  EXEC master..sqlbackup '-SQL " BACKUP DATABASE Staging_116 TO DISK='''+@iBackUpLocation+''' WITH INIT " '
  

  
  ...does not work, even though it looks correct. and the solution below is more complex. any way to make it work?
  
  Thanks,
  
  Fred
  
  

  petey wrote:

  Try this:
  

  DECLARE @iDatabaseName varchar(12)
  DECLARE @iBackUpLocation varchar(260)
  DECLARE @vBackupName varchar(128)
  
  DECLARE @command nvarchar(1024)
  
  SET @idatabaseName = 'pubs'
  SET @iBackUpLocation = 'c:\temp\pubs.sqb'
  SET @vBackupName = 'Backup pubs'
  
  SET @command = '-SQL "BACKUP DATABASE [' + @iDataBaseName + '] TO DISK=''' + @iBackUpLocation+''' WITH INIT, NAME=''' + @vBackupName+'''"' 
  
  EXEC master..sqlbackup @command
  

  freddy12345 December 11, 2006 15:29

  - 0 +
• 
  When you call the SQL Backup extended stored procedure, the parameters must be complete in itself. That's probably a little vague, but the same reason is why this works:
  

  DECLARE @command nvarchar(128)
  SET @command = N'SELECT TOP 10 * FROM sysobjects'
  EXEC master..sp_executesql @command
  

  
  but this does not, 'though it looks correct'.
  

  DECLARE @tablename nvarchar(128)
  SET @tablename = N'sysobjects'
  EXEC master..sp_executesql N'SELECT TOP 10 * FROM ' + @tablename
  

  petey December 11, 2006 20:29

  - 0 +

Add comment

Please sign in to leave a comment.