PRB: Filegroup Log Restore May Fail with Error Message 4305

Article translations Article translations
Article ID: 295371 - View products that this article applies to.
This article was previously published under Q295371
Expand all | Collapse all

On This Page

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 msdb
go
create procedure requiredlogbck @dbname sysname
as 
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 desc
end
go
exec 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 msdb
go
CREATE   PROCEDURE sp_delete_database_backuphistory
  @db_nm nvarchar(256)
AS
BEGIN
  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)
  end

END
				

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: October 17, 2003 - Revision: 3.2
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbprb KB295371

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com