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

Neil Pike

May 25, 1999

1 Min Read
ITPro Today logo in a gray background | ITPro Today

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

Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.

You May Also Like