Solved

SQL Server Script Syntax error

Posted on 2011-03-04
7
384 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Upgrading SQL 2005 Express to 2008 R2 Express 31 203
SQL query to summarize items per month 5 85
Downgrading MS SQL 2008 R2 Enterprise to MS SQL 2005 Standard? 12 95
SQL query 7 49
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
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…

734 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