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

x
?
Solved

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

Posted on 2010-08-28
9
Medium Priority
?
808 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 2000 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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:Deepu Sreedhar
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:Deepu Sreedhar
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

885 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