SQL Server Msg 2812, level 16 state 62 line 30

Question

I want to make automatic backup and restore my database on SQL Server 2012. I have made automatic backup with script in my Windows server.

Now, I want to make auto restore with script. I have found this below script to do that:

USE Master;
GO 
SET NOCOUNT ON

-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @lastDiffBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)

-- 2 - Initialize variables
SET @dbName = 'Customer'
SET @backupPath = 'D:\SQLBackups\'

-- 3 - get list of files
SET @cmd = 'DIR /b ' + @backupPath

INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd

-- 4 - Find latest full backup
SELECT @lastFullBackup = MAX(backupFile) 
FROM @fileList 
WHERE backupFile LIKE '%.BAK' 
   AND backupFile LIKE @dbName + '%'

SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = ''' 
       + @backupPath + @lastFullBackup + ''' WITH NORECOVERY, REPLACE'
PRINT @cmd

-- 4 - Find latest diff backup
SELECT @lastDiffBackup = MAX(backupFile) 
FROM @fileList 
WHERE backupFile LIKE '%.DIF' 
   AND backupFile LIKE @dbName + '%'
   AND backupFile > @lastFullBackup

-- check to make sure there is a diff backup
IF @lastDiffBackup IS NOT NULL
BEGIN
   SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = ''' 
       + @backupPath + @lastDiffBackup + ''' WITH NORECOVERY'
   PRINT @cmd
   SET @lastFullBackup = @lastDiffBackup
END

-- 5 - check for log backups
DECLARE backupFiles CURSOR FOR 
   SELECT backupFile 
   FROM @fileList
   WHERE backupFile LIKE '%.TRN' 
   AND backupFile LIKE @dbName + '%'
   AND backupFile > @lastFullBackup

OPEN backupFiles 

-- Loop through all the files for the database 
FETCH NEXT FROM backupFiles INTO @backupFile 

WHILE @@FETCH_STATUS = 0 
BEGIN 
   SET @cmd = 'RESTORE LOG ' + @dbName + ' FROM DISK = ''' 
       + @backupPath + @backupFile + ''' WITH NORECOVERY'
   PRINT @cmd
   FETCH NEXT FROM backupFiles INTO @backupFile 
END

CLOSE backupFiles 
DEALLOCATE backupFiles 

-- 6 - put database in a useable state
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' WITH RECOVERY'
PRINT @cmd 

The output shows me correct SQL command if you keep PRINT in this query, but when I change PRINT to EXEC in restore log, it shows me an error:

Msg 2812, Level 16, State 62, Line 64
Could not find stored procedure 'RESTORE LOG yogamanual FROM DISK = 'D:\shared241\yogamanual-0954.trn' WITH NORECOVERY'.
Msg 2812, Level 16, State 62, Line 64
Could not find stored procedure 'RESTORE LOG yogamanual FROM DISK = 'D:\shared241\yogamanual-1002.trn' WITH NORECOVERY'.

Am I wrong? Can you guys help me to fix it? I have searched it in Google, but nothing can solve it.

Answer 1

It would need to be EXEC(@cmd) not EXEC @cmd.

Without the parentheses it looks to find a stored procedure with the same name as the string inside @cmd.

Hence the error you are seeing of

Could not find stored procedure 'RESTORE LOG yogamanual FROM DISK = 'D:\shared241\yogamanual-0954.trn' WITH NORECOVERY'.