Solved

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

Posted on 2010-08-28
9
771 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:JDL129
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 1

Expert Comment

by:mgoodspeed
ID: 33552340
All that for 250 points?
Have a routine written as an example, but would want 500 pts min
0
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 500 total points
ID: 33552425
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
 
LVL 14

Expert Comment

by:VBClassicGuy
ID: 33554209
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:JDL129
ID: 33554989
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
 

Author Comment

by:JDL129
ID: 33554994
VBCLASSICGUY!!  Thanks for the post but Mr. Nuvo's was easier to follow.
0
 
LVL 3

Expert Comment

by:deepusreedhar
ID: 33575441
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
 
LVL 3

Expert Comment

by:deepusreedhar
ID: 33575501
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
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 33601950
to create stored procedure
you miss "AS" keyword


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

0
 

Author Closing Comment

by:JDL129
ID: 33604467
I really appreciate all of the responses but Mr. Nuvo was clear short and it worked great!!!!!

Thanks Joe!!

Jerry
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question