0

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..

Community
  • 1
  • 1
Randall.Cummins
  • 426
  • 1
  • 8
  • 20
  • Belongs on http://dba.stackexchange.com – YasirA Apr 26 '13 at 12:08
  • Thanks, I posted it there as well. when I find an answer I will include it below for consistency. – Randall.Cummins Apr 26 '13 at 12:45
  • Actually, you should not create duplicate posts. Just wait till your question is closed as off-topic and migrated to one of the appropriate sites. Thank you. – YasirA Apr 26 '13 at 12:47
  • What happens if you remove the `GO` between the `BACKUP...` line and the `declare @backupSetId ...` line – RoKa Apr 26 '13 at 12:49
  • @YasirArsanukaev I don't know what makes this question off topic... is it just that it fits best with the dba.stackexchange.com? RoKa was able to resolve my question very quickly without going to the other site and there seems to be a lot of related questions regarding SQL syntax. – Randall.Cummins Apr 26 '13 at 13:04
  • These are SQL Server maintenance commands, not SQL commands, and thus the question has to do with database administration, and not with programming in the usual sense. – YasirA Apr 26 '13 at 13:08
  • ah... ok thanks for clarifying, will keep that in mind next time. but I see that my question was closed there for the following reason closed as off topic by JNK♦ 29 mins ago Questions on Database Administrators Stack Exchange are expected to relate to database administration within the scope defined in the FAQ. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about closed questions here. – Randall.Cummins Apr 26 '13 at 13:19
  • 1
    @Randall I think this is because of the limitation where you can close the question as the duplicate only if the other question was created on the same Stackexchange site, but since your original question was created on Stackoverflow, the other duplicate question (now closed on DBA.SE) could not be closed as duplicate. – YasirA Apr 26 '13 at 13:35

1 Answers1

2

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'.

RoKa
  • 160
  • 1
  • 12