MS SQL Transaction Log Issue

[paulmeta]

We enable transaction log archiving at every hour on our SQL server for providing Point-in-time recovery by log roll forward. However, after perform Full backup by Networker, we found that we cannot restore the SQL Server transaction log. See example below:



1200 1300 1400 1500 1600 1700

|-----|-----|-----|-----|-----|

^ ^ ^ ^ ^ ^ ^

A B C D E F G



a) SQL Transaction log backup is carried out during A, C, D, E, F and G.



b) In point (B)12:30, Networker is activated to carry out a full backup.



c) When try to restore the backup for point (D), we were not able to catch-up in restoring the transaction log for point (C) although we were able to restore the transaction log for point (D). As such, there will be data integrity problem in the restored database.



We will like to know how to backup and restore the database in discrete point-in-time (i.e. C, D, E, F and G) with data integrity.



NetWorker Software Detail:

Networker server 7.3.2 (Jumbo Update1)

Networker Storage Node 7.3.2 (Jumbo Update1)

Networker client 7.3.2 (Jumbo update1)

Networker MSSQL Module 5.0

 

[Rif123]

Can you explain how you did the restore? If you use NetWorker and select a point in time to restore to, I don't think you will end up with a successful restore unless the full and all the transaction logs have been restored and then rolled forward. I cant realy see what is going wrong in your environment.

 

[wallace88]

Assume that you did a full backup at point B and incremental backups at point C and D.

Then if you perform a database recovery right after point D, then it would recover the full backups, then recover and playback the transaction logs from C and D. Unless in the recover options you disabled replaying the transaction logs, or there was a problem when backing up at point C.


Of course, without actual logs to look at, this is all hypothetical...