Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 433
  • Last Modified:

Log Backup Fails

I have job which takes the T-log backups for every 15 Mins,but it is failing due to the below error.
Message
Executed as user: XXXXXXXXXXX. backup log db_name  to disk='p:\TruncateLog.trn' with init [SQLSTATE 01000]
 (Message 0)  The backup data in 'p:\TruncateLog.trn' is incorrectly formatted. Backups cannot be appended,
but existing backup sets may still be usable. [SQLSTATE 42000] (Error 3266)  BACKUP LOG is terminating abnormally.
[SQLSTATE 42000] (Error 3013).  The step failed.

I am running the below sql for the job,am i missing anything in the sql,if yes can someone correct it.

declare @sql varchar(500),@db varchar(60)
select @db=min(name) from master..sysdatabases where databasepropertyex(name,'Recovery')='FULL'
while @db is not null
begin
      set @sql='backup log '+@db+'  to disk=''p:\TruncateLog.trn'' with init'
      print @sql
      exec (@sql)
      select @db=min(name) from master..sysdatabases where databasepropertyex(name,'Recovery')='FULL' and name>@db
end

I am running on Microsoft SQL Server  2000 - 8.00.2040 (Intel X86) - SP4


Thanks,
K
0
Kodukulla
Asked:
Kodukulla
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
http://support.microsoft.com/kb/290787

you will need to write to a new file ...
0
 
anushahannaCommented:
you want to make the INIT to NOINIT since you are dumping all the backups into the same file.
http://msdn.microsoft.com/en-us/library/ms186865.aspx

"Indicates that the backup set is appended to the specified media set, preserving existing backup sets. If a media password is defined for the media set, the password must be supplied. NOINIT is the default."
0
 
KodukullaAuthor Commented:
Hi,anushahanna

If i want to take the backups for all the databases individually ( Different trn dump files ) then.I still doubt something wrong with this statement.

set @sql='backup log '+@db+'  to disk=''p:\TruncateLog.trn'' with init'

How can i give the database name before the ( ""disk=''p:\TruncateLog.trn'' with init'"" ) P:\MyDB_Trncatelog.trn .

Please help.
0
 
anushahannaCommented:

OK- please try any of these variations:

set @sql='backup log '+@db+'  to disk=''p:\'+ @db+'_'+replace(replace(cast(getdate() as varchar(100)),' ',''),':','')+'_TruncateLog.trn'' with init'
or
set @sql='backup log '+@db+'  to disk=''p:\'+ @db+'_TruncateLog.trn'' with init'
 
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now