How can we help you today? How can we help you today?

Dynamic sql for the T-SQL script

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.
dora
0

Comments

1 comment

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


    declare @... 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=@... + '"BACKUP DATABASE TO DISK = ''''' + @from_dir + @backup_name + ''
    set @sql=@... + ''''' WITH COPYTO = ''''' + @done_dir + ''''', ERASEFILES = 5, '
    set @sql=@... + 'MAILTO_ONERROR = ''''' + @error_email + ''''', NORECOVERY"'', @exitcode OUT, @sqlerrorcode OUT'
    exec sp_executesql @...
    moose
    0

Add comment

Please sign in to leave a comment.