I need to programmatically back up a database in SQL Server 2005 from vb6

EXPERTS!!!!!

I need to programmatically back up a database in SQL Server 2005 from vb6.  I would like to make a full backup every 7th day and differential backups each day.  After the 7th day fullbackup I would like to erase the previous differentials and start over.

Thanks guys,

Jerry

JDL129Asked:
Who is Participating?
 
JoeNuvoConnect With a Mentor Commented:
You may try below query to perform your required backup.
It will perform rotation without delete old file.



      Declare @WeekDay tinyint
      Declare @Backup_File nvarchar(200)
      Declare @Desc nvarchar(255)                  
      Declare @Name nvarchar(128)                  

SET @WeekDay = DATEPART(weekday, CURRENT_TIMESTAMP)

IF @WeekDay = 1 -- change this value to meet the weekday you want to perform FULL BACKUP
BEGIN
     Set @Backup_File = 'Drive:\Path\filename.bak'
      Set @Desc = 'anything u like, for ex. Full Backup'
      Set @Name = 'Database FULL BACKUP'

      BACKUP DATABASE [database name] TO DISK = @Backup_File
      WITH
       DESCRIPTION = @Desc
      ,NAME = @Name
END
ELSE
BEGIN
     Set @Backup_File = 'Drive:\Path\filename_' + DATENAME(weekday, CURRENT_TIMESTAMP) + '.bak'
      Set @Desc = 'anything u like, for ex. Full Backup'
      Set @Name = 'Database ' + DATENAME(weekday, CURRENT_TIMESTAMP) + ' DIFF BACKUP'

      BACKUP DATABASE [database name] TO DISK = @Backup_File
      WITH DIFFERENTIAL,
       DESCRIPTION = @Desc, NOFORMAT, NOINIT
      ,NAME = @Name
      ,SKIP, NOREWIND, NOUNLOAD
END
0
 
mgoodspeedCommented:
All that for 250 points?
Have a routine written as an example, but would want 500 pts min
0
 
VBClassicGuyCommented:
Here's something to get you started. It gets connection string info from an INI file. A few routines will look unfamilar, but you can tell by looking what they do. It makes a full backup after deleting the old one, but you can modify to suit your needs:

Sub BackupSQLServer()
   On Local Error GoTo Hell
   
   If SQLServer% Then
      Rem *** Build script ***
      script$ = App.path & "\SQL_Backup_Script.txt"
      SQLbak$ = App.path & "\SQL_Server.bak"
      UserID$ = GetIni$(1, "Database", "UserID")
      UserPW$ = GetIni$(1, "Database", "Password")
      DBName$ = GetIni$(1, "Database", "InitCat")
      DBPath$ = GetIni$(1, "Database", "Source")
      h% = FreeFile
      Call CRFO(script$, "o", h%, 0) 'open file for Output
      Print #h%, "use " & DBName$
      Print #h%, "GO"
      Print #h%, "BACKUP DATABASE [" & DBName$ & "] TO DISK = N'" & SQLbak$ & "'"
      Print #h%, "GO"
      Close #h%
      Rem *** Delete any existing backup ***
      If Len(Dir$(SQLbak$)) Then
         Kill SQLbak$
      End If
      Rem *** Run script ***
      txt$ = "sqlcmd -S " & DBPath$ & " -U " & UserID$ & " -P " & UserPW$ & " -i " & script$
      Call ShellAndWait(txt$)
      If Len(Dir$(script$)) Then
         Kill script$
      End If
      If Len(Dir$(SQLbak$)) = 0 Then
         i% = TimedMsgBox%(3, Lang$(105), Lang$(2033), "", Lang$(100), "", "")
         'XL "ERROR" & "SQL Backup Failed" & "OK"
      End If
   End If
   
   Exit Sub
Hell:
   If Len(Dir$(script$)) Then
      Kill script$
   End If
   i% = TimedMsgBox%(3, Lang$(105), Lang$(1786) & " in BackupSQLServer", Lang$(1787), Lang$(100), "", "")
   'XL "ERROR" & "Internal error" & "Processing will resume" & "OK"
End Sub
This is in a separate .bas module:

Option Explicit
Private Const SYNCHRONIZE = &H100000
Private Const WAIT_TIMEOUT = &H102
Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long

Sub ShellAndWait(ByVal cmd As String)
   Dim lPid As Long
   Dim lHnd As Long
   lPid = Shell(cmd, vbNormalFocus)
   If lPid <> 0 Then
      lHnd = OpenProcess(SYNCHRONIZE, 0, lPid)
      If lHnd <> 0 Then
         Do
            DoEvents 'keep application responsive
            Sleep 50 'keep CPU usage from ramping to 100%
         Loop Until WaitForSingleObject(lHnd, 0) <> WAIT_TIMEOUT
         CloseHandle (lHnd)
      End If
   End If
   
End Sub
 
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
JDL129Author Commented:
Mr. Nuvo!!!!!!!!!!!!
You hit the nail on the head!!!
Below is a copy of the sp that I created using your code.  I am getting an error that reads:Msg 156, Level 15, State 1, Procedure sp_Backup, Line 4 Incorrect syntax near the keyword 'Declare'.

CREATE PROCEDURE [dbo].[sp_Backup]
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 = 'C:\Backup\BarCodeSQL.bak'
     Set @Desc = 'Full Backup'
     Set @Name = 'Database FULL BACKUP'

     BACKUP DATABASE [BarCodeSQL] TO DISK = @Backup_File
      WITH
       DESCRIPTION = @Desc
     ,NAME = @Name

END
ELSE
BEGIN

     Set @Backup_File = 'C:\Backup\BarCodeSQL.bak_' + DATENAME(weekday, CURRENT_TIMESTAMP) + '.bak'
     Set @Desc = 'anything u like, for ex. Full Backup'
     Set @Name = 'Database ' + DATENAME(weekday, CURRENT_TIMESTAMP) + ' DIFF BACKUP'

     BACKUP DATABASE [BarCodeSQL] TO DISK = @Backup_File
     WITH DIFFERENTIAL,
      DESCRIPTION = @Desc, NOFORMAT, NOINIT
     ,NAME = @Name
     ,SKIP, NOREWIND, NOUNLOAD
END
0
 
JDL129Author Commented:
VBCLASSICGUY!!  Thanks for the post but Mr. Nuvo's was easier to follow.
0
 
Deepu SreedharBI Software EngineerCommented:
What about calling the below given stored procedure from VB code?
CREATE    PROCEDURE dbo.spBackupYourDb  
 @InputDBName NVARCHAR(240),  
 @BackupDirectory NVARCHAR(240),  
 @User INT  
WITH RECOMPILE  
as  
set nocount on  

DECLARE @DBName NVARCHAR(50), @Device NVARCHAR(100), @Name NVARCHAR(100), @BackupName NVARCHAR(100)  
Declare @BackupStatement varchar(2048)  
declare @DevName sysname  
declare @TheDB sysname  
Declare @dd nvarchar(4)  
Declare @mm nvarchar(4)  
Declare @yy nvarchar(4)  
Declare @hh nvarchar(4)  
Declare @nn nvarchar(4)  
select @dd = CAST(DAY(GETDATE()) AS NVARCHAR(4))  
select @mm = CAST(MONTH(GETDATE()) AS NVARCHAR(4))  
select @yy = CAST(YEAR(GETDATE()) AS NVARCHAR(8))  
select @hh = CAST(DATEPART(hour, GETDATE())  AS NVARCHAR(8))  
select @nn = CAST(DATEPART(minute, GETDATE())  AS NVARCHAR(8))  
select @DBName = @InputDBName  
select @TheDB = name from master.dbo.sysdatabases where name = @InputDBName  

if @TheDB is not null  
BEGIN  
 set @BackupName =  @DBName + '_' +  @dd + '_' + @mm  + '_' + @yy + '_' + @hh +'_' + @nn  
 SET @Device = @BackupDirectory + '\' + @BackupName + '.DAT'  
   
 SET @Name = @DBName + 'FullBackup'  
 select @DevName = name from master.dbo.sysdevices where upper(phyname) = upper(@Device)  

 if @DevName is null  
 BEGIN  
  EXEC sp_addumpdevice 'disk', @BackupName, @Device  
 END  
 select @DevName = name from master.dbo.sysdevices where upper(phyname) = upper(@Device)  
 if @DevName is not null  
 BEGIN  
  select @BackupStatement = 'BACKUP DATABASE '+@DBName+' TO '+@BackupName  
  exec(@BackupStatement)  
  IF @@ERROR <> 0  
  BEGIN  
   select 102  
   return(102)  
  END  
  ELSE  
  BEGIN  
   RESTORE VERIFYONLY FROM DISK = @Device  WITH FILE = 1  
   IF @@ERROR <> 0  
   BEGIN  
    select 104  
    return(104)  
   END  
   ELSE  
   BEGIN  
    select 100  
    return(100)  
   END  
  END  
 END  
 ELSE  
 BEGIN  
  select 103  
  return(103)  
 END  
END  
ELSE  
BEGIN  
 select 101  
 return(101)  
END  
IF @@ERROR <> 0  
BEGIN  
 select 105  
 return(105)  
END

Open in new window

0
 
Deepu SreedharBI Software EngineerCommented:
And also in the VB code you can display the messages according to the return value from the SP as follows
Case 100: Backup Completed Successfully
Case 101: Can't find Database
Case 102: Backup Failure
Case 103: Device Creation Failure
Case 104: Restore Verify Failure
Case 105: Unknown Error
0
 
JoeNuvoCommented:
to create stored procedure
you miss "AS" keyword


CREATE PROCEDURE [dbo].[sp_Backup]
AS
Declare @WeekDay tinyint
...
...

0
 
JDL129Author Commented:
I really appreciate all of the responses but Mr. Nuvo was clear short and it worked great!!!!!

Thanks Joe!!

Jerry
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.

All Courses

From novice to tech pro — start learning today.