SQLDMO

How can I script my SQL Server databases from Visual Basic?

You can use SQLDMO (SQL Distributed Management Objects) in VB to script a SQL Server database. There is a VB example included on the SQL 2000 install CD. If you installed the SQL Server developer tools, you should be able to find the sample code in...

C:\Program Files\Microsoft SQL Server\80\Tools\Devtools\Samples\Sqldmo\Vb\SQLScripts

Modify the path as needed if the SQL installation is not in Program Files on C:

I have written a VB subroutine using SQLDMO for scripting all databases on all servers. This code has been tested in Access 2000 VBA. It should run in VB6 but hasn't been tested, yet.

The routine will script SQL 7 and 2000 databases. It will may work for SQL 6.5 but no guarantees.

You'll need to add a reference for the "Microsoft SQLDMO Object Library" to your VB or Access project.

-------------------------------------------------------

Public Sub ScriptAllSQLServerDatabases()

  'Dim objects and variables
  Dim oSQL As New SQLDMO.Application
  Dim oServerNames As NameList
  Dim oObjNames As NameList
  Dim oSS As SQLDMO.SQLServer
  Dim oDb As SQLDMO.Database
  Dim oT As SQLDMO.Transfer
  Dim i As Integer, j As Integer, k As Integer
  Dim strServer As String, strDBName As String
  Dim sMsg As String
  
  On Error GoTo Scripting_Error
  
  Debug.Print "Scripting started - " & _
    CStr(Now()) & vbCrLf
  
  'Create list of servers
  Set oServerNames = oSQL.ListAvailableSQLServers()
  
  'Iterate through server name list
  For k = 1 To oServerNames.Count
   
   'Get server name
   strServer = oServerNames(k)
   DoEvents
   
   If strServer <> "." Then
   
    'Create Server object
    Set oSS = New SQLDMO.SQLServer
    
    'Set LoginSecure if using trusted connection
    'oSS.LoginSecure = True
    
    'Connect to the server - integrated security
    'oSS.Connect strServer
    
    'Connect to the server - SQL Security
    oSS.Connect strServer, "sa", "password"
    DoEvents
    
    'Iterate through databases on current server
    For i = 1 To oSS.Databases.Count

      'Create database object
      Set oDb = oSS.Databases.Item(i)
      
      'Get DB name
      strDBName = oDb.Name
      DoEvents
      
      'Bypass system dbs
      If strDBName <> "tempdb" And _
        strDBName <> "master" And _
        strDBName <> "msdb" And _
        strDBName <> "model" Then
      
        'Create transfer object
        Set oT = New SQLDMO.Transfer
    
        'Set transfer options
        oT.CopyAllObjects = True
        oT.IncludeUsers = True
        oT.CopyData = SQLDMOCopyData_False
        
        'Create script in designated folder
        oDb.ScriptTransfer oT, _
          SQLDMOXfrFile_SummaryFiles, _
          "C:\data\scripts\" & strServer & _
          "\" & strDBName
        
        Debug.Print strDBName & _
         " database scripting completed - " & _
         CStr(Now()) & vbCrLf
        
        Set oT = Nothing
        Set oDb = Nothing
        DoEvents
        
      End If
    Next i
    
    Set oSS = Nothing
    
   End If
   
  Next k
  
  Debug.Print "Scripting completed - " & CStr(Now())
  
Exit Sub

-------------------------------------------------------