Script error: SQLCMD: Incorrect syntax near the keyword 'with'

When it came to getting tasked with ensuring the backups are in place, I was a little worried because we are using SQL Express and found out that there really isn't a way to manage that without a bit of scripting involved. I am ok with scripting in general, but I am a bit unfamilier with SQL syntax... Here's my script.

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

I thought my initial problem was the way I was declaring the variable for the filename but the actual file DOES get saved: AlphaDB_PRD_Trans_20130426_0738.TRN

I saw this link: Incorrect syntax near the keyword 'with'. but I get another error when I include ; just before keyword 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".

and that error takes me back to my initial thought that the issue is with declaring my variable..

Solution

After the BACKUP LOG ... statement, you have a GO keyword. This signals the "end of batch" to SQL. This also means that, any variables declared before the GO cannot be used after the GO again.

If you remove the GO keyword in your script (between the BACKUP LOG ... and declare @backupSetId as int lines), your script should work.

The reported error "near keyword 'with'" most probably refers to your use of @filename in the RESTORE VERIFYONLY line, (obviously) near another keyword 'with'.