Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQLDiva
Starting Member
26 Posts |
 Posted - 2002-11-04 : 12:03:54
|
| I'm hoping someone can help me with a task that's taking much too long to complete.I want to, using Transact-SQL, script our SQL Server jobs, essentially creating a backup. I know how to do this using Enterprise Manager, but can't seem to find a way to do it in Query Analyzer. We are in a stage right now of constant development, and adding new client databases constantly. Thus, jobs are being added and changed constantly. My main reason to do this is our backups are done offsite using Veritas, but are done by our Systems Administrator, NOT our database group. I'd like to have control over our jobs internally within our database group.Any Help?Thanks,Ann |
|
|
royv
Constraint Violating Yak Guru
455 Posts |
Posted - 2002-11-04 : 12:23:20
|
| If you want to use TSQL, you will have to translate the job tables located in the msdb database. They would include sysjobhistory, sysjobs, sysjobschedules, sysjobservers, sysjobsteps. There might be more tables to consider I'm not sure, and you have to be very careful otherwise you risk corruption of your SQL Server. In general, this is not a recommended approach.***************************************Death must absolutely come to enemies of the code! |
 |
|
|
SQLDiva
Starting Member
26 Posts |
Posted - 2002-11-04 : 12:30:47
|
| Yeah, I figured this wasn't the 'recommended' approach. I was hoping for one of those undocumented extended procedures or something! The politics of our network administration make it impossible for me to control these any other way unless I manually script them out (which is much preferrable to corrupting the database. I'll keep trying. |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-11-04 : 12:35:55
|
| Could you just backup the MSDB? write a simple code that just backups that database?-----------------------SQL isn't just a hobby, It's an addiction |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-04 : 12:58:35
|
| You can do this using the SQL-DMO object library. Jobs have their own object model, and there is a Script method for jobs, job steps, and schedule objects. You can invoke SQL-DMO COM objects using the sp_OA system procedures. There are some SQL-DMO examples on this site on scripting stuff:http://www.sqlteam.com/item.asp?ItemID=11123Also search for "SQL-DMO" on SQL Team, and, naturally, check Books Online for more details. |
|
|
|
royv
Constraint Violating Yak Guru
455 Posts |
Posted - 2002-11-04 : 13:52:20
|
| Well I feel stupid. I should have definetly known to suggest the backup of msdb. Did not know that SQLDMO could work in this situation, that would be a pretty cool option to try. SQLDiva you should definetly attempt the other method's that have been suggested***************************************Death must absolutely come to enemies of the code!Edited by - royv on 11/04/2002 13:53:27 |
|
|
|
SQLDiva
Starting Member
26 Posts |
Posted - 2002-11-04 : 13:55:41
|
| The MSDB is being backed up offsite but we need to have this information onsite. I'm going to start looking into the DMO situation. Thank you to everyone for your suggestions |
|
|
|
|
|
|
|
|
Backing up jobs