March 16, 2009 at 10:03 pm
Hi,
We have SQL Server 2000 database. We take full back up once in the night and log backup after every hour.
I am trying to restore the full backup on SQL Server 2005 which gets restored successfully but while restoring log backup it gives the following error
Server: Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 36138000007098100001 which is too late to apply to the database. An earlier log backup that includes LSN 36138000006401500001 can be restored.
Even if the log backup is taken just 15 minutes after full backup it gives the same error.The database is in Full recovery mode. It gives the same error on SQL Server 2000 also.
The database is also a subscriber as well as publisher under transactional replication.
What must be the reason for this error?
Thanks in advance.
Regards,
Rohit
March 17, 2009 at 1:11 am
Sounds like you're missing a log backup. Does the job that does the full also do a log backup? It's something I've seen a couple times recently.
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
March 17, 2009 at 1:13 am
Thanks for the reply. There are different jobs running for Full backup as well as log backup. Even though I take a full backup manually,update something and then take the log backup and then try to restore it does not restore the log backup.
March 17, 2009 at 1:16 am
How to check whether replication is messing up with the log?
Regards,
Rohit
March 17, 2009 at 1:17 am
Rohit Chitre (3/17/2009)
How to check whether replication is messing up with the log?
Replication cannot mess up Database and log backups.
Can you check the recovery model? Can you check in the SQL error log just to be 100% sure of what backups are being taken?
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
March 17, 2009 at 1:33 am
Recovery model is Full. SQL Sever error log shows following things for full database backup and log backup.
Here I am just mentioning first two entries of log backup
2009-03-17 00:18:03.18 backup Database backed up: Database: .bak'}).
2009-03-17 03:00:11.78 backup Log backed up: Database: , creation date(time): 2005/05/14(13:21:16), first LSN: 36144:1563:1, last LSN: 36144:8115:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'G:\DB_LOG_BACK\DB_Log'}).
2009-03-17 04:00:10.74 backup Log backed up: Database: , creation date(time): 2005/05/14(13:21:16), first LSN: 36144:8115:1, last LSN: 36144:9197:1, number of dump devices: 1, device information: (FILE=2, TYPE=DISK: {'G:\DB_LOG_BACK\DB_Log'}).
March 17, 2009 at 1:54 am
Also when I right click the database ->all tasks->restore which is being backed up I can see the list of all the backups with Full backup.
When I restore the full backup even on the same server with different name I don't get the list.
Or if I store the database on another server with the same name still I don't get the list of backups.
March 17, 2009 at 2:28 am
From the entries in the log it looks like you should be able to restore the database backup with norecovery and then the two log backups, in sequence, also both with norecovery, followed by a RESTORE DATABASE WITH RECOVERY to bring the whole thing online. The LSNs match correctly, there are no gaps.
From what I can see both log backups are going to the same file. Could it be that you're restoring the wrong one from the file?
Could you try restoring all three, the database backup, the first log backup and then the second, all in sequence, all with the NO_RECOVERY option, and post any errors that you get here.
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
March 17, 2009 at 5:12 am
Thanks a lot. Putting the file number while log restore worked.:-)
Thanks again.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply