SQL Server Performance

restore error

Discussion in 'SQL Server Log Shipping' started by Reddy, May 8, 2007.

  1. Reddy New Member

    restore log EmployeeTEST from disk =
    'C:TranBAckupEmployeeEmployee_tlog_200705080000.TRN'
    with norecovery

    when i do this i get this error

    Server: Msg 4306, Level 16, State 1, Line 1
    The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step.
    Server: Msg 3013, Level 16, State 1, Line 1
    RESTORE LOG is terminating abnormally.


    Thanks!
    "He laughs best who laughs last"

  2. satya Moderator

    Have you restore the previous database backup using WITH NORECOVERY or WITH STANDBY as per the message?

    It is obvious that you need to start the sequence again in order to restore further logs. If this is a part of your log shipping sequence then you must restore the full source database backup on the destination database by using either the NORECOVERY or the STANDBY option for log shipping to work.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  3. Reddy New Member

    when I restore the full DB with NORECOVERY and try restoring the TRN file it kicks an error like

    Server: Msg 4326, Level 16, State 1, Line 1
    The log in this backup set terminates at LSN 6445000004475400001, which is too early to apply to the database. A more recent log backup that includes LSN 6445000004475600001 can be restored.
    Server: Msg 3013, Level 16, State 1, Line 1
    RESTORE LOG is terminating abnormally.


    I wud like to know 1 thing ..
    I want to do a full DB backup once and restore it only once(for ever) on standby server and from then I want apply TRN backups to the standby server so tht both servers are in synch. few doubts..
    1)when i do a full DB restore with NORECOVERY, can users connect to the DB and work on it.
    2)after full back up i want to apply TRN file with NORECOVERY so tht I can apply next TRN file, mean while can users connect to the DB and work?
    3)what If I miss a sequence of a TRN file, can i apply the next available TRN file or shud i go for full backup and then start from there again.

    Thanks!
    "He laughs best who laughs last"

  4. satya Moderator

    THat means you have to ensure the log backup is taken after the full database backup and the events you apply on the sequence should match (LSN).

    1) NO RECOVERY means the database will be LOADING state and will not accept any connections, basically the users will not be able to see it.
    2) See 1 NO RECOVERY is not possible to keep the users online, you have to use WITH STANDBY or WITH RECOVERY to make it online.
    3) If u miss the sequence then ensure to apply the last ones in order,.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  5. Reddy New Member

    when am initially restoring full backup with recovery am unable to apply TRN backup on it and when I restore it with NORECOVERY my database is in the state of Loading and my users can not connect to it. I still dont understand how can i do 1 time restore of Full backup and apply TRN files from there on to keep my DB synch and also my users shud be able to work on the target DB. pls guide me...

    what I understand is once am done with my TRN file restore and if i make it online to use then i may not be able to apply TRN files over that, am i right?

    Thanks!
    "He laughs best who laughs last"

  6. MohammedU New Member

    1. Restore full backup with NORECORY (if you don't want to check the data) OR use STANDBY option (if you want to check the data) after full restore.
    2. Apply alll the transaction log backup with NORECOVERY or STANDBY option and last transaction log backup must be with RECOVERY option...
    3. Allow the user to connect...


    You can make read the data in between the log restore if you use STANDBY option only...with NORECOVERY option will not allow you to connect...

    See BOL for details..

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  7. satya Moderator

    In order to restore the transaction logs you must restore full database using NO RECOVERY option or even STANDBY option. As suggested you have to look around the latest books online for recovery models and restore statement topics.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  8. Reddy New Member

    so when i restore WITH STAND BY, y does my DB shows Loading..... status


    and how about Differential backup in my scenario..does it work. like.. 1 full backup/restore when initiating and 1 differential backup/restore every day..

    Thanks!
    "He laughs best who laughs last"

  9. satya Moderator

    The "STANDBY" will not to rollback any uncommitted transactions and to allow read-only access to the database. This is required to specify a file to be used to "undo" any transactions.

    The most recent differential backup contains all changes from all previous differential backups performed since the most recent complete database backup. Differential backups may be considered as an alternative for databases that are large and are modified infrequently. Also make a note that they do not provide point-in-time restore capabilities and an may only be restored after a complete database backup is restored


    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  10. prabhabk New Member

    If you are looking for Reporting Server with log shipping.You must Restore the Full Backup with Standby.
    It will shows the Database status as read-only not loading status..
    After that you have to apply the log sequences with standby option.

    Thanks,
    Prabhakaran
    MS SQL Server DBA
  11. satya Moderator

    ... good [<img src='/community/emoticons/emotion-1.gif' alt=':)' />], but in this case during the log restore the database must be in exclusive use of log restore sequence by having downtime on reporting.<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
  12. prabhabk New Member

    Thanks for your comment satya...
    Yes there will be a downtime till the restore process of log backup and you have to kill all active connections to restore the log backup otherwise the Restore process will fail.

    Thanks,
    Prabhakaran
    MS SQL Server DBA

Share This Page