Restore the .Trn files from the directory folder

  • smer

    SSCommitted

    Points: 1542

    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 results

    RESTORE 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 Files

    SET @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 files

    SET @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 + ” + @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)
    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 

  • yelouati

    SSC Enthusiast

    Points: 108

    You will see 0 pages if no changes happened between log backups.

  • smer

    SSCommitted

    Points: 1542

    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.


  • yelouati

    SSC Enthusiast

    Points: 108

    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.

  • smer

    SSCommitted

    Points: 1542

    yelouati - Sunday, March 17, 2019 1:58 PM

    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.

    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

  • smer

    SSCommitted

    Points: 1542

    The same issue processed 0 page.  I am restoring the full backup  in standby/ read only mode .  

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply