SQL Backup Scripts

I am going through all of our different sql servers and correcting some backup scripts that were failing. This is the script that I'm using as a single step in an SQL Agent Job:

DECLARE @FILENAME AS VARCHAR(255)
SELECT @FILENAME = N'D:\SQL Backups\AERT\AERT_Full_' + CAST(GETDATE() AS VARCHAR(20)) + '.bak'

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

Open in new window

This code is failing with this error: Msg 137, Level 15, State 2, Line 4
Must declare the variable '@FILENAME'.

This particular script will hopefully be used on SQL Server 2000.
Carla RomereDirector of Information TechnologyAsked:
Who is Participating?
 
PadawanDBAConnect With a Mentor Operational DBACommented:
The GO command terminates the batch and the variable declaration/definition along with it.  Go ahead and try removing that.
0
 
Carla RomereDirector of Information TechnologyAuthor Commented:
This was perfect.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.