Hi Experts !
I am unable to point the issue error
SET @StandByFile='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_TEST.BAK'
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'C:'.
USE master;
GO
SET NOCOUNT ON
--- variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @filelist TABLE (backupFile NVARCHAR(255))
DECLARE @StandByFile NVARCHAR(max)
DECLARE @NewLocation nvarchar(50)
DECLARE @backupFile NVARCHAR(500)
--2 - initialze variables
SET @dbName='TEST'
SET @backupPath='D:\ReplicationViaFTPServer\'
SET @NewLocation='D:\ReplicationViaFTPServer\LogBackups\'
---SET @StandByFile='D:\MSSQL10_50MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_AVM.BAK'
SET @StandByFile='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_AVM.BAK'
BEGIN TRY
--- Get List of Files
SET @cmd = 'DIR/b' + @backupPath INSERT INTO @filelist(backupFile)
EXEC master.sys.xp_cmdShell @cmd
---4 check for log backups
DECLARE backupFiles CURSOR FOR SELECT backupFile FROM @fileList WHERE backupFile LIKE '%.TRN' AND backupFile LIKE @dbName + '%'
OPEN backupFIles
--- Loop through all th files for the database
FETCH NEXT FROM backupFiles INTO @backupFile
WHILE @@FETCH_STATUS =0
BEGIN
--- Restore log files
SET @cmd ='RESTORE LOG' + @dbName + 'FROM DISK =''' + @backupPath + @backupFile + '''WITH NORECOVERY'
EXECUTE (@cmd)
--- delete for move log file
SET @cmd ='del' +@backupPath + @backupFile
SET @cmd = 'MOVE' +@backupPath + @backupFile + '' + @NewLocation
EXEC master.sys.xp_cmdshell @cmd
FETCH NEXT FROM backupFiles INTO @backupFile
END
END TRY
BEGIN CATCH
CLOSE backupFiles
DEALLOCATE backupFiles
---5 put database on standby state
SET @cmd ='RESTORE DATABASE ' + @dbName +'WITH STANDBY=' + @StandByFile
EXECUTE (@cmd)
exec master..xp_cmdshell 'echo Error in Restore logs > D:\ReplicationViaFTPServer\LogBackups\ErrorLog\log.txt'
END CATCH
CLOSE backupFiles
DEALLOCATE backupFiles
--- 5 put database on standby state
SET @cmd ='RESTORE DATABASE ' + @dbName +'WITH STANDBY=' + @StandByFile
EXECUTE (@cmd)