Replication Script
October 11, 2002
This VBscript scripts publications on all SQL Servers within your network. It's useful for organizations the have implemented large amounts of replication.
Set up is as follows:
- A "Servers" table containing all the SQL Servers in the organization should be created.
Servers
ServerName varcha(XX)
ScriptDDL. Tinyint (values 0 or 1) 1 = Script, 0 = Do not Script
- The connection string to the server where you created the Servers table should be modified.
- The location for the directory structure should be modified to reflect a valid location.
- Copy and paste the script into a DTS package's ActiveX task and schedule it.
The following directory structure and scripts will be created (where servername is read from the servers table and PublicationName is the name of the Publication):
Replication\ServerName\Create PublicationName (1 for each publication per database)
Replication\ServerName\Delete PublicationName (1 for each publication per database)
Replication\ServerName\Create All Publications (1 file per server to recreate all publications)
Replication\ServerName\Drop All Publications (1 file per server to drop all publications)
Replication\ServerName2\Create PublicationName (1 for each publication per database)
Replication\ServerName2\Delete PublicationName (1 for each publication per database)
Replication\ServerName2\Create All Publications (1 file per server to recreate all publications)
Replication\ServerName2\Drop All Publications (1 file per server to drop all publications)
Replication\MMDDYYYY_Log.txt (log file stating what was created)
Author: Janet Keith
'**********************************************************************
' Visual Basic ActiveX Script
'**********************************************************************
Option Explicit
'Cursor Type Enum
CONST adOpenForwardOnly = 0
CONST adOpenKeyset = 1
CONST adOpenDynamic = 2
CONST adOpenStatic = 3
'Lock Type Enum
CONST adLockReadOnly = 1
CONST adLockPessimistic = 2
CONST adLockOptimistic = 3
CONST adLockBatchOptimistic = 4
CONST forappending = 8
Dim oServer
Dim oDatabase
Dim TransPub
Dim rs
Dim SQL
Dim fld
Dim strFolder
Dim oFSTR
Dim tfLog
Dim strToday
Dim oFSO
Dim strPath
'On Error Resume Next
'Create File System object
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oServer = CreateObject("SQLDmo.SqlServer")
oServer.LoginSecure = True
'Create a table named servers with 2 columns. ServerName and ScriptDDL.
SQL = "select servername from servers where scriptddl = 1"
Set rs = CreateObject("ADODB.Recordset")
rs.Open sql, "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=SQLServers;Data Source=MyServer", adOpenForwardOnly,
adLockReadOnly
Do While (Not rs.EOF)
oServer.Connect rs.Fields(0).Value
strFolder = "c:\dba\scripts\replication\" & rs.Fields(0).Value
strToday = Month(Now()) & Day(Now()) & Year(Now())
strPath = "c:\dba\scripts\replication\" & strToday & "_log.txt"
If Not oFSO.FileExists(strPath) Then
Set tfLog = oFSO.CreateTextFile(strPath, True)
tfLog.Close
End If
Set tfLog = oFSO.OpenTextFile(strPath, ForAppending, True)
If Not oFSO.FolderExists(strFolder) Then
oFSO.CreateFolder (strFolder)
Else
If oFSO.FileExists(strFolder & "\Create All Publications.sql") Then
oFSO.DeleteFile (strFolder & "\Create All Publications.sql")
End If
If oFSO.FileExists(strFolder & "\Drop All Publications.sql") Then
oFSO.DeleteFile (strFolder & "\Drop All Publications.sql")
End If
End If
For Each oDatabase In oServer.Databases
strToday = FormatDateTime(Now(),vbShortDate) & " " & FormatDateTime(Now(),vbLongTime)
tfLog.WriteLine (strToday & " " & strFolder)
'SQLDMOScript_AppendToFile=8192
'SQLDMOScript_ObjectPermissions=2
'SQLDMOScript_ToFileOnly=64
'SQLDMOScript_PrimaryObject=4
'SQLDMORepScript_Creation = 16384
'SQLDMORepScript_Deletion = 32768
If Not oDatabase.SystemObject Then
If Not oDatabase.DBOption.ReadOnly = True Then
For Each TransPub In oServer.Replication.ReplicationDatabases(oDatabase.Name).TransPublications
strToday = FormatDateTime(Now(),vbShortDate) & " " & FormatDateTime(Now(),vbLongTime)
tfLog.WriteLine (strToday & " " & strFolder & "\Create " & TransPub.Name & ".sql")
TransPub.Script 16384, strFolder & "\Create " & TransPub.Name & ".sql"
TransPub.Script 16384 + 8192, strFolder & "\Create All Publications.sql"
TransPub.Script 32768, strFolder & "\Delete " & TransPub.Name & ".sql"
TransPub.Script 32768 + 8192, strFolder & "\Drop All Publications.sql"
Next
End If
End If
Next
oServer.DisConnect
rs.MoveNext
tfLog.Close
Loop
'Record Errors
If Err.Number > 0 Then
strToday = FormatDateTime(Now(),vbShortDate) & " " & FormatDateTime(Now(),vbLongTime)
'MsgBox(strToday & " " & Err.Description & vbCrLf & Err.Number)
tfLog.WriteLine (strToday & " " & Err.Description & vbCrLf & Err.Number)
End If
'Clean up
rs.Close
Set RS = Nothing
Set oServer = Nothing
Set oFSO = Nothing
Back to Database Journal Home
|
