Replication Script

Set up is as follows:

1. 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
   

2. The connection string to the server where you created the Servers table should be modified.
3. The location for the directory structure should be modified to reflect a valid location.
4. 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):

ReplicationServerNameCreate PublicationName (1 for each publication per database)

ReplicationServerNameDelete PublicationName (1 for each publication per database)

ReplicationServerNameCreate All Publications (1 file per server to recreate all publications)

ReplicationServerNameDrop All Publications (1 file per server to drop all publications)

ReplicationServerName2Create PublicationName (1 for each publication per database)

ReplicationServerName2Delete PublicationName (1 for each publication per database)

ReplicationServerName2Create All Publications (1 file per server to recreate all publications)

ReplicationServerName2Drop All Publications (1 file per server to drop all publications)

ReplicationMMDDYYYY_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:dbascriptsreplication" & rs.Fields(0).Value
        
     strToday = Month(Now()) & Day(Now()) & Year(Now()) 
     strPath = "c:dbascriptsreplication" & 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