Database Restore script says Cannot open backup device when path passed to FROM DISK using variable

Question

I have the following script to restore a database. On the line that says:

FROM DISK = @backup_location --N'C:\Temp\TempDB.bak'

If I use the @backup_location variable as above, it fails with the error:

Msg 3201, Level 16, State 2, Line 24 Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Backup\C'. Operating system error 2(The system cannot find the file specified.). Msg 3013, Level 16, State 1, Line 24 RESTORE DATABASE is terminating abnormally.

If I hard code the location using:

FROM DISK = N'C:\Temp\TempDB.bak'

It works fine.

How can I use a variable to specify the database path?

USE [master]

Go

-- DECLARE VARIABLES:
DECLARE @dbname SYSNAME
DECLARE @backup_location VARCHAR

SET @dbname = 'TempDB';
SET @backup_location = N'C:\Temp\TempDB.bak';

-- BEGIN: KILL ACTIVE CONNECTIONS
Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null
Begin
        Execute ('Kill ' + @spid)
        Select @spid = min(spid) from master.dbo.sysprocesses
        where dbid = db_id(@dbname) and spid > @spid
End
-- END: KILL ACTIVE CONNECTIONS

-- RESTORE DB:
RESTORE DATABASE @dbname
    FROM  DISK = @backup_location --N'C:\Temp\TempDB.bak'
    WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5

Go

Thanks for your time.

Answer 1

Change the last few lines:

-- RESTORE DB:
declare @statement varchar(max)
--allow for quotation marks in @backup_location
select @statement =
    'RESTORE DATABASE ' + @dbname + 
    'FROM  DISK = ''' + replace(@backup_location, '''', '''''') +
    'WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5'
exec (@statement)

Answer 2

@codenoir, thanks for the response. Once I saw your varchar(max) I changed @backup_location variable declaration to the same and it worked without the need for exec.

So final code was:

USE [master]

GO

-- DECLARE VARIABLES:
DECLARE @dbname SYSNAME
DECLARE @backup_location VARCHAR(MAX)

SET @dbname = 'TestDB';
SET @backup_location = N'C:\Temp\TestDB.bak';

-- BEGIN: KILL ACTIVE CONNECTIONS
DECLARE @spid INT
SELECT @spid = MIN(spid) FROM master.dbo.sysprocesses
WHERE dbid = DB_ID(@dbname)
WHILE @spid Is Not Null
BEGIN
        EXECUTE ('Kill ' + @spid)
        SELECT @spid = MIN(spid) FROM master.dbo.sysprocesses
        WHERE dbid = db_id(@dbname) and spid > @spid
END
-- END: KILL ACTIVE CONNECTIONS

-- RESTORE DB:
RESTORE DATABASE @dbname
    FROM  DISK = @backup_location
    WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5

Go