VB6 SQL-DMO how backup to remote hard drive

[MarkInCA]

Hello, all!
I'm successfully using SQL DMO to create backups of my SQL database on my local machine. I can connect to our local network and manipulate server databases. Is there a way to create a .bak on my local disk of the server database?

my abbreviated code:

Dim poBackup As SQLDMO.Backup

'Create backup
Set mvSQLServer = New SQLDMO.SQLServer
mvSQLServer.LoginTimeout = 15
mvSQLServer.Connect dbServerName, uid, pwd

Set poBackup = New SQLDMO.Backup

poBackup.Database = DBName

PathToLocal = "C:\somelocaldir

BackupFileName = PathToLocal & "\" & DBName & ".bak"

poBackup.Files = BackupFileName


Again, this works great when I'm backing up a local database. I think I need something like:
PathToLocal = "\\myPCname..."
when I'm backing up from "\\ourNetworkServer\..."

What am I missing?

Thanks

 

[BobRodes]

>I think I need something like: [etc]

Did you try that?

 

[MarkInCA]

Hello, all!

If anyone needs this for future reference, here is the solution I created:

Set reference to "Active DS Type Library"


Private sub BackupToShare

Dim psCompId As String
Dim psBackupPath As String
Dim psDirPath As String
Dim psInfo As New ADSystemInfo
Dim pb As Boolean
Dim psDomainID As String

Dim psBackupFileName As String
Dim poBackup As sqldmo.Backup
Dim psMsg As String
Dim poLocal As sqldmo.BackupDevice


On Error GoTo SQLErrHand

'Get local computer name
psCompId = ComputerID
psDomainID = psInfo.DomainDNSName


'Check to see if path exists
psBackupPath = "somepath\DatabaseBackup"

psDirPath = psBackupPath & "\*.bak"

'Create folder if needed
If Len(Dir(psBackupPath, vbDirectory)) = 0 Then
MkDir (psBackupPath)
End If

'Allow the folder to be shared by all
pb = SetSharePermissions(psBackupPath, psDomainID, psCompId, False)

'Create the name of the backup file
psBackupFileName = "someSQLdatabaseName" & Format(Now, "yyyymmddhhnnss") & ".bak"

'Create the SQL command
psMsg = "BACKUP DATABASE " & "someSQLdatabaseName" & " TO DISK = '\\" & psCompId & "\DatabaseBackup\" & psBackupFileName & "' With INIT"

'dbdata is an active connection to the database
dbData.Execute psMsg

'Close the share
pb = SetSharePermissions(psBackupPath, psDomainID, psCompId, True)


end sub


Public Function SetSharePermissions(ByVal asPath As String, ByVal asDomain As String, _
ByVal asCompID As String, ByVal abDelete As Boolean) As Boolean

Dim fserv As IADsContainer
Dim share As IADsFileShare
Dim shareNew As IADsFileShare
Dim v As Variant
Dim psCompId As String
Dim psDomainID As String
Dim psObj As String
Dim psInfo As New ADSystemInfo

On Error GoTo SetShareErrHand

SetSharePermissions = False



' Replace DOMAIN & SERVER with the appropriate domain and server names
psCompId = ComputerID
psDomainID = psInfo.DomainDNSName
psObj = "WinNT://" & asDomain & "/" & asCompID & "/lanmanserver"

Set fserv = GetObject(psObj)

' Enumerate existing shares
For Each share In fserv
v = share.Class
v = share.ADsPath
v = share.HostComputer
v = share.Path
Next share

If abDelete = False Then

' Create share in fileservice container
Set shareNew = fserv.Create("fileshare", "DatabaseBackup")
shareNew.Path = asPath
shareNew.SetInfo ' Commit new share

Else

' Delete share
fserv.Delete "fileshare", "DatabaseBackup"

End If

SetSharePermissions = True


Exit Function

SetShareErrHand:

Debug.Print Err.NUMBER & "-" & Err.DESCRIPTION

SetSharePermissions = False


End Function

 

[MichaelRed]

Error ==> user defined type not defined -->

Dim psInfo As New ADSystemInfo

MichaelRed

 

[MichaelRed]

OOPs ==> MyBad error is on -->

Dim poBackup As sqldmo.Backup



MichaelRed

 

[BobRodes]

<Dim psInfo As New ADSystemInfo

See faq222-6008 for reasons not to use this syntax in VB6.

HTH

Bob