脚本错误：SQLCMD：“与”关键字附近有语法错误

Question

当它来与确保备份越来越任务到位，我还是有点担心，因为我们使用的是SQL Express和发现，是不是真的有一种无需参与脚本管理的方法。一般来说，我对脚本没问题，但是对于SQL语法，我有点不太流行......这是我的脚本。

declare @currentDate datetime 
set @currentDate = GetDate() 
declare @fileName varchar(255) 

set @fileName = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AlphaDB_PRD_Trans_'  
+ cast(Year(@currentDate) as varchar(4)) 
+ Replicate('0', 2 - Len(cast(Month(@currentDate) as varchar(2)))) 
    + cast(Month(@currentDate) as varchar(2)) 
+ Replicate('0', 2 - Len(cast(Day(@currentDate) as varchar(2)))) 
    + cast(Day(@currentDate) as varchar(2)) 
+ '_' +  
+ Replicate('0', 2 - Len(cast(DatePart(hour, @currentDate) as varchar(2)))) 
    + cast(DatePart(hour, @currentDate) as varchar(2))  
+ Replicate('0', 2 - Len(cast(DatePart(minute, @currentDate) as varchar(2)))) 
    + cast(DatePart(minute, @currentDate) as varchar(2)) + '.TRN'; 

BACKUP LOG [AlphaDB_PRD] TO DISK = @fileName with DESCRIPTION = N'AlphaDB_PRD-Transaction Log Backup', NOFORMAT, INIT, NAME = N'AlphaDB_PRD-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 
GO 
declare @backupSetId as int 
select @backupSetId = position from msdb..backupset where database_name=N'AlphaDB_PRD' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'AlphaDB_PRD') 
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''AlphaDB_PRD'' not found.', 16, 1) end 
RESTORE VERIFYONLY FROM DISK = @fileName WITH FILE = @backupSetId, NOUNLOAD, NOREWIND 
GO 

我想我最初的问题是，我宣布为filename 但实际文件中的变量的方式不会获取保存：AlphaDB_PRD_Trans_20130426_0738.TRN

我看到这个链接：Incorrect syntax near the keyword 'with'. 但我得到另一个错误时包括我刚才;关键字WITH前：

Msg 102, Level 15, State 1, Server ALPHASRVPRD, Line 17 
Incorrect syntax near '='. 
Msg 137, Level 15, State 2, Server ALPHASRVPRD, Line 4 
Must declare the scalar variable "@fileName". 

和错误把我带回到我最初的想法是个Ë问题是与我的声明变量..

Answer 1

的BACKUP LOG ...语句后，你有一个GO关键字。这表示“批量结束”为SQL。这也意味着，在GO之前声明的变量不能再GO后使用。

如果您删除脚本中的GO关键字（在BACKUP LOG ...和declare @backupSetId as int之间），则脚本应该可以工作。

报告错误“near keyword”与“”很可能是指您在RESTORE VERIFYONLY行（显然）在另一个关键字'with'附近使用@filename。