• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 397
  • Last Modified:

SQL Server Script Syntax error

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
Robert Hamel
Asked:
Robert Hamel
  • 4
  • 2
2 Solutions
 
Rajkumar GsSoftware EngineerCommented:
In first look, I feel this change

SELECT @SQL =
'BACKUP DATABASE ' + @DBName + ' TO
 DISK ='+ @FileName + ' WITH NOFORMAT, INIT,SKIP, NOREWIND, NOUNLOAD,  STATS = 10'
0
 
Rajkumar GsSoftware EngineerCommented:
seems like you missed appending + ' after @FileName

Raj
0
 
Rajkumar GsSoftware EngineerCommented:
Also if required , try this change also


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

to


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

Raj
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
Rajkumar GsSoftware EngineerCommented:
>> 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
 
SharathData EngineerCommented:
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
 
Robert HamelAuthor Commented:
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
 
SharathData EngineerCommented:
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now