Dynamic sql for the T-SQL script

dora

I am trying to insert the T-SQL script into my stored procedure that does a backup of all the databases. waht i need to accomplish in the script is to be able to pass teh name of the database and the path name dynamically to the sql statement. How do I pass those values in the script ?
I tried somethig like this but it errored out. Could you please advice.

EXECUTE master..sqlbackup N'-SQL "BACKUP DATABASE "'+ @DBNAME + '" TO DISK = "' +@physicalName+ '"
WITH COMPRESSION = 1, ERASEFILES = 2, VERIFY"'

Thanks,
Dora.

moose

You could try something along these lines, setting the variables with the required options for your environment.


declare @sql nvarchar(4000),
@backup_name varchar(500),
@exitcode int,
@sqlerrorcode int,@Name varchar(128),@from_dir varchar(1000),@done_dir varchar(1000), @error_email varchar(1000)


set @sql='DECLARE @exitcode int;DECLARE @sqlerrorcode int;EXECUTE master..sqlbackup N''-SQL '
set @sql=@sql + '"BACKUP DATABASE TO DISK = ''''' + @from_dir + @backup_name + ''
set @sql=@sql + ''''' WITH COPYTO = ''''' + @done_dir + ''''', ERASEFILES = 5, '
set @sql=@sql + 'MAILTO_ONERROR = ''''' + @error_email + ''''', NORECOVERY"'', @exitcode OUT, @sqlerrorcode OUT'
exec sp_executesql @sql