Solved

SQL Server Script Syntax error

Posted on 2011-03-04
7
383 Views
Last Modified: 2012-05-11
I'm having trouble with a script...  I'm fairly new at SQL Server, but determined to get better.
The Purpose of my script is to take a database, back it up to a file without a time/date stamp on it.  I want to do this so I can use an SFTP script to push the file to one of my customers on a weekly basis.  The SFTP script being used has a requirement for the file to be the same name for it to automatically run each Saturday.  The script is below... i am getting this error:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'E'.
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.

Can someone point me in the right direction?  I must be missing something easy.  The directory does exist.  Why is it having a problem with the E for my E drive, and what is wrong with my WITH.  (This is SQL 2005 Std).  


DECLARE @Filename varchar(256)
DECLARE @BkpPath varchar(256) 
DECLARE @DBName varchar(256)

SET @BkpPath = 'E:\SQL Backups\TEST\'

SELECT @DBName = 'MyDatabase'
SELECT @FileName = @DBName + 'Backup.BAK'
--SELECT @BkpPath = 'E:\SQL Backups\TEST\'
SELECT @FileName = @BkpPath + @FileName

DECLARE @SQL varchar(max)

SELECT @SQL = 
'BACKUP DATABASE ' + @DBName + ' TO 
	DISK ='+ @FileName 
	WITH NOFORMAT, INIT,SKIP, NOREWIND, NOUNLOAD,  STATS = 10'

EXEC (@SQL)

Open in new window

0
Comment
Question by:Robert Hamel
  • 4
  • 2
7 Comments
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 35039753
In first look, I feel this change

SELECT @SQL =
'BACKUP DATABASE ' + @DBName + ' TO
 DISK ='+ @FileName + ' WITH NOFORMAT, INIT,SKIP, NOREWIND, NOUNLOAD,  STATS = 10'
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 35039763
seems like you missed appending + ' after @FileName

Raj
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 35039791
Also if required , try this change also


SET @BkpPath = 'E:\SQL Backups\TEST\'

to


SET @BkpPath = @'E:\SQL Backups\TEST\'

Raj
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Assisted Solution

by:Rajkumar Gs
Rajkumar Gs earned 150 total points
ID: 35039822
>> Incorrect syntax near the keyword 'with'.

Regarding this error, I cannot see any 'with' in the posted query

If any
change
WITH
to
;WITH

Raj
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35039898
try this.
DECLARE @Filename varchar(256)
DECLARE @BkpPath varchar(256) 
DECLARE @DBName varchar(256)

SET @BkpPath = 'E:\SQL Backups\TEST\'

SELECT @DBName = 'MyDatabase'
SELECT @FileName = @DBName + 'Backup.BAK'
--SELECT @BkpPath = 'E:\SQL Backups\TEST\'
SELECT @FileName = @BkpPath + @FileName

DECLARE @SQL varchar(max)

SELECT @SQL = 
'BACKUP DATABASE ''' + @DBName + ''' TO 
	DISK ='+ @FileName + '
	WITH NOFORMAT, INIT,SKIP, NOREWIND, NOUNLOAD,  STATS = 10'

exec (@SQL)

Open in new window

0
 

Author Comment

by:Robert Hamel
ID: 35040261
I tried the items above from both of you, none worked.
when I added @ in front of 'E:\SQL Backups\TEST\'
it says you must declare scalar variable.

There is a WITH in the @SQL variable, line 17.  Adding the semi colon did make the with error go away.  Thanks.

When I tried Sharath's code, i see this, not liking the database name for some reason.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'MyDatabase'.



DECLARE @Filename varchar(256)
DECLARE @BkpPath varchar(256) 
DECLARE @DBName varchar(256)

SET @BkpPath = 'E:\SQL Backups\TEST\'

SELECT @DBName = 'MyDatabase'
SELECT @FileName = @DBName + 'Backup.BAK'
--SELECT @BkpPath = 'E:\SQL Backups\TEST\'
SELECT @FileName = @BkpPath + @FileName

DECLARE @SQL varchar(max)

SELECT @SQL = 
'BACKUP DATABASE ''' + @DBName + ''' TO 
	DISK ='+ @FileName + '
	;WITH NOFORMAT, INIT,SKIP, NOREWIND, NOUNLOAD,  STATS = 10'

exec (@SQL)

Open in new window

0
 
LVL 41

Accepted Solution

by:
Sharath earned 350 total points
ID: 35041116
Thats my mistake. Try this.
DECLARE @Filename varchar(256)
DECLARE @BkpPath varchar(256) 
DECLARE @DBName varchar(256)

SET @BkpPath = 'E:\SQL Backups\TEST\'

SELECT @DBName = 'MyDatabase'
SELECT @FileName = @DBName + 'Backup.BAK'
--SELECT @BkpPath = 'E:\SQL Backups\TEST\'
SELECT @FileName = @BkpPath + @FileName

DECLARE @SQL varchar(max)

SELECT @SQL = 
'BACKUP DATABASE ' + @DBName + ' TO 
	DISK ='''+ @FileName + '''
	WITH NOFORMAT, INIT,SKIP, NOREWIND, NOUNLOAD,  STATS = 10'

exec (@SQL)

Open in new window

0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question