Trouble Restoring Transaction Log Backups

[russellbcopeland]

My Situation

A good sized (35 GB or so) SQL 2000 database has 2 backup devices. 1 for the full backup and one for the transaction log backups.

Full backups are done every night and transaction log backups are done every hour except for when the full backup is being run. Immediatley after the full backup is completed a transaction log backup step is used to overwright the old transaction log backups to create a fresh file that the regular hourly transaction log backups will be appended to.

I want to keep the full backup separate from the transaction log backup because I copy the backup to another server after they are completed and do not want to copy a 35GB file each hour. This has worked great and fast except I can't seem to restore my transaction log backups.

I restored a full backup with norecovery then tried to restore the transaction log backup that was done immediatley after and SQL 2000 server is telling mme that the LSN's do not match. I have checked and my LSN's for my transaction log backups all line up correctly except the initialLSN for my first transaction log backup is higher than the lastLSN for the full system backup. According to the SQL Server 2000 docs (Books Online and Microsofts Knowledge base) this is normal except that I cannot restore the transaction log backups.

Any help on what I am doing wrong will be greatly appreciated.

Thanks
Russell

 

[RickCole]

I have no problem doing this on SQL Server 7 so it might be a quirk in 2000. The only other thing I can think of is that Truncate log on checkpoint is set after the full restore which would cause the above error. Sure you've got that covered so hopefully someone else may be able to help.

Rick.