Sqlbackup parameters argument varchar length

alchemistmatt

What is the maximum allowed length for the parameters argument provided to the sqlbackup extended stored procedure? varchar(4000), varchar(8000), nvarchar(4000), nvarchar(max)?

The reason I ask is that the BACKUP DATABASES syntax could get quite long if using 32 databases (the maximum allowed by sqlbackup) and if the DB names are long. For example, 32 databases with 64 character names gives 2048 characters, and if the parameter type for the stored procedure is nvarchar, then that's a 4096 length string for the database names alone. When constructing the list of databases to send to sqlbackup, I can monitor the length of the string while adding DBs to the list, but I'd like to know what limits I should be checking for. Some example code:

Declare @DBList varchar(3000)
Set @DBList = 'Master,Model,MSDB'

Declare @command varchar(4000)
Set @command = '-SQL "BACKUP DATABASES [' + @DBList + '] TO DISK = ''\\BackupServer\BackupShare\<DATABASE>\<AUTO>'' WITH MAXDATABLOCK=524288, NAME=''<AUTO>'', DESCRIPTION=''<AUTO>'', ERASEFILES=20, COMPRESSION=3, THREADCOUNT=2, VERIFY, LOGTO=''I:\SqlServerBackup\'', MAILTO_ONERROR = ''BackupMonitor@mydomain.com''"'

exec master..sqlbackup @command

Thanks,
Matt

alchemistmatt

As an aside, a suggested improvement is to provide an INFOONLY or SIMULATE option for the sqlbackup stored procedure that would let us test the syntax of commands sent without actually performing the backups. The procedure could report the names of the databases that would be backed up, and the names of the files that would be created (thus validating the server names and folder paths provided) but wouldn't actually run the backup and wouldn't update any of the logs tracked by Sql Backup.

alchemistmatt

And one more question: What if a database name contains a comma? I presume it cannot be included in the list of databases when using "BACKUP DATABASES", since the delimeter in that list is a comma and additional square brackets in the list are not allowed. I don't, in fact, have any databases with a comma in the name, but it is a possibility. The help doesn't mention any special delimiters (e.g. single quotes) to use when adding databases to the '[ list_of_databases ] ' for the BACKUP DATABASES command in case any of the databases have non-standard characters (in particular a comma or space) in the name.

petey

Try limiting the length of the parameter to about 60000 chars', or 30000 nchars'.

Your suggestion re a 'simulation' mode is noted, as are your comments on database names using 'special' characters. You can use the single database backup syntax to backup such databases in any case e.g.

EXEC master..sqlbackup '-sql "BACKUP DATABASE [a,b] TO ..." '