Error in Sql server query

Posted by Prabu_Spark under Sql Server on 8/21/2013 | Points: 10 | Views : 872 | Status : [Member] | Replies : 3
Hi sir,
I am getting the error

"Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '+'.
Msg 319, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon." after executing the below query. Kindly give me the solution for this problem




Query:
----------------

declare @backuptime varchar(50)
set @backuptime=convert(varchar,getdate())
BACKUP DATABASE checking TO DISK = 'd:\testdb1_'+@backuptime+'.bak' WITH FORMAT, MEDIANAME = 'checking', NAME = 'Full backup of my database';
GO

With regards,
J.Prabu.
[Email:prbspark@gmail.com]



Responses

Posted by: Ssj_Kumar on: 8/21/2013 [Member] Starter | Points: 25

Up
0
Down
Check with this
declare @backuptime varchar(50)
set @backuptime=convert(varchar,getdate())
Declare @path varchar(50)
set @path='d:\testdb1_'+@backuptime+'.bak'
BACKUP DATABASE checking TO DISK = @path WITH FORMAT, MEDIANAME = 'checking', NAME = 'Full backup of my database';
GO

Regards,
Jayakumar Selvakani

Prabu_Spark, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Allemahesh on: 8/21/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
You can find the different solution for this in below link:-
http://www.dotnetspider.com/forum/328958-Error-Sql-server-query.aspx

Happy Coding

Prabu_Spark, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/21/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
If you would like to have DATETIME value without spaces in the file name... you can do as follows
declare @backuptime varchar(50) 

set @backuptime=convert(varchar,getdate())
DECLARE @path VARCHAR(30) = 'd:\testdb1_'+REPLACE(@backuptime, ' ', '_')+'.bak'
--SELECT @path
BACKUP DATABASE checking TO DISK = @path WITH FORMAT, MEDIANAME = 'checking', NAME = 'Full backup of my database';


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Prabu_Spark, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response