Go Premium for a chance to win a PS4. Enter to Win

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

SQL Server backup fails from vb6 application

I have a backup stored procedure gratiously provided by a member of this great establishment.  It runs fine from the server but fails occasionally when run from my vb6 app.  It doesn't timeout because it only runs about 20 seconds and the timeout is set for 60 seconds.

The error message simply says: 'Backup Failed'

Any ideas would be appreciated!!!

Jerry
0
JDL129
Asked:
JDL129
  • 2
1 Solution
 
tlovieCommented:
Does the VB6 application run with the same login credentials as when you run it from the server?  What does the proc do?
0
 
JDL129Author Commented:
Thanks for the response!!!

Attached is the stored procedure that I run from vb6.  The error I get is:Backup Failed
The application logs into the SQLServer database and calls the attached.

Thanks,

Jerry
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Backup]
@BackupPath nvarchar(200)
As
Declare @WeekDay tinyint
     Declare @Backup_File nvarchar(200)
     Declare @Desc nvarchar(255)                 
     Declare @Name nvarchar(128)                 

SET @WeekDay = DATEPART(weekday, CURRENT_TIMESTAMP)

IF @WeekDay = 7 -- change this value to meet the weekday you want to perform FULL BACKUP
BEGIN

     Set @Backup_File = @BackupPath + '\BarCodeSQL.bak'
     Set @Desc = 'Full Backup'
     Set @Name = 'Barcode FULL BACKUP'

     BACKUP DATABASE [BarCodeSQL] TO DISK = @Backup_File 
      WITH 
       DESCRIPTION = @Desc
     ,NAME = @Name
Return @@Error
END
ELSE
BEGIN

     Set @Backup_File = @BackupPath + '\BarCodeSQL.bak_' + DATENAME(weekday, CURRENT_TIMESTAMP) + '.bak'
     Set @Desc = 'Incremental Backup'
     Set @Name = 'Barcode ' + DATENAME(weekday, CURRENT_TIMESTAMP) + ' DIFF BACKUP'

     BACKUP DATABASE [BarCodeSQL] TO DISK = @Backup_File
     WITH DIFFERENTIAL,
      DESCRIPTION = @Desc, NOFORMAT, NOINIT
     ,NAME = @Name
     ,SKIP, NOREWIND, NOUNLOAD
Return @@Error
END

Open in new window

0
 
SQLLoverCommented:
run a profiler to monitor what happed , i think the problem is in passing @BackupPath parameter from vb  
0
 
JDL129Author Commented:
Thanks for the input!!!!!!!!!!
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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