Sql Restore issue of starting from transaction log.

  • 124420294

    SSC Enthusiast

    Points: 184

    Hi Guys,

    I have a issue of applying sql transaction log to database. For example,

    a) create a database "testdb"

    b) BACKUP DATABASE testdb to "d:\testdb.bak"

    c) add data to testdb, add table and add record or something.

    d) BACKUP log testdb to "d:\testdb_log1.trn"

    e) add data to testdb, add table and add record or something.

    f) BACKUP log testdb to "d:\testdb_log2.trn"

    Start to restore

    a) restore database testdb from disk "d:\testdb.bak" with RECOVERY option.

    Now I using RECOVERY instead of NORECOVERY on purpose. And the testdb is on normal state.

    My problem is how can I apply the rest .trn log files( d:\testdb_log1.trn and d:\testdb_log2.trn) to database

    I use VSS to bring database to Restoring state, and then apply log files.

    It reports error like "Could not redo log record (39:386:8), for transaction ID (0:0), on page (1:6), allocation unit 6488064, database 'testdb' (database ID 21). Page: LSN = (39:386:2), allocation unit = 6488064, type = 16. Log: OpCode = 7, context 24, PrevPageLSN: (39:223:9). Restore from a backup of the database, or repair the database.”

    And I check log of sql, and find when I bring database to Restoring, it report info of

    "The database 'testdb' is marked RESTORING and is in a state that does not allow recovery to be run."

    Any replies will be appreciated.

    Thanks

  • Carolyn Richardson

    SSCrazy Eights

    Points: 8194

    I think you already know the answer to this, to restore logs you must first restore the full backup with NORECOVERY you are trying to do something that is incorrect as part of a restore process.

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • Ed Wagner

    SSC Guru

    Points: 286957

    When you do your restores, only the last one gets the WITH RECOVERY option. The ones that aren't the last one get the WITH NORECOVERY option.

  • Gail Shaw

    SSC Guru

    Points: 1004424

    124420294 (10/26/2016)


    Hi Guys,

    I have a issue of applying sql transaction log to database. For example,

    a) create a database "testdb"

    b) BACKUP DATABASE testdb to "d:\testdb.bak"

    c) add data to testdb, add table and add record or something.

    d) BACKUP log testdb to "d:\testdb_log1.trn"

    e) add data to testdb, add table and add record or something.

    f) BACKUP log testdb to "d:\testdb_log2.trn"

    Start to restore

    a) restore database testdb from disk "d:\testdb.bak" with RECOVERY option.

    Now I using RECOVERY instead of NORECOVERY on purpose. And the testdb is on normal state.

    My problem is how can I apply the rest .trn log files( d:\testdb_log1.trn and d:\testdb_log2.trn) to database

    I use VSS to bring database to Restoring state, and then apply log files.

    It reports error like "Could not redo log record (39:386:8), for transaction ID (0:0), on page (1:6), allocation unit 6488064, database 'testdb' (database ID 21). Page: LSN = (39:386:2), allocation unit = 6488064, type = 16. Log: OpCode = 7, context 24, PrevPageLSN: (39:223:9). Restore from a backup of the database, or repair the database.”

    You start the restore over from step a, this time restoring WITH NORECOVERY. Once the database has been recovered, you can't go back and restore log backups.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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