Question on db maintenance scripts

Relatively new to SQL Server, running both 2000 and 2005.
I have to set up database maintenance scripts, db and log backups,
optimizations etc in batch files so that they can be run by an external
scheduler.
When I produce a script from the SQL Server database maintenance
wizards and save it off in a bat file, the script will not work when
run manually, probably due to the difference in syntax.
I have some examples of backup scripts using sqlmaint.exe, but can't
find anything on how to script optimization/reorg jobs so that they can
be run by an external scheduler.
Can anybody point me to any docs or guidance on this?
Thanks in advance.
GerryI suggest you skip the scripting part and read up on sqlmaint.exe. So instead of calling
xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe directly. this gives you much
better control and options.
2005 is another story, where a maint plan is an SSIS job, but you can just let the Wizard and
designer create package for you and schedule a job using DTEXEC.EXE to execute that package.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164723926.064627.125300@.l12g2000cwl.googlegroups.com...
> Relatively new to SQL Server, running both 2000 and 2005.
> I have to set up database maintenance scripts, db and log backups,
> optimizations etc in batch files so that they can be run by an external
> scheduler.
> When I produce a script from the SQL Server database maintenance
> wizards and save it off in a bat file, the script will not work when
> run manually, probably due to the difference in syntax.
> I have some examples of backup scripts using sqlmaint.exe, but can't
> find anything on how to script optimization/reorg jobs so that they can
> be run by an external scheduler.
> Can anybody point me to any docs or guidance on this?
> Thanks in advance.
> Gerry
>|||Tibor:
Thanks for the advice. I will do that.
On 2005 we have a scheduler (Control M) running business processing
jobs outside of SQL Server and I will have to schedule jobs that are
dependent on these jobs finishing.
How would you approach a db maintenance job that was job dependent?
Tibor Karaszi wrote:
> I suggest you skip the scripting part and read up on sqlmaint.exe. So instead of calling
> xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe directly. this gives you much
> better control and options.
> 2005 is another story, where a maint plan is an SSIS job, but you can just let the Wizard and
> designer create package for you and schedule a job using DTEXEC.EXE to execute that package.
>|||> How would you approach a db maintenance job that was job dependent?
Not sure I understand your question, as Control M apparently already has that feature. Can you
elaborate?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164735316.116658.270460@.j44g2000cwa.googlegroups.com...
> Tibor:
> Thanks for the advice. I will do that.
> On 2005 we have a scheduler (Control M) running business processing
> jobs outside of SQL Server and I will have to schedule jobs that are
> dependent on these jobs finishing.
> How would you approach a db maintenance job that was job dependent?
>
> Tibor Karaszi wrote:
>> I suggest you skip the scripting part and read up on sqlmaint.exe. So instead of calling
>> xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe directly. this gives you much
>> better control and options.
>> 2005 is another story, where a maint plan is an SSIS job, but you can just let the Wizard and
>> designer create package for you and schedule a job using DTEXEC.EXE to execute that package.
>>
>|||Tibor:
I have to empty certain tables every evening. and that emptying task is
dependent on another job finishing.
I'm thinking an sql statement...but with something like this is
sqlmaint.exe used or should I be looking at osql utility or something
else.
Thanks in advance
Gerry
Tibor Karaszi wrote:
> > How would you approach a db maintenance job that was job dependent?
>
> Not sure I understand your question, as Control M apparently already has that feature. Can you
> elaborate?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DataPro" <datapro01@.yahoo.com> wrote in message
> news:1164735316.116658.270460@.j44g2000cwa.googlegroups.com...
> > Tibor:
> >
> > Thanks for the advice. I will do that.
> >
> > On 2005 we have a scheduler (Control M) running business processing
> > jobs outside of SQL Server and I will have to schedule jobs that are
> > dependent on these jobs finishing.
> >
> > How would you approach a db maintenance job that was job dependent?
> >
> >
> > Tibor Karaszi wrote:
> >> I suggest you skip the scripting part and read up on sqlmaint.exe. So instead of calling
> >> xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe directly. this gives you much
> >> better control and options.
> >>
> >> 2005 is another story, where a maint plan is an SSIS job, but you can just let the Wizard and
> >> designer create package for you and schedule a job using DTEXEC.EXE to execute that package.
> >>
> >>
> >|||Yes, you can use OSQL.EXE with either an input file or specify the TUNCATE TABLE or DELETE command
using the /Q switch for OSQL.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164801015.266092.235400@.l12g2000cwl.googlegroups.com...
> Tibor:
>
> I have to empty certain tables every evening. and that emptying task is
> dependent on another job finishing.
> I'm thinking an sql statement...but with something like this is
> sqlmaint.exe used or should I be looking at osql utility or something
> else.
> Thanks in advance
> Gerry
>
> Tibor Karaszi wrote:
>> > How would you approach a db maintenance job that was job dependent?
>>
>> Not sure I understand your question, as Control M apparently already has that feature. Can you
>> elaborate?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "DataPro" <datapro01@.yahoo.com> wrote in message
>> news:1164735316.116658.270460@.j44g2000cwa.googlegroups.com...
>> > Tibor:
>> >
>> > Thanks for the advice. I will do that.
>> >
>> > On 2005 we have a scheduler (Control M) running business processing
>> > jobs outside of SQL Server and I will have to schedule jobs that are
>> > dependent on these jobs finishing.
>> >
>> > How would you approach a db maintenance job that was job dependent?
>> >
>> >
>> > Tibor Karaszi wrote:
>> >> I suggest you skip the scripting part and read up on sqlmaint.exe. So instead of calling
>> >> xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe directly. this gives you
>> >> much
>> >> better control and options.
>> >>
>> >> 2005 is another story, where a maint plan is an SSIS job, but you can just let the Wizard and
>> >> designer create package for you and schedule a job using DTEXEC.EXE to execute that package.
>> >>
>> >>
>> >
>|||Tibor Karaszi wrote:
> Yes, you can use OSQL.EXE with either an input file or specify the TUNCATE TABLE or DELETE command
> using the /Q switch for OSQL.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
Thanks much