Restore Database

Hello Experts, I am trying to restore a database through a sql agent job but it won't work, I do get a successful message though with no errors. It takes 0 second to complete.

However, when i run it in a tsql window it works fine.

TSQL command:
RESTORE DATABASE [ML_Mortgages] FROM DISK = '\\CORPMLDB0\ML_LQB\CORPMLDB0\ML_LQB\FULL\CORPMLDB0_ML_LQB_FULL_20180816_111134.bak' WITH RECOVERY, REPLACE

SQL Agent Job:

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 = 'ML_Mortgages'
SET @backupPath = '\\CORPMLDB0\ML_LQB\CORPMLDB0\ML_LQB\FULL\'

-- 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 RECOVERY, REPLACE'

--PRINT @cmd

-- 5 - check for log backups
DECLARE backupFiles CURSOR FOR
   SELECT backupFile
   FROM @fileList
   --WHERE backupFile LIKE '%.TRN'
   WHERE 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

   FETCH NEXT FROM backupFiles INTO @backupFile
END

CLOSE backupFiles
DEALLOCATE backupFiles


EXECUTE sp_executesql @cmd
Fay ADBAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
It's extremely likely that the job doesn't have authority to access the file:
\\CORPMLDB0\ML_LQB\CORPMLDB0\ML_LQB\FULL\CORPMLDB0_ML_LQB_FULL_20180816_111134.bak
although typically you would get an error indicating that.

If you have sysadmin authority, then the job will execute as the service account (it does not execute under your account).  That account probably can't access that folder.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fay ADBAAuthor Commented:
The user has all the permissions. I can restore a different database on the same sql box using the same backup path but not the ML_Mortgages db.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.