Restore the .Trn files from the directory folder
-
Hello Experts !
with the help of below topic https://www.sqlservercentral.com/Forums/Topic1817731-3412-1.aspx I tired to some modification the below script which is posted having no errors and no content in log file ,but it does not restore the files from the directory I am wondering what's a issue ,help please.
The output resultsRESTORE DATABASE successfully processed 0 pages in 1.696 seconds (0.000 MB/sec).
DIR/bD:\ReplicationViaFTPServer\
RESTORE DATABASE successfully processed 0 pages in 4.219 seconds (0.000 MB/sec).
RESTORE DATABASE DBName WITH STANDBY ='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_DBName.BAK'USE master;
GO
SET NOCOUNT ON
DECLARE @dbName sysnameDECLARE @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)
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)
SET @dbName='DBName'
SET @backupPath='D:\ReplicationViaFTPServer\'
SET @NewLocation='D:\ReplicationViaFTPServer\LogBackups\'
RESTORE DATABASE DBName WITH NORECOVERY
SET @StandByFile='''C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_DBName.BAK'''
BEGIN TRY
--- Get List of FilesSET @cmd = 'DIR/b' + @backupPath INSERT INTO @filelist(backupFile)
EXEC master.sys.xp_cmdShell @cmd
PRINT @cmd -- prints---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 filesSET @cmd ='RESTORE LOG' + @dbName + 'FROM DISK =''' + @backupPath + @backupFile + '''WITH NORECOVERY'
EXECUTE (@cmd)
PRINT @cmd
--- delete for move log file
---SET @cmd ='del' +@backupPath + @backupFile
SET @cmd = 'MOVE' +@backupPath + @backupFile + '' + @NewLocationEXEC master.sys.xp_cmdshell @cmd
FETCH NEXT FROM backupFiles INTO @backupFile
ENDEND TRY
BEGIN CATCHCLOSE backupFiles
DEALLOCATE backupFiles---5 put database on standby state
SET @cmd ='RESTORE DATABASE '+ @dbName + ' ' + 'WITH STANDBY =' + @StandByFile
EXECUTE (@cmd)
PRINT @cmd
exec master..xp_cmdshell 'echo Error in Restore logs > D:\ReplicationViaFTPServer\LogBackups\ErrorLog\log.txt'
END CATCH
CLOSE backupFiles
DEALLOCATE backupFiles---- 6 put database on standby state
SET @cmd ='RESTORE DATABASE '+ @dbName + ' ' + 'WITH STANDBY =' + @StandByFile
EXECUTE (@cmd)
PRINT @cmd -
You will see 0 pages if no changes happened between log backups.
-
The last .trn file (storetrn.trn) as shown in picture 1 manually, after that trying to run the script to restoring the files ,but having no change in the status of server DB . Picture2 having the new .trn files available.
-
did you modify the data or table definitions prior to taking the log backup? If no changes were applied to the database itself, the log restore would update 0 pages.
- yelouati - Sunday, March 17, 2019 1:58 PM
I think no..
Let me take the full backup and .trn file backup and restore from the scratch and have a try to run the script again to restore the latest .trn file
-
The same issue processed 0 page. I am restoring the full backup in standby/ read only mode .
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply