July 12, 2003 at 8:50 am
Hi!
Doing my backups by a multiserver jobs that run stored procedures. The one that is responsible for log backup is below. The backup command from this procedure is here:
SET @backup_command = 'BACKUP LOG ' + @db_name + ' TO DISK=''' + @log_backup_path + @db_name + '\' + @db_name + '.TLF''' + ' with NOINIT, NOFORMAT, NOUNLOAD'
When try to restore any database with log restore, I get the error:
"The log in this backup set begins at LSN ... , which is too late to apply to the database An earlier log backup that includes LSN ... , can be restored. Restore log is terminating abnormaly"
What is wrong with my stored procedure?
CREATE PROCEDURE "backup_dbs_log" AS
DECLARE @log_backup_path varchar(10)
DECLARE @backup_command varchar(200)
DECLARE @db_name varchar(30)
DECLARE @result int
DECLARE @dir_command varchar(30)
DECLARE @tm_txt char(8)
DECLARE @done smallint
SET @done=1
-- see if needed folders exist
EXEC @result = master..xp_cmdshell 'dir R:\LOG', no_output
IF (@result <> 0)
EXEC @result=master..xp_cmdshell 'mkdir R:\LOG', no_output
SET @log_backup_path = 'R:\LOG\'
-- the list of databases for log backup (based on table where I hold the shedule)
DECLARE backup_list CURSOR
LOCAL
SCROLL
STATIC
READ_ONLY
FOR
SELECT dbname
FROM BACKUP_DATABASES
WHERE (enabled = 1) and (category>=3) and (DATEDIFF(minute, last_log_backup, getdate())>=log_backup_interval)
ORDER BY priority
OPEN backup_list
FETCH NEXT FROM backup_list INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
-- check for directories
SET @dir_command = 'dir R:\LOG\' + @db_name
EXEC @result = master..xp_cmdshell @dir_command, no_output
IF (@result <> 0)
SET @dir_command = 'mkdir R:\LOG\' + @db_name
EXEC @result=master..xp_cmdshell @dir_command, no_output
-- My backup log command
SET @backup_command = 'BACKUP LOG ' + @db_name + ' TO DISK=''' + @log_backup_path + @db_name + '\' + @db_name + '.TLF''' + ' with NOINIT, NOFORMAT, NOUNLOAD'
EXEC(@backup_command)
IF @@ERROR = 0
UPDATE BACKUP_DATABASES
SET last_log_backup=GetDate()
WHERE dbname=@db_name
FETCH NEXT FROM backup_list INTO @db_name
END
CLOSE backup_list
DEALLOCATE backup_list
-- Insert into MAIl table the messages about failed backups
INSERT INTO MAIL
(recipient, subject, message, occur, author)
SELECT 'SQLAdmins@domain.com', 'The log backup of database ' + @@SERVERNAME + '..' + "dbname", 'The log backup of database ' + @@SERVERNAME + '..' + "dbname", GetDate(), 'Backup'
FROM BACKUP_DATABASES
WHERE (enabled = 1) and (category>=3) and (DATEDIFF(minute, last_log_backup, getdate())>=log_backup_interval)
GO
July 12, 2003 at 9:42 pm
Hi there
Some things to check:
a) do a full backup, then run the stored proc to do one and only one log backup, try a restore by applying the full then this log, work?
If so, try with 2 sets of log backup, work?
double check if your appending to the same log file, if so, your restore command will be a little more complex that working seperate files. Check this also.
Just a matter of working through with a simple restore and get more complex later on to catch the problem.
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
July 13, 2003 at 1:34 am
You have to restore full database backup with 'norecovery' before restoring any transaction log backups. Only transaction log backups that are created after the full database backup can be restored.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply