September 29, 2006 at 8:38 am
We are running SQL Server 2K and using LiteSpeed for backing up our databases. Every day or two it seems that the backups fail for one or two databases. Never the same databases. The error we get is "Msg 62309, Level 19, State 1, Line 1". When I generate a script and do a LiteSpeed backup from Query Analyzer the backups are generated with not problems.
We are backing up databases on many other servers on different boxes using LiteSpeed (same model of box, same OS, same SQL Server version and setup) and never have this problem on any other server.
Does anyone know what is going on, here?
October 2, 2006 at 8:00 am
This was removed by the editor as SPAM
November 12, 2007 at 12:43 pm
This is due to you trying to take a trans backup with no fullbackup.
Kindest Regards,
David
** Obstacles are those frightening things that appear when we take our eyes off the goal. **
November 13, 2007 at 10:46 am
Any detailed error message? I checked Quest KB, it says...
-------------------------------------------------
Backup error message: "Server: Msg 62309, Level 19, State 1, Line 1"
Problem DescriptionError:
"BACKUP LOG is terminating abnormally when initiating backup of Transaction Logs.
The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE."
CauseThis message may appear on backups run in Query Analyzer, or Maintenance Plans.
ResolutionThe SQL Server requirements for Transaction Log Backups is that the database properties be in "Full" Recovery mode.
1. Use the SQL Server command ALTER DATABASE or the Properties on the database in Enterprise Manager to change the Recovery Mode to "Full".
2. Restart the transaction log backup process.
November 13, 2007 at 10:49 am
People, my original post is more than 1 year old. You do not need to respond, anymore.
Thank you.
December 3, 2008 at 3:12 pm
Looking to expand on this issue rater thean creating a new post. Yes, the issue was that a Full backup neded to be performed before a log can be backed up.
Question: How does SQL Server know whether a full backup has been taken?
My end goal is to add that logic and incorporate it into a log backup script which will first check to see whetehr a full backup has been taken yet. That will prevent getting the error to begin with.
December 7, 2008 at 12:08 am
[font="Verdana"]
--CODE PROVIDED AS IS.
SELECT BS.SERVER_NAME,
BS.DATABASE_NAME,
CASE BACKUPTYPE =
WHEN BS.TYPE = 'D' THEN 'DATABASE'
WHEN BS.TYPE = 'L' THEN 'TRANSACTION LOG'
WHEN BS.TYPE = 'I' THEN 'DATABASE DIFFERENTIAL'
WHEN BS.TYPE = 'F' THEN 'FILE OR FILEGROUP'
ELSE 'UNKNOWN'
END,
BS.BACKUP_START_DATE,
BS.BACKUP_FINISH_DATE
FROM MSDB..BACKUPSET BS
WHERE BACKUP_FINISH_DATE > DATEADD (DAY, -0.5, GETDATE())
Reference:
http://msdn.microsoft.com/en-us/library/aa260602(SQL.80).aspx
Regards,[/font]
December 7, 2008 at 8:58 am
Thanks but that will not work. That table is just showing backup history. If the recovery model were changed from full to simple and then back again to full - a full backup would need to be taken before a log backup could be taken. The entry in the msdb table would still be there from the prior backup which is no longer applicable. Here is the solution for a database named test:
SET NOCOUNT ON
DBCC TRACEON(3604)
DECLARE @Table TABLE (
ParentObject nvarchar(100),
[Object] nvarchar(100),
Field nvarchar(100),
[VALUE] nvarchar(100)
)
DECLARE @cmd varchar(100)
SET @cmd = 'DBCC DBINFO (''test'') WITH TABLERESULTS, NO_INFOMSGS'
INSERT @Table
EXEC (@cmd)
IF (SELECT [Value] FROM @Table
WHERE [Object] = 'dbi_dbbackupLSN'
AND Field = 'm_fSeqNo') = 0
BEGIN
RETURN
END
ELSE BEGIN
--T-Log backup script
END
December 7, 2008 at 10:37 am
[font="Verdana"]Hmmm...interesting. Did not think about the scenario you detailed. But, it is always good to know these sorta things. Thank you :)[/font]
May 11, 2009 at 2:06 am
See http://weblogs.sqlteam.com/tarad/archive/2009/04/07/Backup-SQL-Server-DatabasesAgain.aspx
It has the logic inside that you're looking for. And a lot more.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply