Backup Fails - sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). 1

[smedvid]

I get a sporadic error for certain SQL Server Backups. I have a Maintenance Plan for Backup with Verify enabled. My target directory for the backup is a network directory share. It works fine 85% of the time. Two or Three times a month, I get a failure message "sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029)". Although the Backup file is created, and appears to be the correct size.

I have thought about doing a local backup to the C:\ Drive; then devising some method to Delete and Move the backups to the network drive. Anyone doing this now with VB Code (File-Scripting Obejct) or with RoboCopy? This would solve my problem, I believe. tia,

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[SQLBill]

Find the maintenance job in Enterprise Manager>Manage>SQL Server Agent>Jobs.

Right click on it and select View Job History. Check the box for Viewing job steps. Find the one that failed. Just because a job fails, it doesn't mean the whole job failed. One step within the job may have failed and that will give a failure for the job.

-SQLBill

Posting advice: FAQ481-4875

 

[smedvid]

Only 1 step - and error is SQLMaint.exe error listed. I have also tried to create a new MP and it solves the issue intitially, then over time... the problem re-appears sporadically. I just found a forum, that said to set recovery from simple to full... I am trying that now.. thanks!

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[SQLBill]

OOOO. you have your database set in SIMPLE mode? That explains everything. You can NOT do a transaction log backup when your database recoverey mode is set for SIMPLE. (But this should be causing a failure ALL of the time).

If that's not the case, is anything else accessing the backup file? Do you or the sysadmin/network admin back it up to tape? If so, the backup to tape could be occuring during the VERIFY part of the maintenance plan.

-SQLBill

Posting advice: FAQ481-4875

 

[smedvid]

Possble, but I have manipulated the times of the Backup a couple of times, plus the Backup is small 300MB and take a couple of minutes only. Plus the job fails when I run it manaully also... so time may not be an issue... tia,

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[Catadmin]

Steve,

I get the same error sometimes due to simple Network issues (loss of connectivity) and usually the backup is corrupt. When you get this error, try to restore the backup to a Dev or Test server to test the backup. 9 times out of 10 it'll fail.

You asked for VBScript? Here's the one I use (because SQL doesn't allow UNC paths).

'*********************************************
' Visual Basic ActiveX Script
'*********************************************

Function Main()


Dim MyBackup

Set MyBackup = CreateObject("Scripting.FileSystemObject")

MyBackup.CopyFile "\\MyProdServerName\SQL_BACKUPS\MyDB\CopyForQC-Dev\<dbName>_db_20*.bak", "\\MyDevServerName\e$\ProdCopyBackups\"

Set MyBackup = Nothing

Main = DTSTaskExecResult_Success
End Function

You'll want to change MyBackup to a user friendly object name and insert the actual servernames where it says "MyProdServerName" and "MyDevServerName". Hope it helps! Oh, and make sure you create the job using ActiveX script and parse it before you save the job.



Catadmin - MCDBA, MCSA
"The only stupid question is the one you *didn't* ask.

 

[smedvid]

Thanks! I will use the script next week and convert to copy from the server to the network. Now, the (2) backups that had this issue yesterday, were both fine today - and I changed nothing.

Steve Medvid
&quot;IT Consultant & Web Master&quot;

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[Catadmin]

I'm glad I could help.

Oh, one thing I forgot. You'll need a job that regularly clears the backup files off the server you copied to. If you don't, your HD will fill up fast.

Here's what I did:

create table #Backup (Filename varchar(250))

Declare @Filename varchar(250), @RemoveFile varchar(100),
@Directory varchar(250), @MyDate datetime, @LongFileName varchar(200)

Set @Directory = '"dir e:\SQL_DB_BAK\apacs_dev\Restore"'

Insert into #Backup
exec master..xp_cmdshell @Directory

/*Select * from #Backup
where filename like '%apacs_db_200%'

Drop table #Backup*/

declare BakDel CURSOR for select * from #backup
where filename like '%apacs_db_200%'

Open BakDel
fetch next from BakDel into @LongFileName
while @@fetch_Status = 0
Begin

Set @FileName = '"e:\SQL_DB_BAK\apacs_dev\Restore\' + (Select substring(@longFilename, 40, 50) ) + '"'
Set @MyDate = (Select Cast(Substring(@longFileName, 1, 10) as datetime) )

--Select @FileName, @MyDate, DateDiff(dd,@myDate,GetDate())

If DateDiff(dd,@MyDate,GetDate()) > 1
Begin
Set @RemoveFile = 'Del ' + @FileName
exec master..xp_cmdShell @RemoveFile
END

Fetch Next from BakDel into @LongFileName

END
Close BakDel
Deallocate BakDel

Drop table #backup

Hope everything works out for you.



Catadmin - MCDBA, MCSA
"The only stupid question is the one that *wasn't* asked.