I will be transferring database from one server to another along with the ma
ster and msdb database for use as a disaster recovery server. On the second
server, I'd like to script the loading of all the databases (including the m
aster). I can stop the SQL
Service using NET STOP MSSQLSERVER. Then, I can place the database in single
user mode using the command "sqlservr -m". Next, I used OSQL to restore the
master database (which restores the database and stops the sql server - inh
erently). The problem is th
at the command "sqlservr -m" leave the command shell open. I must close the
window by manually typing <ctrl-c> and exiting the shell. I want this to be
a hands-off operation. Any suggestions?
Thanks!
AdamFirst I believe if you just create a cmd file and run your sqlservr -m from
there I think it will kill the window.
Jeff Duncan
MCDBA, MCSE+I
"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam|||Have you tried the "exit" command?
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam|||When you run the "sqlservr -m" the command shell hangs until <ctrl-c> typed.
If you create a .bat file with the commands "sqlservr -m" followed on the n
ext line by "exit", the "exit" command would not be executed until the <cntr
l-c> was entered to end the
"sqlservr -m" command.|||When you run the "sqlservr -m" the command shell hangs until <ctrl-c> typed.
If you create a .bat file with the commands "sqlservr -m" followed on the n
ext line by "exit", the "exit" command would not be executed until the <cntr
l-c> was entered to end the
"sqlservr -m" command.|||"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
Try using:
start sqlservr -m|||Try using the TSQL "SHUTDOWN" command inside the script you run through OSQL
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Adam" <ahafner@.itape.com> wrote in message news:B4188C24-EC76-40F1-BD02-A583C87A3075@.micros
oft.com...
> I will be transferring database from one server to another along with the master a
nd msdb database for use
as a disaster recovery server. On the second server, I'd like to script the
loading of all the databases
(including the master). I can stop the SQL Service using NET STOP MSSQLSERVE
R. Then, I can place the database
in single user mode using the command "sqlservr -m". Next, I used OSQL to re
store the master database (which
restores the database and stops the sql server - inherently). The problem is
that the command "sqlservr -m"
leave the command shell open. I must close the window by manually typing <ct
rl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam|||Hi,
Since you are setting up a Disaster recovery server, instead of restoring
the databases one one by , you have a alternate method which is very easy.
I have done this many times and succeeded always.This will work out if
you're moving to a different machine with the same logical drive layout
(Same folder structure) same as production.
Steps:-
1. Install the SQL Server and the same service pack as on your existing
machine.
2 Stop the SQL service on your old machine. Copy the entire SQL7/SQL 2000
folder and it's subfolders,
plus any separate folders containing logs or data, to the new machine.
Also copy over any other folders containing
data that you want to move to the new machine.
3. Start the SQL Server service in new machine. Login to query analyzer and
execute sp_dropserver <drop server> and use sp_addserver <new server>,Local
4. Stop and Start the SQL Server service
5. Check all the database are running fine.
6. Start the SQL Server in production server.
Have a look into Vyas's site
http://vyaskn.tripod.com/moving_sql_server.htm
Thanks
Hari
MCDBA
"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam|||I can not bring down the production server at any time during the day (24x7)
.
Showing posts with label msdb. Show all posts
Showing posts with label msdb. Show all posts
Tuesday, March 20, 2012
Script Master DB Restore
I will be transferring database from one server to another along with the master and msdb database for use as a disaster recovery server. On the second server, I'd like to script the loading of all the databases (including the master). I can stop the SQL
Service using NET STOP MSSQLSERVER. Then, I can place the database in single user mode using the command "sqlservr -m". Next, I used OSQL to restore the master database (which restores the database and stops the sql server - inherently). The problem is th
at the command "sqlservr -m" leave the command shell open. I must close the window by manually typing <ctrl-c> and exiting the shell. I want this to be a hands-off operation. Any suggestions?
Thanks!
Adam
First I believe if you just create a cmd file and run your sqlservr -m from
there I think it will kill the window.
Jeff Duncan
MCDBA, MCSE+I
"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam
|||Have you tried the "exit" command?
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam
|||When you run the "sqlservr -m" the command shell hangs until <ctrl-c> typed. If you create a .bat file with the commands "sqlservr -m" followed on the next line by "exit", the "exit" command would not be executed until the <cntrl-c> was entered to end the
"sqlservr -m" command.
|||When you run the "sqlservr -m" the command shell hangs until <ctrl-c> typed. If you create a .bat file with the commands "sqlservr -m" followed on the next line by "exit", the "exit" command would not be executed until the <cntrl-c> was entered to end the
"sqlservr -m" command.
|||"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
Try using:
start sqlservr -m
|||Try using the TSQL "SHUTDOWN" command inside the script you run through OSQL.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Adam" <ahafner@.itape.com> wrote in message news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the master and msdb database for use
as a disaster recovery server. On the second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP MSSQLSERVER. Then, I can place the database
in single user mode using the command "sqlservr -m". Next, I used OSQL to restore the master database (which
restores the database and stops the sql server - inherently). The problem is that the command "sqlservr -m"
leave the command shell open. I must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam
|||Hi,
Since you are setting up a Disaster recovery server, instead of restoring
the databases one one by , you have a alternate method which is very easy.
I have done this many times and succeeded always.This will work out if
you're moving to a different machine with the same logical drive layout
(Same folder structure) same as production.
Steps:-
1. Install the SQL Server and the same service pack as on your existing
machine.
2 Stop the SQL service on your old machine. Copy the entire SQL7/SQL 2000
folder and it's subfolders,
plus any separate folders containing logs or data, to the new machine.
Also copy over any other folders containing
data that you want to move to the new machine.
3. Start the SQL Server service in new machine. Login to query analyzer and
execute sp_dropserver <drop server> and use sp_addserver <new server>,Local
4. Stop and Start the SQL Server service
5. Check all the database are running fine.
6. Start the SQL Server in production server.
Have a look into Vyas's site
http://vyaskn.tripod.com/moving_sql_server.htm
Thanks
Hari
MCDBA
"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam
|||I can not bring down the production server at any time during the day (24x7).
Service using NET STOP MSSQLSERVER. Then, I can place the database in single user mode using the command "sqlservr -m". Next, I used OSQL to restore the master database (which restores the database and stops the sql server - inherently). The problem is th
at the command "sqlservr -m" leave the command shell open. I must close the window by manually typing <ctrl-c> and exiting the shell. I want this to be a hands-off operation. Any suggestions?
Thanks!
Adam
First I believe if you just create a cmd file and run your sqlservr -m from
there I think it will kill the window.
Jeff Duncan
MCDBA, MCSE+I
"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam
|||Have you tried the "exit" command?
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam
|||When you run the "sqlservr -m" the command shell hangs until <ctrl-c> typed. If you create a .bat file with the commands "sqlservr -m" followed on the next line by "exit", the "exit" command would not be executed until the <cntrl-c> was entered to end the
"sqlservr -m" command.
|||When you run the "sqlservr -m" the command shell hangs until <ctrl-c> typed. If you create a .bat file with the commands "sqlservr -m" followed on the next line by "exit", the "exit" command would not be executed until the <cntrl-c> was entered to end the
"sqlservr -m" command.
|||"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
Try using:
start sqlservr -m
|||Try using the TSQL "SHUTDOWN" command inside the script you run through OSQL.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Adam" <ahafner@.itape.com> wrote in message news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the master and msdb database for use
as a disaster recovery server. On the second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP MSSQLSERVER. Then, I can place the database
in single user mode using the command "sqlservr -m". Next, I used OSQL to restore the master database (which
restores the database and stops the sql server - inherently). The problem is that the command "sqlservr -m"
leave the command shell open. I must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam
|||Hi,
Since you are setting up a Disaster recovery server, instead of restoring
the databases one one by , you have a alternate method which is very easy.
I have done this many times and succeeded always.This will work out if
you're moving to a different machine with the same logical drive layout
(Same folder structure) same as production.
Steps:-
1. Install the SQL Server and the same service pack as on your existing
machine.
2 Stop the SQL service on your old machine. Copy the entire SQL7/SQL 2000
folder and it's subfolders,
plus any separate folders containing logs or data, to the new machine.
Also copy over any other folders containing
data that you want to move to the new machine.
3. Start the SQL Server service in new machine. Login to query analyzer and
execute sp_dropserver <drop server> and use sp_addserver <new server>,Local
4. Stop and Start the SQL Server service
5. Check all the database are running fine.
6. Start the SQL Server in production server.
Have a look into Vyas's site
http://vyaskn.tripod.com/moving_sql_server.htm
Thanks
Hari
MCDBA
"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam
|||I can not bring down the production server at any time during the day (24x7).
Wednesday, March 7, 2012
script all MSDB jobs in TSQL
I know of a way to script all SQL Agent jobs using EM. But i want to script
all my jobs on a daily basis and copy that script file to another server . I
know how i can do the daily part of it which would be scheduling it again
but whats the TSQL or command I need to run within the job step .. ThanksYou can use Distributed Management Objects (DMO), not T-SQL. Check the
article at
http://www.winnetmag.com/SQLServer/...790/38790.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uEV1iN8iEHA.3988@.tk2msftngp13.phx.gbl...
> I know of a way to script all SQL Agent jobs using EM. But i want to
script
> all my jobs on a daily basis and copy that script file to another server .
I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>|||You can set up a job that uses an ActiveX script type job step containing
the following
Set oSQL = CreateObject("SQLDMO.SQLServer")
oSQL.Name = "(local)"
oSQL.LoginSecure = True
oSQL.LoginTimeout = 10
oSQL.Connect
Set oJobs = oSQL.JobServer.Jobs
oJobs.Script 4,"c:\jobs.sql",2048
oSQL.DisConnect
Set oSQL = Nothing
This will script all your jobs to c:\jobs.sql. For a named instance, change
the oSQL.Name
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uEV1iN8iEHA.3988@.tk2msftngp13.phx.gbl...
>I know of a way to script all SQL Agent jobs using EM. But i want to script
> all my jobs on a daily basis and copy that script file to another server .
> I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>|||one thing to watch out for is that the job owner (@.owner_login_name) will be
scripted out too. If login doesn't exist or in a different domain / env this
may cause you problems.
Could parse the Scripted text (can use the Script object just to return text
as opposed to file) and do a replace operation on the @.owner_login_name =
'xx' and replace with @.owner_login_name = null, so that sp_add_job will
create the job with owner of the user executing the script.
Then write the corrected text to File.
cheers,
Andy
"Hassan" wrote:
> I know of a way to script all SQL Agent jobs using EM. But i want to scrip
t
> all my jobs on a daily basis and copy that script file to another server .
I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
>|||... or use the DTS Transfer Jobs task !
"Hassan" wrote:
> I know of a way to script all SQL Agent jobs using EM. But i want to scrip
t
> all my jobs on a daily basis and copy that script file to another server .
I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
>
all my jobs on a daily basis and copy that script file to another server . I
know how i can do the daily part of it which would be scheduling it again
but whats the TSQL or command I need to run within the job step .. ThanksYou can use Distributed Management Objects (DMO), not T-SQL. Check the
article at
http://www.winnetmag.com/SQLServer/...790/38790.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uEV1iN8iEHA.3988@.tk2msftngp13.phx.gbl...
> I know of a way to script all SQL Agent jobs using EM. But i want to
script
> all my jobs on a daily basis and copy that script file to another server .
I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>|||You can set up a job that uses an ActiveX script type job step containing
the following
Set oSQL = CreateObject("SQLDMO.SQLServer")
oSQL.Name = "(local)"
oSQL.LoginSecure = True
oSQL.LoginTimeout = 10
oSQL.Connect
Set oJobs = oSQL.JobServer.Jobs
oJobs.Script 4,"c:\jobs.sql",2048
oSQL.DisConnect
Set oSQL = Nothing
This will script all your jobs to c:\jobs.sql. For a named instance, change
the oSQL.Name
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uEV1iN8iEHA.3988@.tk2msftngp13.phx.gbl...
>I know of a way to script all SQL Agent jobs using EM. But i want to script
> all my jobs on a daily basis and copy that script file to another server .
> I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>|||one thing to watch out for is that the job owner (@.owner_login_name) will be
scripted out too. If login doesn't exist or in a different domain / env this
may cause you problems.
Could parse the Scripted text (can use the Script object just to return text
as opposed to file) and do a replace operation on the @.owner_login_name =
'xx' and replace with @.owner_login_name = null, so that sp_add_job will
create the job with owner of the user executing the script.
Then write the corrected text to File.
cheers,
Andy
"Hassan" wrote:
> I know of a way to script all SQL Agent jobs using EM. But i want to scrip
t
> all my jobs on a daily basis and copy that script file to another server .
I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
>|||... or use the DTS Transfer Jobs task !
"Hassan" wrote:
> I know of a way to script all SQL Agent jobs using EM. But i want to scrip
t
> all my jobs on a daily basis and copy that script file to another server .
I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
>
script all MSDB jobs in TSQL
I know of a way to script all SQL Agent jobs using EM. But i want to script
all my jobs on a daily basis and copy that script file to another server . I
know how i can do the daily part of it which would be scheduling it again
but whats the TSQL or command I need to run within the job step .. ThanksYou can use Distributed Management Objects (DMO), not T-SQL. Check the
article at
http://www.winnetmag.com/SQLServer/Article/ArticleID/38790/38790.html.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uEV1iN8iEHA.3988@.tk2msftngp13.phx.gbl...
> I know of a way to script all SQL Agent jobs using EM. But i want to
script
> all my jobs on a daily basis and copy that script file to another server .
I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>|||You can set up a job that uses an ActiveX script type job step containing
the following
Set oSQL = CreateObject("SQLDMO.SQLServer")
oSQL.Name = "(local)"
oSQL.LoginSecure = True
oSQL.LoginTimeout = 10
oSQL.Connect
Set oJobs = oSQL.JobServer.Jobs
oJobs.Script 4,"c:\jobs.sql",2048
oSQL.DisConnect
Set oSQL = Nothing
This will script all your jobs to c:\jobs.sql. For a named instance, change
the oSQL.Name
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uEV1iN8iEHA.3988@.tk2msftngp13.phx.gbl...
>I know of a way to script all SQL Agent jobs using EM. But i want to script
> all my jobs on a daily basis and copy that script file to another server .
> I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>|||one thing to watch out for is that the job owner (@.owner_login_name) will be
scripted out too. If login doesn't exist or in a different domain / env this
may cause you problems.
Could parse the Scripted text (can use the Script object just to return text
as opposed to file) and do a replace operation on the @.owner_login_name ='xx' and replace with @.owner_login_name = null, so that sp_add_job will
create the job with owner of the user executing the script.
Then write the corrected text to File.
cheers,
Andy
"Hassan" wrote:
> I know of a way to script all SQL Agent jobs using EM. But i want to script
> all my jobs on a daily basis and copy that script file to another server . I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
>
all my jobs on a daily basis and copy that script file to another server . I
know how i can do the daily part of it which would be scheduling it again
but whats the TSQL or command I need to run within the job step .. ThanksYou can use Distributed Management Objects (DMO), not T-SQL. Check the
article at
http://www.winnetmag.com/SQLServer/Article/ArticleID/38790/38790.html.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uEV1iN8iEHA.3988@.tk2msftngp13.phx.gbl...
> I know of a way to script all SQL Agent jobs using EM. But i want to
script
> all my jobs on a daily basis and copy that script file to another server .
I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>|||You can set up a job that uses an ActiveX script type job step containing
the following
Set oSQL = CreateObject("SQLDMO.SQLServer")
oSQL.Name = "(local)"
oSQL.LoginSecure = True
oSQL.LoginTimeout = 10
oSQL.Connect
Set oJobs = oSQL.JobServer.Jobs
oJobs.Script 4,"c:\jobs.sql",2048
oSQL.DisConnect
Set oSQL = Nothing
This will script all your jobs to c:\jobs.sql. For a named instance, change
the oSQL.Name
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uEV1iN8iEHA.3988@.tk2msftngp13.phx.gbl...
>I know of a way to script all SQL Agent jobs using EM. But i want to script
> all my jobs on a daily basis and copy that script file to another server .
> I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>|||one thing to watch out for is that the job owner (@.owner_login_name) will be
scripted out too. If login doesn't exist or in a different domain / env this
may cause you problems.
Could parse the Scripted text (can use the Script object just to return text
as opposed to file) and do a replace operation on the @.owner_login_name ='xx' and replace with @.owner_login_name = null, so that sp_add_job will
create the job with owner of the user executing the script.
Then write the corrected text to File.
cheers,
Andy
"Hassan" wrote:
> I know of a way to script all SQL Agent jobs using EM. But i want to script
> all my jobs on a daily basis and copy that script file to another server . I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
>
script all MSDB jobs in TSQL
I know of a way to script all SQL Agent jobs using EM. But i want to script
all my jobs on a daily basis and copy that script file to another server . I
know how i can do the daily part of it which would be scheduling it again
but whats the TSQL or command I need to run within the job step .. Thanks
You can use Distributed Management Objects (DMO), not T-SQL. Check the
article at
http://www.winnetmag.com/SQLServer/A...90/38790.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uEV1iN8iEHA.3988@.tk2msftngp13.phx.gbl...
> I know of a way to script all SQL Agent jobs using EM. But i want to
script
> all my jobs on a daily basis and copy that script file to another server .
I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
|||You can set up a job that uses an ActiveX script type job step containing
the following
Set oSQL = CreateObject("SQLDMO.SQLServer")
oSQL.Name = "(local)"
oSQL.LoginSecure = True
oSQL.LoginTimeout = 10
oSQL.Connect
Set oJobs = oSQL.JobServer.Jobs
oJobs.Script 4,"c:\jobs.sql",2048
oSQL.DisConnect
Set oSQL = Nothing
This will script all your jobs to c:\jobs.sql. For a named instance, change
the oSQL.Name
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uEV1iN8iEHA.3988@.tk2msftngp13.phx.gbl...
>I know of a way to script all SQL Agent jobs using EM. But i want to script
> all my jobs on a daily basis and copy that script file to another server .
> I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
|||one thing to watch out for is that the job owner (@.owner_login_name) will be
scripted out too. If login doesn't exist or in a different domain / env this
may cause you problems.
Could parse the Scripted text (can use the Script object just to return text
as opposed to file) and do a replace operation on the @.owner_login_name =
'xx' and replace with @.owner_login_name = null, so that sp_add_job will
create the job with owner of the user executing the script.
Then write the corrected text to File.
cheers,
Andy
"Hassan" wrote:
> I know of a way to script all SQL Agent jobs using EM. But i want to script
> all my jobs on a daily basis and copy that script file to another server . I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
>
|||try attached
rename to .vbs
usage CSCRIPT ScriptAllJobs.vbs <Server> <FileName> <owner>
Server is Source Server where you want to script jobs from
FileName is output file for the script
owner is the new owner of the jobs. either do NULL (which will give make the
owner the user who runs the create script) or 'somelogin' ie the NT Login
that should be the owner ie 'Domainx\Jobowner' - with the quotes.
hope that makes sense
I had to do a bit of hacking cos a) the owner is hard coded and b) the
servername is hard coded (ie replace this with @.@.ServerName). Not too happy
about this, I think I might have missed a DMO Option somewhere
ai
Andy.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uEV1iN8iEHA.3988@.tk2msftngp13.phx.gbl...
> I know of a way to script all SQL Agent jobs using EM. But i want to
script
> all my jobs on a daily basis and copy that script file to another server .
I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
begin 666 ScriptAllJobs.txt
M)R!38W)I<'1!;&Q*;V)S+G-Q;" -"B<@.4V-R:7!T<R!A;&P@.:F]B<R!O;B!A
M(&=I=F5N(%-13"!397)V97(@.86YD(')E<&QA8V5S('1H92!H87)D8V]D960@.
M<F5F97)E;F-E<R!T;R!A;&QO=R!T;R!B92 -"B<@.8V]P:65D('1O(&%N;W1H
M97(@.<V5R=F5R#0HG($%N9'D@.0F%L;" R-"\X+S(P,#0)=C$N, E"87-E(%9E
M<G-I;VX-"@.T*3W!T:6]N($5X<&QI8VET#0H)( T*)R!344PM1$U/(&-O;G-T
M86YT<PT*0V]N<W0@.4U%,1$U/4V-R:7!T7T1R;W!S(#T@.,0T*#0I#;VYS="!3
M44Q$34]38W)I<'1?4')I;6%R>4]B:F5C=" ](#0@.#0I#;VYS="!344Q$34]3
M8W)I<'1?5&]&:6QE3VYL>2 ](#8T#0I#;VYS="!344Q$34]38W)I<'1?07!P
M96YD=&]&:6QE(#T@.,C4V#0H-"D-O;G-T(%-13$1-3U-C<FEP=#)?3&]G:6Y3
M240@./2 X,3DR#0H-"E!U8FQI8R!38W)I<'1/8FIE8W1/<'1I;VYS#0I38W)I
M<'1/8FIE8W1/<'1I;VYS(#T@.("!344Q$34]38W)I<'1?4')I;6%R>4]B:F5C
M=" K(%\-"@.D)"5-13$1-3U-C<FEP=%]$<F]P<R -"@.T*1&EM(&]!<F=S(" @.
M(" @.(" @.(" @.(" @.(" @.(" @.(" ))R!!<F=U;65N=',-"D1I;2!7<VA3:&5L
M; D)"0DG(&9O<B!7:6YD;W=S(%-C<FEP=&EN9R!(;W-T#0I$:6T@.4V5R=F5R
M3F%M92 @.(" @.(" @.(" @.(" @.(" @.(" @.( DG('1H92!397)V97(-"D1I;2!&
M:6QE;F%M90D)"0DG($9I;&5N86UE('1O('=R:71E('1O( T*1&EM(%-13%-T
M<FEN9PT*1&EM($YE=T]W;F5R3F%M92 -"@.T*)R!#<F5A=&4@.4VAE;&P@.;V)J
M96-T#0IS970@.5W-H4VAE;&P@./2!#<F5A=&5/8FIE8W0@.*")7<V-R:7!T+E-H
M96QL(BD-"@.T*)R!087)A;65T97)S#0IS970@.;T%R9W,@./2!7<V-R:7!T+D%R
M9W5M96YT<PT*#0HG(&-H96-K(&-O<G)E8W0@.;G5M8F5R(&]F(&%R9W,-"FEF
M("AO07)G<RY#;W5N=" \/B S*2!4:&5N#0H)5W-C<FEP="Y%8VAO(")5<V%G
M92 Z(&-S8W)I<'0@.4V-R:7!T06QL2F]B<RYV8G,@./'-E<G9E<CX@./$9I;&5.
M86UE/B \3F5W3W=N97(^(@.T*"5=S8W)I<'0N16-H;R B:2YE+B @..B!C<V-R
M:7!T(%-C<FEP=$%L;$IO8G,N=F)S(%!23T1397)V97(@.2F]B<RYS<6P@.<V$B
M#0H)5W-C<FEP="Y1=6ET(" @.( T*16QS90T*"2<@.;VL@.<V\@.87-S:6=N('!A
M<F%M<PT*"5-E<G9E<DYA;64@.(" @.(" ](&]!<F=S*# I#0H)1FEL94YA;64)
M/2!O07)G<R@.Q*0T*"4YE=T]W;F5R3F%M90D](&]!<F=S*#(I#0I%;F0@.268-
M"@.T*)R!-86EN(%-C<FEP= T*4V-R:7!T06QL2F]B<R!397)V97).86UE+"!&
M:6QE3F%M90T*#0HG($5N9"!O9B!-86EN#0H-"E-U8B!38W)I<'1!;&Q*;V)S
M("A397)V97).86UE+"!&:6QE3F%M92D-"@.E$:6T@.;T1A=&%B87-E(" @.(" @.
M(" @.(" @.(" @.(" @.("<@.=&AE('1A<F=E="!D871A8F%S92!T;R!U<V4-"@.E$
M:6T@.4V-R:7!T4W1R:6YG(" @.(" @.(" @.(" @.(" @.("<@.<F5T=7)N<R!I;G1O
M(&AE<F4@.=')A;G-A8W0@.<W%L(&9O<B!O8FIE8W0-"@.E$:6T@.4U%,3W5T<'5T
M1FEL90D))R!F:6QE('1H92!S=&]R960@.<')O8V5D=7)E(&ES('=R:71T96X@.
M=&\@.#0H)1&EM(&]397)V97(-"@.E$:6T@.9G,-"@.T*"41I;2!F<T]U='!U=$9I
M;&4-"@.T*"41I;2!*;V(-"@.E$:6T@.2F]B4W1R:6YG#0H)1&EM($1R;W!%>&ES
M=&EN9TIO8E-T<FEN9PT*#0H))R!C<F5A=&4@.4U%,(%-E<G9E<B!O8FIE8W0-
M"@.E3970@.;U-E<G9E<B ]($-R96%T94]B:F5C="@.B4U%,1$U/+E-13%-E<G9E
M<B(I#0H-"@.DG(&%L=V%Y<R!U<V4@.82!T<G5S=&5D(&-O;FYE8W1I;VX@.86YD
M(&-O;FYE8W0-"@.EO4V5R=F5R+DQO9VEN4V5C=7)E(#T@.5')U90T*"6]397)V
M97(N0V]N;F5C="!397)V97).86UE("<@.=')U<W1E9"!C;VYN96-T:6]N#0H-
M"@.DG(%)O;&P@.=&AR;W5G:"!E86-H(&IO8B!A;F0@.9V5N97)A=&4@.=&AE(%-1
M3"P@.:&%C:R!O=70@.:&%R9&-O9&5D('-T=69F(&%N9"!A<'!E;F0@.=&\@.4V-R
M:7!T4W1R:6YG('9A<FEA8FQE#0H)1F]R($5A8V@.@.2F]B(&EN(&]397)V97(N
M2F]B4V5R=F5R+DIO8G,-"@.D))R!$<F]P('1H92!J;V(@.:68@.:70@.97AI<W1S
M+B!3:&]U;&0@.8F4@.82!$34\@.;W!T:6]N(&9O<B!T:&ES(#\_/S\_#0H)"41R
M;W!%>&ES=&EN9TIO8E-T<FEN9R ](")"14=)3B!44D%.4T%#5$E/3B(@.*R!V
M8D-23$8-"@.D)1')O<$5X:7-T:6YG2F]B4W1R:6YG(#T@.1')O<$5X:7-T:6YG
M2F]B4W1R:6YG("L@.(DE&($5825-44R H4T5,14-4("H@.1E)/32!M<V1B+F1B
M;RYS>7-J;V)S(%=(15)%(&YA;64@./2 G(B K($IO8BY.86UE("L@.(B<I(B K
M('9B0U),1@.T*"0E$<F]P17AI<W1I;F=*;V)3=')I;F<@./2!$<F]P17AI<W1I
M;F=*;V)3=')I;F<@.*R!V8E1!0B K(")%6$5#(&US9&(N9&)O+G-P7V1E;&5T
M95]J;V(@.0&IO8E]N86UE(#T@.)R(@.*R!*;V(N3F%M92 K("(G(B K('9B0U),
M1@.T*"0E$<F]P17AI<W1I;F=*;V)3=')I;F<@./2!$<F]P17AI<W1I;F=*;V)3
M=')I;F<@.*R B1T\B("L@.=F)#4DQ�H)"4IO8E-T<FEN9R ]($IO8BY38W)I
M<'0@.*%-13$1-3U-C<FEP=%]0<FEM87)Y3V)J96-T*0T*"0E*;V)3=')I;F<@.
M/2!215!,04-%*$IO8E-T<FEN9RP@.(F)E9VEN('1R86YS86-T:6]N(BP@.1')O
M<$5X:7-T:6YG2F]B4W1R:6YG*0T*"0D-"@.D))R!R97!L86-E('1H92!O=VYE
M<B!W:71H($Y53$P@.<V\@.=&AA="!I="!C<F5A=&5S(&IO8B!A< R!T:&4@.=7-E
M<B!E>&5C=71I;F<@.=&AE('-C<FEP= T*"0E*;V)3=')I;F<@./2!297!L86-E
M2&%R9$-O9&5D4F5F*$IO8E-T<FEN9RP@.(D!O=VYE<E]L;V=I;E]N86UE(BP@.
M3F5W3W=N97).86UE("D-"@.D))R!297!L86-E(%-E<G9E<FYA;64@.=7-E9"!I
M;B!S<%]!9&1?:F]B7U-E<G9E<B!T;R!B92!T:&4@.8W5R<F5N="!S97)V97(L
M(&]T:&5R=VES92!I="!W:6QL('5S92!T:&4@.<V5R=F5R;F%M92!S8 W)I<'0@.
M9G)O;2!I9B!L;V-A;"!I;G-T86YC90T*"0E*;V)3=')I;F<@./2!297!L86-E
M2&%R9$-O9&5D4F5F*$IO8E-T<FEN9RP@.(D!S97)V97)?;F%M92(L(") 0%-%
M4E9%4DY!344B*0T*#0H)"2<@.061D('1O('1H92!E>&ES=&EN9 R!3=')I;F<-
M"@.D)4V-R:7!T4W1R:6YG(#T@.4V-R:7!T4W1R:6YG("L@.=F)#4DQ&("L@.2F]B
M4W1R:6YG#0H)"5=38W)I<'0N16-H;R B5W)I=&EN9R!J;V(@.(B K($IO8BY.
M86UE("L@.(B!T;R B("L@.1FEL94YA;64-"@.E.97AT( T*#0H))R!.;W<@.9FEN
M86QL>2!W<FET92!T:&4@.9FEL90T*"7-E="!F<R ]($-R96%T94]B:F5C="@.B
M4V-R:7!T:6YG+D9I;&53>7-T96U/8FIE8W0B*0T*"5-E="!F<T]U='!U=$9I
M;&4@./2!F<RY#<F5A=&5497AT1FEL92A&:6QE3F%M92P@.5')U92D-"@.EF<T]U
M='!U=$9I;&4N5W)I=&5,:6YE(%-C<FEP=%-T<FEN9PT*"69S3W5T<'5T1FEL
M92Y#;&]S90T*16YD(%-U8@.T*#0HG(%-E87)C:"!F;W(@.82!3=')I;F<@.=&AA
M="!H87,@.0'-E<G9E<B ](&XG9&]O9F5R)R!T>7!E('-Y;G1A>"!A;F0@.<F5T
M=7)N(&XG9&]O9F5R)R L('=H97)E(%-E87)C:%-T<FEN9R!I<R! <V5R=F5R
M(&EN('1H:7,@.8V%S92X-"B<@.4V\@.=V4@.8V%N(')E<&QA8V4@.:&%R9&-O9&5D
M(')E9F5R;F-E<RX@.#0I&=6YC=&EO;B!297!L86-E2&%R9$-O9&5D4F5F("A3
M44Q3=')I;F<L(%-E87)C:%-T<FEN9RP@.4F5P;&%C95-T<FEN9RD-"@.E$:6T@.
M3F5W4W1R:6YG#0H)1&EM(%-E87)C:%-T<FEN9U!O<PT*"41I;2!396-O;F11
M=6]T95!O<PT*#0H)1&EM($QO9VEN3F%M90T*"41I;2!344Q3=')I; F=,96X-
M"@.T*"5-13%-T<FEN9TQE;B ]($QE;BA344Q3=')I;F<I#0H-"@.DG(&=E="!T
M:&4@.;&]C871I;VX@.;V8@.=&AE(&]W;F5R7VQO9VEN7VYA;64@.8VAA<@.T*"5-E
M87)C:%-T<FEN9U!O<R ]($EN4W1R*#$L(%-13%-T<FEN9RP@.4V5A<F-H4W1R
M:6YG+" P*0T*#0H))R!G970@.82!3=')I;F<@.+"!S=&%R=&EN9R!W:71H( $XG
M;&]G:6X@.;F%M92<-"@.E,;V=I;DYA;64@./2!-:60H4U%,4W1R:6YG+"!396%R
M8VA3=')I;F=0;W,@.*R!,14XH4V5A<F-H4W1R:6YG*2 K(#,L(%-13%-T<FEN
M9TQE;B M(%-E87)C:%-T<FEN9U!O<RD-"@.D-"@.E396-O;F11=6]T95!O<R ]
M($EN4W1R*#,L($QO9VEN3F%M92P@.(B<B+" P*0D)#0H)3&]G:6Y.86UE(#T@.
M36ED*$QO9VEN3F%M92P@.,2P@.4V5C;VYD475O=&50;W,I#0H)# 0H))R!74V-R
M:7!T+D5C:&\@.3&]G:6Y.86UE#0H)4F5P;&%C94AA<F1#;V1E9%)E9B ](%)E
M<&QA8V4H4U%,4W1R:6YG+"!,;V=I;DYA;64L(%)E<&QA8V53= ')I;F<I#0I%
6;F0@.1G5N8W1I;VX@.#0H-"@.T*#0H-"@.``
`
end
|||... or use the DTS Transfer Jobs task !
"Hassan" wrote:
> I know of a way to script all SQL Agent jobs using EM. But i want to script
> all my jobs on a daily basis and copy that script file to another server . I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
>
all my jobs on a daily basis and copy that script file to another server . I
know how i can do the daily part of it which would be scheduling it again
but whats the TSQL or command I need to run within the job step .. Thanks
You can use Distributed Management Objects (DMO), not T-SQL. Check the
article at
http://www.winnetmag.com/SQLServer/A...90/38790.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uEV1iN8iEHA.3988@.tk2msftngp13.phx.gbl...
> I know of a way to script all SQL Agent jobs using EM. But i want to
script
> all my jobs on a daily basis and copy that script file to another server .
I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
|||You can set up a job that uses an ActiveX script type job step containing
the following
Set oSQL = CreateObject("SQLDMO.SQLServer")
oSQL.Name = "(local)"
oSQL.LoginSecure = True
oSQL.LoginTimeout = 10
oSQL.Connect
Set oJobs = oSQL.JobServer.Jobs
oJobs.Script 4,"c:\jobs.sql",2048
oSQL.DisConnect
Set oSQL = Nothing
This will script all your jobs to c:\jobs.sql. For a named instance, change
the oSQL.Name
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uEV1iN8iEHA.3988@.tk2msftngp13.phx.gbl...
>I know of a way to script all SQL Agent jobs using EM. But i want to script
> all my jobs on a daily basis and copy that script file to another server .
> I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
|||one thing to watch out for is that the job owner (@.owner_login_name) will be
scripted out too. If login doesn't exist or in a different domain / env this
may cause you problems.
Could parse the Scripted text (can use the Script object just to return text
as opposed to file) and do a replace operation on the @.owner_login_name =
'xx' and replace with @.owner_login_name = null, so that sp_add_job will
create the job with owner of the user executing the script.
Then write the corrected text to File.
cheers,
Andy
"Hassan" wrote:
> I know of a way to script all SQL Agent jobs using EM. But i want to script
> all my jobs on a daily basis and copy that script file to another server . I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
>
|||try attached
rename to .vbs
usage CSCRIPT ScriptAllJobs.vbs <Server> <FileName> <owner>
Server is Source Server where you want to script jobs from
FileName is output file for the script
owner is the new owner of the jobs. either do NULL (which will give make the
owner the user who runs the create script) or 'somelogin' ie the NT Login
that should be the owner ie 'Domainx\Jobowner' - with the quotes.
hope that makes sense
I had to do a bit of hacking cos a) the owner is hard coded and b) the
servername is hard coded (ie replace this with @.@.ServerName). Not too happy
about this, I think I might have missed a DMO Option somewhere
ai
Andy.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uEV1iN8iEHA.3988@.tk2msftngp13.phx.gbl...
> I know of a way to script all SQL Agent jobs using EM. But i want to
script
> all my jobs on a daily basis and copy that script file to another server .
I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
begin 666 ScriptAllJobs.txt
M)R!38W)I<'1!;&Q*;V)S+G-Q;" -"B<@.4V-R:7!T<R!A;&P@.:F]B<R!O;B!A
M(&=I=F5N(%-13"!397)V97(@.86YD(')E<&QA8V5S('1H92!H87)D8V]D960@.
M<F5F97)E;F-E<R!T;R!A;&QO=R!T;R!B92 -"B<@.8V]P:65D('1O(&%N;W1H
M97(@.<V5R=F5R#0HG($%N9'D@.0F%L;" R-"\X+S(P,#0)=C$N, E"87-E(%9E
M<G-I;VX-"@.T*3W!T:6]N($5X<&QI8VET#0H)( T*)R!344PM1$U/(&-O;G-T
M86YT<PT*0V]N<W0@.4U%,1$U/4V-R:7!T7T1R;W!S(#T@.,0T*#0I#;VYS="!3
M44Q$34]38W)I<'1?4')I;6%R>4]B:F5C=" ](#0@.#0I#;VYS="!344Q$34]3
M8W)I<'1?5&]&:6QE3VYL>2 ](#8T#0I#;VYS="!344Q$34]38W)I<'1?07!P
M96YD=&]&:6QE(#T@.,C4V#0H-"D-O;G-T(%-13$1-3U-C<FEP=#)?3&]G:6Y3
M240@./2 X,3DR#0H-"E!U8FQI8R!38W)I<'1/8FIE8W1/<'1I;VYS#0I38W)I
M<'1/8FIE8W1/<'1I;VYS(#T@.("!344Q$34]38W)I<'1?4')I;6%R>4]B:F5C
M=" K(%\-"@.D)"5-13$1-3U-C<FEP=%]$<F]P<R -"@.T*1&EM(&]!<F=S(" @.
M(" @.(" @.(" @.(" @.(" @.(" @.(" ))R!!<F=U;65N=',-"D1I;2!7<VA3:&5L
M; D)"0DG(&9O<B!7:6YD;W=S(%-C<FEP=&EN9R!(;W-T#0I$:6T@.4V5R=F5R
M3F%M92 @.(" @.(" @.(" @.(" @.(" @.(" @.( DG('1H92!397)V97(-"D1I;2!&
M:6QE;F%M90D)"0DG($9I;&5N86UE('1O('=R:71E('1O( T*1&EM(%-13%-T
M<FEN9PT*1&EM($YE=T]W;F5R3F%M92 -"@.T*)R!#<F5A=&4@.4VAE;&P@.;V)J
M96-T#0IS970@.5W-H4VAE;&P@./2!#<F5A=&5/8FIE8W0@.*")7<V-R:7!T+E-H
M96QL(BD-"@.T*)R!087)A;65T97)S#0IS970@.;T%R9W,@./2!7<V-R:7!T+D%R
M9W5M96YT<PT*#0HG(&-H96-K(&-O<G)E8W0@.;G5M8F5R(&]F(&%R9W,-"FEF
M("AO07)G<RY#;W5N=" \/B S*2!4:&5N#0H)5W-C<FEP="Y%8VAO(")5<V%G
M92 Z(&-S8W)I<'0@.4V-R:7!T06QL2F]B<RYV8G,@./'-E<G9E<CX@./$9I;&5.
M86UE/B \3F5W3W=N97(^(@.T*"5=S8W)I<'0N16-H;R B:2YE+B @..B!C<V-R
M:7!T(%-C<FEP=$%L;$IO8G,N=F)S(%!23T1397)V97(@.2F]B<RYS<6P@.<V$B
M#0H)5W-C<FEP="Y1=6ET(" @.( T*16QS90T*"2<@.;VL@.<V\@.87-S:6=N('!A
M<F%M<PT*"5-E<G9E<DYA;64@.(" @.(" ](&]!<F=S*# I#0H)1FEL94YA;64)
M/2!O07)G<R@.Q*0T*"4YE=T]W;F5R3F%M90D](&]!<F=S*#(I#0I%;F0@.268-
M"@.T*)R!-86EN(%-C<FEP= T*4V-R:7!T06QL2F]B<R!397)V97).86UE+"!&
M:6QE3F%M90T*#0HG($5N9"!O9B!-86EN#0H-"E-U8B!38W)I<'1!;&Q*;V)S
M("A397)V97).86UE+"!&:6QE3F%M92D-"@.E$:6T@.;T1A=&%B87-E(" @.(" @.
M(" @.(" @.(" @.(" @.("<@.=&AE('1A<F=E="!D871A8F%S92!T;R!U<V4-"@.E$
M:6T@.4V-R:7!T4W1R:6YG(" @.(" @.(" @.(" @.(" @.("<@.<F5T=7)N<R!I;G1O
M(&AE<F4@.=')A;G-A8W0@.<W%L(&9O<B!O8FIE8W0-"@.E$:6T@.4U%,3W5T<'5T
M1FEL90D))R!F:6QE('1H92!S=&]R960@.<')O8V5D=7)E(&ES('=R:71T96X@.
M=&\@.#0H)1&EM(&]397)V97(-"@.E$:6T@.9G,-"@.T*"41I;2!F<T]U='!U=$9I
M;&4-"@.T*"41I;2!*;V(-"@.E$:6T@.2F]B4W1R:6YG#0H)1&EM($1R;W!%>&ES
M=&EN9TIO8E-T<FEN9PT*#0H))R!C<F5A=&4@.4U%,(%-E<G9E<B!O8FIE8W0-
M"@.E3970@.;U-E<G9E<B ]($-R96%T94]B:F5C="@.B4U%,1$U/+E-13%-E<G9E
M<B(I#0H-"@.DG(&%L=V%Y<R!U<V4@.82!T<G5S=&5D(&-O;FYE8W1I;VX@.86YD
M(&-O;FYE8W0-"@.EO4V5R=F5R+DQO9VEN4V5C=7)E(#T@.5')U90T*"6]397)V
M97(N0V]N;F5C="!397)V97).86UE("<@.=')U<W1E9"!C;VYN96-T:6]N#0H-
M"@.DG(%)O;&P@.=&AR;W5G:"!E86-H(&IO8B!A;F0@.9V5N97)A=&4@.=&AE(%-1
M3"P@.:&%C:R!O=70@.:&%R9&-O9&5D('-T=69F(&%N9"!A<'!E;F0@.=&\@.4V-R
M:7!T4W1R:6YG('9A<FEA8FQE#0H)1F]R($5A8V@.@.2F]B(&EN(&]397)V97(N
M2F]B4V5R=F5R+DIO8G,-"@.D))R!$<F]P('1H92!J;V(@.:68@.:70@.97AI<W1S
M+B!3:&]U;&0@.8F4@.82!$34\@.;W!T:6]N(&9O<B!T:&ES(#\_/S\_#0H)"41R
M;W!%>&ES=&EN9TIO8E-T<FEN9R ](")"14=)3B!44D%.4T%#5$E/3B(@.*R!V
M8D-23$8-"@.D)1')O<$5X:7-T:6YG2F]B4W1R:6YG(#T@.1')O<$5X:7-T:6YG
M2F]B4W1R:6YG("L@.(DE&($5825-44R H4T5,14-4("H@.1E)/32!M<V1B+F1B
M;RYS>7-J;V)S(%=(15)%(&YA;64@./2 G(B K($IO8BY.86UE("L@.(B<I(B K
M('9B0U),1@.T*"0E$<F]P17AI<W1I;F=*;V)3=')I;F<@./2!$<F]P17AI<W1I
M;F=*;V)3=')I;F<@.*R!V8E1!0B K(")%6$5#(&US9&(N9&)O+G-P7V1E;&5T
M95]J;V(@.0&IO8E]N86UE(#T@.)R(@.*R!*;V(N3F%M92 K("(G(B K('9B0U),
M1@.T*"0E$<F]P17AI<W1I;F=*;V)3=')I;F<@./2!$<F]P17AI<W1I;F=*;V)3
M=')I;F<@.*R B1T\B("L@.=F)#4DQ�H)"4IO8E-T<FEN9R ]($IO8BY38W)I
M<'0@.*%-13$1-3U-C<FEP=%]0<FEM87)Y3V)J96-T*0T*"0E*;V)3=')I;F<@.
M/2!215!,04-%*$IO8E-T<FEN9RP@.(F)E9VEN('1R86YS86-T:6]N(BP@.1')O
M<$5X:7-T:6YG2F]B4W1R:6YG*0T*"0D-"@.D))R!R97!L86-E('1H92!O=VYE
M<B!W:71H($Y53$P@.<V\@.=&AA="!I="!C<F5A=&5S(&IO8B!A< R!T:&4@.=7-E
M<B!E>&5C=71I;F<@.=&AE('-C<FEP= T*"0E*;V)3=')I;F<@./2!297!L86-E
M2&%R9$-O9&5D4F5F*$IO8E-T<FEN9RP@.(D!O=VYE<E]L;V=I;E]N86UE(BP@.
M3F5W3W=N97).86UE("D-"@.D))R!297!L86-E(%-E<G9E<FYA;64@.=7-E9"!I
M;B!S<%]!9&1?:F]B7U-E<G9E<B!T;R!B92!T:&4@.8W5R<F5N="!S97)V97(L
M(&]T:&5R=VES92!I="!W:6QL('5S92!T:&4@.<V5R=F5R;F%M92!S8 W)I<'0@.
M9G)O;2!I9B!L;V-A;"!I;G-T86YC90T*"0E*;V)3=')I;F<@./2!297!L86-E
M2&%R9$-O9&5D4F5F*$IO8E-T<FEN9RP@.(D!S97)V97)?;F%M92(L(") 0%-%
M4E9%4DY!344B*0T*#0H)"2<@.061D('1O('1H92!E>&ES=&EN9 R!3=')I;F<-
M"@.D)4V-R:7!T4W1R:6YG(#T@.4V-R:7!T4W1R:6YG("L@.=F)#4DQ&("L@.2F]B
M4W1R:6YG#0H)"5=38W)I<'0N16-H;R B5W)I=&EN9R!J;V(@.(B K($IO8BY.
M86UE("L@.(B!T;R B("L@.1FEL94YA;64-"@.E.97AT( T*#0H))R!.;W<@.9FEN
M86QL>2!W<FET92!T:&4@.9FEL90T*"7-E="!F<R ]($-R96%T94]B:F5C="@.B
M4V-R:7!T:6YG+D9I;&53>7-T96U/8FIE8W0B*0T*"5-E="!F<T]U='!U=$9I
M;&4@./2!F<RY#<F5A=&5497AT1FEL92A&:6QE3F%M92P@.5')U92D-"@.EF<T]U
M='!U=$9I;&4N5W)I=&5,:6YE(%-C<FEP=%-T<FEN9PT*"69S3W5T<'5T1FEL
M92Y#;&]S90T*16YD(%-U8@.T*#0HG(%-E87)C:"!F;W(@.82!3=')I;F<@.=&AA
M="!H87,@.0'-E<G9E<B ](&XG9&]O9F5R)R!T>7!E('-Y;G1A>"!A;F0@.<F5T
M=7)N(&XG9&]O9F5R)R L('=H97)E(%-E87)C:%-T<FEN9R!I<R! <V5R=F5R
M(&EN('1H:7,@.8V%S92X-"B<@.4V\@.=V4@.8V%N(')E<&QA8V4@.:&%R9&-O9&5D
M(')E9F5R;F-E<RX@.#0I&=6YC=&EO;B!297!L86-E2&%R9$-O9&5D4F5F("A3
M44Q3=')I;F<L(%-E87)C:%-T<FEN9RP@.4F5P;&%C95-T<FEN9RD-"@.E$:6T@.
M3F5W4W1R:6YG#0H)1&EM(%-E87)C:%-T<FEN9U!O<PT*"41I;2!396-O;F11
M=6]T95!O<PT*#0H)1&EM($QO9VEN3F%M90T*"41I;2!344Q3=')I; F=,96X-
M"@.T*"5-13%-T<FEN9TQE;B ]($QE;BA344Q3=')I;F<I#0H-"@.DG(&=E="!T
M:&4@.;&]C871I;VX@.;V8@.=&AE(&]W;F5R7VQO9VEN7VYA;64@.8VAA<@.T*"5-E
M87)C:%-T<FEN9U!O<R ]($EN4W1R*#$L(%-13%-T<FEN9RP@.4V5A<F-H4W1R
M:6YG+" P*0T*#0H))R!G970@.82!3=')I;F<@.+"!S=&%R=&EN9R!W:71H( $XG
M;&]G:6X@.;F%M92<-"@.E,;V=I;DYA;64@./2!-:60H4U%,4W1R:6YG+"!396%R
M8VA3=')I;F=0;W,@.*R!,14XH4V5A<F-H4W1R:6YG*2 K(#,L(%-13%-T<FEN
M9TQE;B M(%-E87)C:%-T<FEN9U!O<RD-"@.D-"@.E396-O;F11=6]T95!O<R ]
M($EN4W1R*#,L($QO9VEN3F%M92P@.(B<B+" P*0D)#0H)3&]G:6Y.86UE(#T@.
M36ED*$QO9VEN3F%M92P@.,2P@.4V5C;VYD475O=&50;W,I#0H)# 0H))R!74V-R
M:7!T+D5C:&\@.3&]G:6Y.86UE#0H)4F5P;&%C94AA<F1#;V1E9%)E9B ](%)E
M<&QA8V4H4U%,4W1R:6YG+"!,;V=I;DYA;64L(%)E<&QA8V53= ')I;F<I#0I%
6;F0@.1G5N8W1I;VX@.#0H-"@.T*#0H-"@.``
`
end
|||... or use the DTS Transfer Jobs task !
"Hassan" wrote:
> I know of a way to script all SQL Agent jobs using EM. But i want to script
> all my jobs on a daily basis and copy that script file to another server . I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
>
Subscribe to:
Posts (Atom)