Post reply

Restore the .Trn files from the directory folder

  • March 16, 2019 at 11:05 am

    #409704

    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 

  • March 17, 2019 at 12:16 pm

    #2024728

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

  • March 17, 2019 at 12:47 pm

    #2024730

    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.


  • March 17, 2019 at 1:58 pm

    #2024731

    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.

  • March 17, 2019 at 2:07 pm

    #2024733

    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

  • March 17, 2019 at 11:39 pm

    #2024743

    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