PRB: Filegroup Log Restore May Fail with Error Message 4305 (295371)

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