A. You can do it with DMO. Here is some sample code supplied bt Gert Drapers.
Option Explicit
Dim mSQLServer As SQLDMO.SQLServer
Dim WithEvents mBackupEvents As SQLDMO.Backup
Private Sub cmdBackupWithEvents_Click()
Dim mDatabase As SQLDMO.Database
Dim mBackup As New SQLDMO.Backup
Set mBackupEvents = mBackup
For Each mDatabase In mSQLServer.Databases
If (mDatabase.Name <> "model" And mDatabase.Name <> "tempdb") Then
mBackup.Database = mDatabase.Name
mBackup.Files = "c:\dump\" & mDatabase.Name & Format(Date,
"yyyy-mm-dd") & ".bak"
mBackup.SQLBackup mSQLServer
End If
Next
Set mBackupEvents = Nothing
Set mBackup = Nothing
End Sub
Private Sub cmdConnect_Click()
Set mSQLServer = New SQLDMO.SQLServer
Call mSQLServer.Connect("(local)", "sa", "")
Msg "Connected " & Now()
End Sub
Private Sub cmdDisconnect_Click()
If (Not mSQLServer Is Nothing) Then
mSQLServer.DisConnect
Set mSQLServer = Nothing
End If
Msg "Disconnected " & Now()
End Sub
Private Sub mBackupEvents_Complete(ByVal Message As String)
txtOut = txtOut & Message & vbCrLf
End Sub
Private Sub mBackupEvents_NextMedia(ByVal Message As String)
txtOut = txtOut & Message & vbCrLf
End Sub
Private Sub mBackupEvents_PercentComplete(ByVal Message As String, ByVal
Percent As Long)
txtOut = txtOut & Message & "Percent " & Percent & vbCrLf
End Sub