Skip navigation

How can I programmatically kick off a SQL dump and get the stats on how far through it is back dynamically?

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

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
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

TAGS: SQL Server
Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.