point in time restore with a bad TRN in sequence

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

point in time restore with a bad TRN in sequence

Author

Topic

eharlow
Starting Member

3 Posts

Posted - 2009-03-07 : 14:14:08

We're running SQL Server 2000 server on WindowsServer2003 with database 'x' is in full recovery mode. The maintenance plans/jobs create BAKs nightly and TRNs every two hours. We've done point in time restores previously, but are having the following issue now:

The 2nd TRN (x2.TRN, below) in the sequence is bad and is stopping me from restoring beyond it (to look at activity later in the day).

x.BAK
Time: 1am
FirstLSN: 7858000000024900001
LastLSN: 7858000000025200001
RecoveryForkID: 9F8347A2-2491-4C9E-80D4-D42DF0172A6F

x1.TRN
Time: 3am
FirstLSN: 7858000000024900001
LastLSN: 7918000000005400001
RecoveryForkID: 9F8347A2-2491-4C9E-80D4-D42DF0172A6F

x2.TRN
Time: 5am
FirstLSN: 7947000000001600002
LastLSN: 7947000000004200001
RecoveryForkID: C5761F6C-CE81-4379-850A-EA5842039611

x3.TRN
Time: 7am
FirstLSN: 7947000000004200001
LastLSN: 7947000000004200001
RecoveryForkID: C5761F6C-CE81-4379-850A-EA5842039611

etc.
etc.

Based on the TRN file names, it is clear we are trying to restore in proper sequence. We can successfully restore the BAK and x1.TRN using this code...


  RESTORE DATABASE [x] 
  FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\x.BAK' 
  WITH NORECOVERY, REPLACE

  RESTORE LOG [x] 
  FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\x1.TRN' 
  WITH RECOVERY

I'm a little surprised we can even restore x1.TRN because its FirstLSN doesn't match the BAK's LastLSN. Regardless, when running this code...


  RESTORE DATABASE [x] 
  FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\x.BAK' 
  WITH NORECOVERY, REPLACE

  RESTORE LOG [x] 
  FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\x1.TRN' 
  WITH NORECOVERY

  RESTORE LOG [x] 
  FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\x2.TRN' 
  WITH RECOVERY

...we receive the following error:

Processed 6600 pages for database 'x, file 'x_Data' on file 1.
Processed 1 pages for database 'x', file 'x_Log' on file 1.
RESTORE DATABASE successfully processed 6601 pages in 6.051 seconds (8.935 MB/sec).
Msg 4305, Level 16, State 1, Line 5
The log in this backup set begins at LSN 7947000000001600002, which is too recent to apply to the database. An earlier log backup that includes LSN 7858000000025200001 can be restored.
Msg 3013, Level 16, State 1, Line 5
RESTORE LOG is terminating abnormally.

Based on the FirstLSNs and LastLSNs of x1.TRN and x2.TRN, this error makes sense. Conceptually, it looks like the train jumped the track between x1.TRN and x2.TRN. Looking in the Windows Event Viewer, we see an application log error happened after x1.TRN was created successfully. The error is 18278 (Database log truncated: x). So, it appears x1.TRN was created then an error happened when trying to truncate the log.

All that said, a user is reporting 'lost data' from that day, which is why we're investigating. I'm really interested in the activity around 4pm (x7.TRN and x8.TRN). Everything I read on the web so far says "TRNs must be applied in sequence" - period. Is there a way to either repair or skip TRNs on way to a point in time restore? Any ideas or experience dealing with such a thing?

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-07 : 14:55:32

Hi,

I think you might be out of luck on restoring the database. Anytime a truncate takes place you need to do full backup to reestablish the t-log backup chain. That said you might be able to use ApexSQL Log reader to see what had happened in that TLog file. I have not used it broken chain; but if you put in just x2.trn you might be able to read it... Thanks.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-07 : 15:44:43

Can you specify what is the status of database as of now? Is it Corrupt/Suspect?

eharlow
Starting Member

3 Posts

Posted - 2009-03-09 : 10:24:47

sodeep, the status of the database is 'normal'.

eharlow
Starting Member

3 Posts

Posted - 2009-03-09 : 10:30:57

guptam,

I'll check out ApexSQL log reader and cross my fingers. I'm looking into why a truncate took place without a full backup thereafter. Thanks for the feedback.

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-09 : 14:19:23

ApexSQL Log reader is a life saver; it helped me narrow down changes when we had SQL Injection on a db I don't manage. And client didn't notice about it until almost 2-3 days later. So log reader was nice way to figure out when and what changed :).

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources