Help with Parameters

Dsteyer

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.

Brian Donahue

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.

Dsteyer

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.

petey

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

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

petey

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