String manipulation with variable

  • i am trying to use @datestamp variable in simple string , but not luck , i dont want to use another variable for full string as i am getting this kind of string input from other job, is there any way i can simple use the variable @datestamp rather using full statement in variable and then execute?

    Declare @Datestamp as varchar(10)

    SET @Datestamp=Convert(varchar(8),getdate(),112)

    BACKUP DATABASE [ReportServer]

    TO DISK = 'D:\MSSQL\BAK\ReportServer +'@Datestamp' +.BAK'

    WITH FORMAT, COMPRESSION

  • You can do this in this way.

    Declare @Datestamp as varchar(10)

    SET @Datestamp=Convert(varchar(8),getdate(),112)

    EXEC('BACKUP DATABASE [ReportServer]

    TO DISK = ''D:\MSSQL\BAK\ReportServer'+ @Datestamp+'.BAK''

    WITH FORMAT, COMPRESSION')

  • Awesome , also while trying to do the same i am stuck in string manipulation for @datestamp and with below it is always giving two quotes ,any way to get around this to get single quotes to generate correct statement?

    Declare @BACKUPCMD as varchar(5000)

    ,@BACKUPDIR1 as varchar(5000)

    ,@DB as varchar (500)

    ,@BACKUPFILEEXT as varchar(100)

    ,@WITHSWITCH as varchar(1000)

    select @BACKUPDIR1='d:\mssql\bak'

    ,@DB='reportserver'

    ,@BACKUPFILEEXT='.bak'

    ,@WITHSWITCH='WITH FORMAT, COMPRESSION'

    Select @BACKUPCMD='Declare @Datestamp as varchar(10)' + char(13)

    Select @BACKUPCMD=@BACKUPCMD + 'SET @Datestamp=Convert(varchar(8),getdate(),112)'+ char(13)

    SELECT @BACKUPCMD = @BACKUPCMD + 'Exec(''BACKUP DATABASE [' + @DB + ']' + CHAR(13)

    + 'TO DISK = '+'''' + QUOTENAME(@BACKUPDIR1 + '\' + @DB +'''+@Datestamp+'''+@BACKUPFILEEXT , '''') + CHAR(13)

    + @WITHSWITCH +''''+')'+ CHAR(13)

  • nevermind ,found the issue ,it was quotename causing issue , thanks again for the help.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply