INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQLDMO

How can I script my SQL Server databases from Visual Basic? by tlbroadbent
Posted: 14 Mar 02 (Edited 8 May 03)

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

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

Please inform me of any problems encountered or recommendations you have for improvement.

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close