Microsoft KB Archive/295371

= PRB: Filegroup Log Restore May Fail with Error Message 4305 =

Article ID: 295371

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q295371



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 &quot;Workaround&quot; 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



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.