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

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

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

Michael R. Hotek

All content on this site, except where noted, represents an original work of Michael R. Hotek and is protected by applicable copyright laws. The SQL Server FAQ is the sole work of Neil Pike. No page, portion of a page, or download may be used for commercial purposes in whole or in part without the express, written permission of the applicable author.