0

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.