September 1, 2005 at 1:03 pm
Hi guys,
I have a simple script. Trying to execute this script to backup a database. Instead of speciafying the db name, I created a local variable and assigned the db name to the variable, but when I run the code, I get a syntax error, and I can't seem find it.
here's the code
DECLARE @DBName varchar(255)
set @dbname = 'Joe_LeninL'
BACKUP DATABASE @DBName
TO DISK = 'K:\User_Databases\CFT_dbs\Data\Joe_leninl\' + @DBName + '_bkp.bak'
WITH INIT,
NOUNLOAD,
NAME = @DBName + ' backup',
NOSKIP ,
STATS = 10,
NOFORMAT
here's the error that I get
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '+'.
thanks in advance
September 1, 2005 at 1:10 pm
You'd have to run all this code under dynamic sql with exec (@DynamicCode)
September 1, 2005 at 1:28 pm
I don't understand. Plz explain
thx
September 1, 2005 at 1:38 pm
Declare @ExecSQL as varchar(1000)
Declare @Dbname as varchar(50)
set @DbName = 'Joe_LeninL'
set @ExecSQL = '
BACKUP DATABASE ' + @DbName + ' TO DISK = ''K:\User_Databases\CFT_dbs\Data\' + @DbName + '\' + @DbName + '_bkp.bak''
WITH INIT,
NOUNLOAD,
NAME = ' + @DBName + ' backup,
NOSKIP ,
STATS = 10,
NOFORMAT
'
PRINT (@ExecSQL)
--EXEC (@ExecSQL)
uncomment the exec part once you're sure that the code is correct (check the print versio of the code)
September 1, 2005 at 3:04 pm
thanks for the response, when I print the statement to screen, it shows the code, but when I run the EXEC (@ExecSQL), I get this
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'Joe_LeninL'.
September 1, 2005 at 3:08 pm
What if you print the code and execute it?
September 1, 2005 at 3:23 pm
this is what I get when I print it and execute it
BACKUP DATABASE Joe_LeninL
TO DISK = 'K:\User_Databases\CFT_dbs\Data\Joe_LeninL\Joe_LeninL.bak'
WITH INIT,
NOUNLOAD,
NAME = Joe_LeninL backup,
NOSKIP ,
STATS = 10,
NOFORMAT
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near 'Joe_LeninL'.
September 1, 2005 at 3:53 pm
Do you have a folder called Joe_LeninL? Or is this just the name of the file within the folder Data? You may need to drop the extra \' + @DbName + '\ from your script...
I wasn't born stupid - I had to study.
September 1, 2005 at 3:57 pm
got it to work, thanks for all your help
September 1, 2005 at 5:21 pm
Can you post the final code so that it may help somebody else?
September 4, 2005 at 8:09 pm
You need to make sure the value for name is quoted.
BACKUP DATABASE Joe_LeninL
TO DISK = 'K:\User_Databases\CFT_dbs\Data\Joe_LeninL\Joe_LeninL.bak'
WITH INIT,
NOUNLOAD,
NAME = 'Joe_LeninL backup',
NOSKIP ,
STATS = 10,
NOFORMAT
Julian Kuiters
juliankuiters.id.au
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply