Solved

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

Posted on 2010-08-28
9
787 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

617 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