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

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

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