Solved

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

Posted on 2010-08-28
9
760 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
 

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now