automated replication scripting

We create replication scripts on a regular basis, saving them to a warm standby server as a precaution. Is there a way to automate (i.e. in a job) the scripting of replication? Right-clicking in EM is becoming tedious with the number of servers we have (80+ of them SQL Servers). My manager does not want to go through just restoring msdb on the standby; he says there are issues with that. I am new enough to replication to just go with his guidance, which is why we are taking this approach.

You can take a look at RMO programming. For example, Publication object has a method Script() that can be used to create a script (see http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.replication.publication.script.aspx). Other replication objects, such as DistributionDatabase, MergePublication, has similar methods.|||At a glance, this appears to apply to SQL Server 2005. I cannot find documentation for SQL Server 2000.|||

For SQL 2000, you can use SQL DMO. Check out SQL 2000 BOL and find topic "Script Method (Replication Objects)".

Peng

|||

Peng, that was it! Sweet as pie, it worked beautifully using a VBScript in a DTS. Something I have never done, but always wanted to. I have always known that SQLDMO would eventually become a tool I needed and you've put me onto it. Thank you!

automated replication scripting

We create replication scripts on a regular basis, saving them to a warm standby server as a precaution. Is there a way to automate (i.e. in a job) the scripting of replication? Right-clicking in EM is becoming tedious with the number of servers we have (80+ of them SQL Servers). My manager does not want to go through just restoring msdb on the standby; he says there are issues with that. I am new enough to replication to just go with his guidance, which is why we are taking this approach.

You can take a look at RMO programming. For example, Publication object has a method Script() that can be used to create a script (see http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.replication.publication.script.aspx). Other replication objects, such as DistributionDatabase, MergePublication, has similar methods.|||At a glance, this appears to apply to SQL Server 2005. I cannot find documentation for SQL Server 2000.|||

For SQL 2000, you can use SQL DMO. Check out SQL 2000 BOL and find topic "Script Method (Replication Objects)".

Peng

|||

Peng, that was it! Sweet as pie, it worked beautifully using a VBScript in a DTS. Something I have never done, but always wanted to. I have always known that SQLDMO would eventually become a tool I needed and you've put me onto it. Thank you!

automate sql scripting of jobs in sql agent

In SQL Ent. Mgr -> Mgt -> SQL Svr Agent -> Jobs -> right click- > All Task ,
I can manaully script out all exisiting sql agent jobs, and keep for backup.
How can I automate this to run at a fixed schedule (eg, monthyl etc..) ?
TIAHI
Use SQL-DMO:
'_______________________________________________________
Dim conServer
Dim fso, fsoF
Dim iFile, iFolder
Dim oJB
Dim strJob
Dim strCat
Dim strFilename
Dim strPath
Const ioModeAppend = 8
Set conServer = CreateObject("SQLDMO.SQLServer")
conServer.LoginSecure = True
conServer.Connect "<>Your Server Name><" 'Minden felhasználói Job itt
található
strPath = "\\<<Your UNC path>>\Jobs\"
strFilename = strPath & "JOBS-00l1.sql" 'Naponta egy állomány készül a
dátummal megjelölve
For Each oJB In conServer.JobServer.Jobs
strCat = oJB.category
strCat = instr(1,strCat, "REPL-")
if strCat =0 then 'A replikációs Job-ok nem kerülnek a szkriptbe
strJob = strJob & "----" &
vbCrLf
strJob = strJob & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf
strJob = strJob & "----" &
vbCrLf
strJob = strJob & oJB.Script(4) & vbCrLf
strCat = oJB.category
strCat = instr(1,strCat, "REPL-")
End If
Next
Set conServer = Nothing
Set fso = CreateObject("Scripting.FileSystemObject")
Set fsoF = CreateObject("Scripting.FileSystemObject")
Set iFolder = fsoF.getfolder(strPath)
Set iFile = fso.CreateTextFile(strFilename, True)
iFile.Write (strJob)
iFile.Close
Set fso = Nothing
Set FileContainer = iFolder.Files 'A harminc napnál régebbi állományokat
letöröljük.
For Each f1 in FileContainer
if (f1.DateCreated < (Date - 30)) Then
f1.delete
end if
Next
Set fsoF = Nothing
'__________________________________________________________________
Andras Jakus MCDBA
"pk" wrote:
> In SQL Ent. Mgr -> Mgt -> SQL Svr Agent -> Jobs -> right click- > All Task ,
> I can manaully script out all exisiting sql agent jobs, and keep for backup.
> How can I automate this to run at a fixed schedule (eg, monthyl etc..) ?
> TIA
>|||PK, you may use that script provided by Andras to script out SQL jobs on qa
monthly basis or/and you may also want to look at other options:
You may also use SCRPTXFR that comes along with SQL Server 2000 which will
allow you to script out all the object in text files.
You may also backup MSDB database on a monthly basis (This will ensure not
just Jobs but alos DTS packages, alerts, etc to be backed-up)
You may also BCP out all the SysJobs table every month and incase of issues
you may just load back the data in the MSDB tables.
HTH.
Saleem@.sqlnt.com
"Andras Jakus" wrote:
> HI
> Use SQL-DMO:
> '_______________________________________________________
> Dim conServer
> Dim fso, fsoF
> Dim iFile, iFolder
> Dim oJB
> Dim strJob
> Dim strCat
> Dim strFilename
> Dim strPath
> Const ioModeAppend = 8
> Set conServer = CreateObject("SQLDMO.SQLServer")
> conServer.LoginSecure = True
> conServer.Connect "<>Your Server Name><" 'Minden felhasználói Job itt
> található
> strPath = "\\<<Your UNC path>>\Jobs\"
> strFilename = strPath & "JOBS-00l1.sql" 'Naponta egy állomány készül a
> dátummal megjelölve
> For Each oJB In conServer.JobServer.Jobs
> strCat = oJB.category
> strCat = instr(1,strCat, "REPL-")
> if strCat =0 then 'A replikációs Job-ok nem kerülnek a szkriptbe
> strJob = strJob & "----" &
> vbCrLf
> strJob = strJob & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf
> strJob = strJob & "----" &
> vbCrLf
> strJob = strJob & oJB.Script(4) & vbCrLf
> strCat = oJB.category
> strCat = instr(1,strCat, "REPL-")
> End If
> Next
> Set conServer = Nothing
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set fsoF = CreateObject("Scripting.FileSystemObject")
> Set iFolder = fsoF.getfolder(strPath)
> Set iFile = fso.CreateTextFile(strFilename, True)
> iFile.Write (strJob)
> iFile.Close
> Set fso = Nothing
> Set FileContainer = iFolder.Files 'A harminc napnál régebbi állományokat
> letöröljük.
> For Each f1 in FileContainer
> if (f1.DateCreated < (Date - 30)) Then
> f1.delete
> end if
> Next
> Set fsoF = Nothing
> '__________________________________________________________________
> Andras Jakus MCDBA
> "pk" wrote:
> > In SQL Ent. Mgr -> Mgt -> SQL Svr Agent -> Jobs -> right click- > All Task ,
> > I can manaully script out all exisiting sql agent jobs, and keep for backup.
> >
> > How can I automate this to run at a fixed schedule (eg, monthyl etc..) ?
> >
> > TIA
> >

automate sql scripting of jobs in sql agent

In SQL Ent. Mgr -> Mgt -> SQL Svr Agent -> Jobs -> right click- > All Task ,
I can manaully script out all exisiting sql agent jobs, and keep for backup.
How can I automate this to run at a fixed schedule (eg, monthyl etc..) ?
TIA
HI
Use SQL-DMO:
'_________________________________________________ ______
Dim conServer
Dim fso, fsoF
Dim iFile, iFolder
Dim oJB
Dim strJob
Dim strCat
Dim strFilename
Dim strPath
Const ioModeAppend = 8
Set conServer = CreateObject("SQLDMO.SQLServer")
conServer.LoginSecure = True
conServer.Connect "<>Your Server Name><" 'Minden felhasználói Job itt
található
strPath = "\\<<Your UNC path>>\Jobs\"
strFilename = strPath & "JOBS-00l1.sql" 'Naponta egy állomány készül a
dátummal megjel?lve
For Each oJB In conServer.JobServer.Jobs
strCat = oJB.category
strCat = instr(1,strCat, "REPL-")
if strCat =0 then 'A replikációs Job-ok nem kerülnek a szkriptbe
strJob = strJob & "----" &
vbCrLf
strJob = strJob & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf
strJob = strJob & "----" &
vbCrLf
strJob = strJob & oJB.Script(4) & vbCrLf
strCat = oJB.category
strCat = instr(1,strCat, "REPL-")
End If
Next
Set conServer = Nothing
Set fso = CreateObject("Scripting.FileSystemObject")
Set fsoF = CreateObject("Scripting.FileSystemObject")
Set iFolder = fsoF.getfolder(strPath)
Set iFile = fso.CreateTextFile(strFilename, True)
iFile.Write (strJob)
iFile.Close
Set fso = Nothing
Set FileContainer = iFolder.Files 'A harminc napnál régebbi állományokat
let?r?ljük.
For Each f1 in FileContainer
if (f1.DateCreated < (Date - 30)) Then
f1.delete
end if
Next
Set fsoF = Nothing
'_________________________________________________ _________________
Andras Jakus MCDBA
"pk" wrote:

> In SQL Ent. Mgr -> Mgt -> SQL Svr Agent -> Jobs -> right click- > All Task ,
> I can manaully script out all exisiting sql agent jobs, and keep for backup.
> How can I automate this to run at a fixed schedule (eg, monthyl etc..) ?
> TIA
>
|||PK, you may use that script provided by Andras to script out SQL jobs on qa
monthly basis or/and you may also want to look at other options:
You may also use SCRPTXFR that comes along with SQL Server 2000 which will
allow you to script out all the object in text files.
You may also backup MSDB database on a monthly basis (This will ensure not
just Jobs but alos DTS packages, alerts, etc to be backed-up)
You may also BCP out all the SysJobs table every month and incase of issues
you may just load back the data in the MSDB tables.
HTH.
Saleem@.sqlnt.com
"Andras Jakus" wrote:
[vbcol=seagreen]
> HI
> Use SQL-DMO:
> '_________________________________________________ ______
> Dim conServer
> Dim fso, fsoF
> Dim iFile, iFolder
> Dim oJB
> Dim strJob
> Dim strCat
> Dim strFilename
> Dim strPath
> Const ioModeAppend = 8
> Set conServer = CreateObject("SQLDMO.SQLServer")
> conServer.LoginSecure = True
> conServer.Connect "<>Your Server Name><" 'Minden felhasználói Job itt
> található
> strPath = "\\<<Your UNC path>>\Jobs\"
> strFilename = strPath & "JOBS-00l1.sql" 'Naponta egy állomány készül a
> dátummal megjel?lve
> For Each oJB In conServer.JobServer.Jobs
> strCat = oJB.category
> strCat = instr(1,strCat, "REPL-")
> if strCat =0 then 'A replikációs Job-ok nem kerülnek a szkriptbe
> strJob = strJob & "----" &
> vbCrLf
> strJob = strJob & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf
> strJob = strJob & "----" &
> vbCrLf
> strJob = strJob & oJB.Script(4) & vbCrLf
> strCat = oJB.category
> strCat = instr(1,strCat, "REPL-")
> End If
> Next
> Set conServer = Nothing
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set fsoF = CreateObject("Scripting.FileSystemObject")
> Set iFolder = fsoF.getfolder(strPath)
> Set iFile = fso.CreateTextFile(strFilename, True)
> iFile.Write (strJob)
> iFile.Close
> Set fso = Nothing
> Set FileContainer = iFolder.Files 'A harminc napnál régebbi állományokat
> let?r?ljük.
> For Each f1 in FileContainer
> if (f1.DateCreated < (Date - 30)) Then
> f1.delete
> end if
> Next
> Set fsoF = Nothing
> '_________________________________________________ _________________
> Andras Jakus MCDBA
> "pk" wrote:
|||Have look at
http://www.nigelrivett.net/DMOScriptAllDatabases.html
It shows how to schedule scripting most objects in the database and save
them in sourcesafe (although I haven't got round to automating that yet -
suspect I will soon).
"pk" wrote:

> In SQL Ent. Mgr -> Mgt -> SQL Svr Agent -> Jobs -> right click- > All Task ,
> I can manaully script out all exisiting sql agent jobs, and keep for backup.
> How can I automate this to run at a fixed schedule (eg, monthyl etc..) ?
> TIA
>

Auto scripting SPs, perhaps concatenating text fields...

Hi,

I'm trying to get the text of all my SPs saved into text (*.sql) files. My first thought was to use sp_helptext and bcp the table to a text file but this isn't working (see my other post) so thought I'd try another method.

I can get the code from syscomment.text and concatenate the varchar(8000) field together in a text field. Unfortunately this isn't as easy as just text = text + newtext, how is this done?

Or am I doing it all comletely the wrong way? BTW, I have over 150 SPs so I can't save them individually.

Thanks!

Nick

Why don't you use EM or SSMS for that? Both utilities have feature to scrip database objects.|||

I've just found the "create one file per object" option. Thanks gavrilenko_s!

Nick