Sql Restore issue of starting from transaction log.

Question

Sql Restore issue of starting from transaction log.

124420294

Old Hand

Points: 344
More actions
October 26, 2016 at 2:15 am

#146169

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

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

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

Carolyn Richardson SSCrazy Eights Points: 8357 More actions · Answer 1

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: 287026 More actions · Answer 2

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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