November 24, 2012 at 6:17 pm
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
November 24, 2012 at 7:27 pm
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')
November 24, 2012 at 9:51 pm
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)
November 24, 2012 at 10:45 pm
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