PRB: Filegroup Log Restore May Fail with Error Message 4305

This article has been archived. It is offered "as is" and will no longer be updated.
Symptoms
When you restore logs after a filegroup restore, the error message that follows may occur if all of these conditions are true:
  • At least one log backup is performed before the filegroup backup is performed.
  • There are open transactions when the preceding log backup is performed.
  • You are only restoring log backups that were performed after the filegroup backup without restoring the log backup mentioned in the first item in this list.

Error Messages

SQL Server 2000
Server: Msg 4305, Level 16, State 1, Line 2
The log in this backup set begins at LSN 6000000007200001, which is too late to apply to the database. An earlier log backup that includes LSN 6000000005000001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 2
RESTORE LOG is terminating abnormally.
SQL Server 7.0
Server: Msg 4305, Level 16, State 1, Line 2
This backup set cannot be restored because the database has not been rolled forward far enough. You must first restore all earlier logs before restoring this log.
Server: Msg 3013, Level 16, State 1, Line 2
Backup or restore operation terminating abnormally.
Cause
This behavior is by design.

A complete backup set must include all the logs for any transaction that is active while any data is being backed up. Because log backups never overlap, you may need log backups prior to the file or filegroup backups if there were any active transactions when the files or filegroups were backed up.

A sample stored procedure is provided for you in the "Workaround" section, which allows you to determine the earliest log backup that is needed for a successful filegroup restore.
Workaround
To determine the earliest log that is required to recover with the latest set of file group backups, create a stored procedure similar to the one that follows. You should run this stored procedure whenever you decide to delete some log files to determine which log files you need to preserve for the latest set of filegroup backups. Failure to do so may cause your file group backup to be unusable.
use msdbgocreate procedure requiredlogbck @dbname sysnameas begin	select top 1 * from msdb..backupset 	where first_lsn <= 		(select max(first_lsn) from msdb..backupset where database_name=@dbname and type = 'F')	and type = 'L' and database_name=@dbname order by backup_set_id descendgoexec msdb.dbo.requiredlogbck 'mydb'go				
When you execute the preceding stored procedure execution, it will return at most one row. The backup_start_date that also includes time should inform you as to which is the earliest log to preserve. You must preserve all logs starting from that point forward.

IMPORTANT: If you ever had a database with the same name on the server, you must purge the backup history by using the msdb.dbo.sp_delete_database_backuphistory (only available in SQL Server 2000) stored procedure before you try to back up the current database. Otherwise, you may not receive an accurate report when you use the preceding sample stored procedure because there may be some backup history already entered for the previous database with the same name.

For SQL Server 7.0, here is a sample that simulates functionality similar to the sp_delete_database_backuphistory stored procedure in SQL Server 2000:
use msdbgoCREATE   PROCEDURE sp_delete_database_backuphistory  @db_nm nvarchar(256)ASBEGIN  declare @bsid int  declare @msid int  declare @rows int  declare @errorflag int  declare @str nvarchar(64)  set nocount on  set @errorflag = 0  declare oldbackups insensitive cursor for    select backup_set_id from backupset where database_name=@db_nm    for read only  open oldbackups  fetch next from oldbackups into @bsid  while(@@fetch_status = 0)  begin    begin transaction    set rowcount 1    set @rows = (select count(*) from restorehistory where backup_set_id = @bsid)    set rowcount 0    if (@rows > 0)    begin      delete from restorefile where restore_history_id in (select restore_history_id from restorehistory where backup_set_id = @bsid)      if (@@error <> 0)      begin         rollback transaction         set @errorflag = 1         break      end      delete from restorefilegroup where restore_history_id in (select restore_history_id from restorehistory where backup_set_id = @bsid)      if (@@error <> 0)      begin         rollback transaction         set @errorflag = 1         break      end      delete from restorehistory where backup_set_id = @bsid      if (@@error <> 0)      begin         rollback transaction         set @errorflag = 1         break      end    end    delete from backupfile where backup_set_id = @bsid    if (@@error <> 0)    begin       rollback transaction       set @errorflag = 1       break    end    set @msid = (select media_set_id from backupset where backup_set_id = @bsid)    delete from backupset where backup_set_id = @bsid    if (@@error <> 0)    begin       rollback transaction       set @errorflag = 1       break    end    set rowcount 1    set @rows = (select count(*) from backupset where media_set_id = @msid)    set rowcount 0    if (@rows = 0)    begin      delete from backupmediafamily where media_set_id = @msid      if (@@error <> 0)      begin         rollback transaction         set @errorflag = 1         break      end      delete from backupmediaset where media_set_id = @msid      if (@@error <> 0)      begin         rollback transaction         set @errorflag = 1         break      end    end    commit transaction    fetch next from oldbackups into @bsid  end  deallocate oldbackups  set nocount off  if (@errorflag <> 0)  begin    set @str = (select convert( nvarchar(64), @bsid))    raiserror( 4325, -1, -1, @str )    return(1)  endEND				
More information

File and Filegroup Backups

File and filegroup backups and restores require good planning and testing. When you use a file or a filegroup backup strategy, make sure that you thoroughly test the restore to make sure that it works for you.

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
281122 INF: Restore File and Filegroup Backups in SQL Server
Properties

Article ID: 295371 - Last Review: 01/16/2015 22:21:33 - Revision: 4.0

  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • kbnosurvey kbarchive kbprb KB295371
Feedback