i have wrie a sql agent job for auto restored dababase
code:------------------------------------------------------------------------
ALTER DATABASE PPM
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
use[PPM]
DECLARE @dbName nvarchar(1000)
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)
declare @RestoreFilePath nvarchar(500)
SET @dbName = 'PPM_backup'
SET @backupPath = 'F:\FTPSQL\'
SET @cmd = 'DIR /b "' + @backupPath + '"'
INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd
SELECT @lastFullBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.BAK'
AND backupFile LIKE @dbName + '%'
set @RestoreFilePath ='' + @backupPath + @lastFullBackup + ''' WITH NORECOVERY,FILE=1, NOUNLOAD, REPLACE, STATS = 10 '
RESTORE DATABASE [PPM]
FROM DISK = @RestoreFilePath
ALTER DATABASE PPM SET MULTI_USER
error:-------------------------------------------------------------------------------
NT AUTHORITY\LOCAL SERVICE. RESTORE cannot process database because it is in use by this session. It is recommended that the master database be used when performing this operation. [SQLSTATE 42000] (Error 3102) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
Please Help me