Help with Parameters

DsteyerDsteyer Posts: 3
edited August 1, 2017 2:47PM in SQL Backup Previous Versions
Hi Everyone. First love the product so far. Was wondering if I could get some syntax help though with Parameters. Here is my old non SQLBackUp syntax:
BACKUP DATABASE @iDataBaseName TO  DISK = @iBackUpLocation 
    WITH  INIT ,  NOUNLOAD ,  
    NAME = @vBackUpName,  
    NOSKIP ,  
    STATS = 10,  
    NOFORMAT DECLARE @i INT

Have been trying to figure out how to insert parameters using the new command but have not found any examples in help and I did not see anything here. Basically I pull from a table what databases I want backed up and fill the parameters with that information. Any help would be appreciated.
Tagged:

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    edited August 1, 2017 2:47PM
    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.
  • DsteyerDsteyer Posts: 3
    edited August 1, 2017 2:48PM
    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.
  • peteypetey Posts: 2,358 New member
    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
    
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • freddy12345freddy12345 Posts: 19 Bronze 2
    edited August 1, 2017 2:48PM
    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
    
  • peteypetey Posts: 2,358 New member
    edited August 1, 2017 2:48PM
    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
    
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.