Must declare scalar variable @filename

Transact-SQL
1

Hello,

I have problems with a Restore job and I cannot find what is wrong with my code please help Me

ERROR MSG :
.Net SqlClient Data Provider: Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@fileName".

MY CODE :
-- PASO #1 : Declaramos Variables
DECLARE @path VARCHAR(256) -- Ruta Archivo De Respaldo
DECLARE @name VARCHAR(50) -- Base De Datos De Respaldo
DECLARE @fileDate VARCHAR(20) -- Fecha De Archivo De Respaldo
DECLARE @fileName VARCHAR(256) -- Ruta & Nombre De Respaldo

SET @path ='E:\Program Files\Microsoft SQL Server\MSSQL10_50.VKSQL2008\MSSQL\Backup' -- Especificamos Ruta De Respaldo
SET @name ='006' -- Especificamos Base De Datos De Respaldo
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) -- Especificamos Fecha De Respaldo
SET @fileName = @path + @name + '_' + @fileDate + '.BAK' -- Obtenemos Ruta & Nombre Respaldo

-- PASO #2 : Cambiar Modo de BD a User Single Mode.
USE [MASTER]
GO
ALTER DATABASE [007]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

-- PASO #3 : Comenzar Restauracion
USE [MASTER]
GO
RESTORE DATABASE[007] FROM DISK = @fileName;
WITH
MOVE '006_Dat' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.VKSQL2008\MSSQL\DATA\007.mdf',
MOVE '006_Log' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.VKSQL2008\MSSQL\DATA\007.ldf',
RECOVERY,REPLACE,STATS=10;

-- PASO #4 : Regresar Modo de BD a Multi User Mode.
USE [MASTER]
GO
ALTER DATABASE [007] SET MULTI_USER
GO

Thanks in advance

2

You need dynamic sql

Declare @backup varchar(max)

Select @backup =

' BACKUP DATABASE [006]
TO DISK = ' + @pathwithname + 
' WITH NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10'

Print @backup ---to see if it is a clean script
Exec(@backup)
3

If I print the result for @fName variable it is Ok : E:\Program Files\Microsoft SQL Server\MSSQL10_50.VKSQL2008\MSSQL\Backup\006_20200301.BAK

the problem is located on the next step :

-- PASO #3 : Comenzar Restauracion
RESTORE DATABASE[007] FROM DISK = @fileName;
WITH
MOVE '006_Dat' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.VKSQL2008\MSSQL\DATA\007.mdf',
MOVE '006_Log' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.VKSQL2008\MSSQL\DATA\007.ldf',
RECOVERY,REPLACE,STATS=10;

4

you cant do that. you need dynamic sql, did you see what I posted?

declare @cmd = nvarchar(max)
set @cmd  = 'RESTORE DATABASE[007] FROM DISK =' + @fileName + ' ;
WITH
MOVE '006_Dat' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.VKSQL2008\MSSQL\DATA\007.mdf',
MOVE '006_Log' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.VKSQL2008\MSSQL\DATA\007.ldf',
RECOVERY,REPLACE,STATS=10;

print @cmd
exec(@cmd)
5

I received this

.Net SqlClient Data Provider: Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='.
.Net SqlClient Data Provider: Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@fileName".
.Net SqlClient Data Provider: Msg 132, Level 15, State 1, Line 5
The label 'E' has already been declared. Label names must be unique within a query batch or stored procedure.
.Net SqlClient Data Provider: Msg 105, Level 15, State 1, Line 5
Unclosed quotation mark after the character string ',
RECOVERY,REPLACE,STATS=10;

print @cmd
exec(@cmd)
'.

6

well use the proper name you have for the filename whether @fName or @filename

7

That is what Im trying to do,
My original code works ok, the problem is that it use an statical name of file but and I have to do this restore process with special file name (db name+date of day) this is that code :

PASO OPCIONAL: Obtener el nombre y la ruta del archivo de respaldo a utilizar.

RESTORE FILELISTONLY
FROM DISK = 'F:\Exact\006_backup_2019_10_31_210003_1975637.bak'
GO

PASO #1 : Cambiar Modo de BD a User Single Mode.

USE MASTER
GO
ALTER DATABASE [007]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

PASO #2 : Comenzar Restauracion

USE [MASTER]
GO
RESTORE DATABASE[007] FROM DISK='E:\Program Files\Microsoft SQL Server\MSSQL10_50.VKSQL2008\MSSQL\Backup*006_backup_2019_10_31_210003_1975637.bak'*
WITH
MOVE '006_Dat' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.VKSQL2008\MSSQL\DATA\007.mdf',
MOVE '006_Log' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.VKSQL2008\MSSQL\DATA\007.ldf',
RECOVERY,REPLACE,STATS=10;

Paso #3 : Regresar Modo de BD a Multi User Mode.

USE MASTER
GO
ALTER DATABASE [007] SET MULTI_USER
GO

8

ok sorry you need extra quotes

Select @backup =

'RESTORE DATABASE[007] FROM DISK =' + @fileName + ' ;
WITH
MOVE ''006_Dat'' TO ''E:\Program Files\Microsoft SQL Server\MSSQL10_50.VKSQL2008\MSSQL\DATA\007.mdf'',
MOVE ''006_Log'' TO ''E:\Program Files\Microsoft SQL Server\MSSQL10_50.VKSQL2008\MSSQL\DATA\007.ldf'',
RECOVERY,REPLACE,STATS=10;'

9

Im receiving same error :
.Net SqlClient Data Provider: Msg 137, Level 15, State 2, Line 5
Must declare the scalar variable "@fileName".

10

well you have to declare it amigo!

You need dynamic sql

DECLARE @name VARCHAR(50) -- Base De Datos De Respaldo
DECLARE @fileDate VARCHAR(20) -- Fecha De Archivo De Respaldo
DECLARE @fileName VARCHAR(256) -- Ruta & Nombre De Respaldo

SET @path ='E:\Program Files\Microsoft SQL Server\MSSQL10_50.VKSQL2008\MSSQL\Backup' -- Especificamos Ruta De Respaldo
SET @name ='006' -- Especificamos Base De Datos De Respaldo
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) -- Especificamos Fecha De Respaldo
SET @fileName = @path + @name + '_' + @fileDate + '.BAK' -- Obtenemos Ruta & Nombre Respaldo


ALTER DATABASE [006]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;


Declare @backup varchar(max)

Select @backup =

' BACKUP DATABASE [006]
TO DISK = ' + @fileName + 
' WITH NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10'

Print @backup ---to see if it is a clean script
Exec(@backup)

-- PASO #4 : Regresar Modo de BD a Multi User Mode.
USE [MASTER]
GO
ALTER DATABASE [006] SET MULTI_USER
GO
1 Like
11

A GO ends a batch, and thus destroys all local variables.

Just get rid of all the GOs and try it again.

1 Like
12

Thanks a lot ScottPletcher, You were right, changing the declarations of variables and the assignment of values after the "GO" everything worked perfect.

Thank You soooooooo much my friend.

Kind Regards...