SCheduled Jobs

Hi All,
I have many scheduled jobs running that creates a centralized database from
20 different databases in 20 different locations.
The jobs say they were successful, but the information is not coming over
all the time. If I manually run the job in EM, it works fine. Some of the
jobs call Stored Procedures and others are DTS packages. The jobs that call
SP's have between 3 and 20 steps in them, I check each job and all steps say
they were successful.
The DTS packages delete from the destination table and insert all records
from the Source. The DTS can have up to 4 tables being transferred.
TIA,
JoeHi
It is hard to say from your description what is wrong. Adding extra debug to
your processes may help and making sure that you are checking the error
status correctly in the stored procedures may show some unexpected events.
You may also want to profile the process to view what is really being
executed. Loading the data into separate staging table will allow you to
analyse the data that has been imported.
John
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:3817E52C-788E-4AEA-96A6-11E701AA44B3@.microsoft.com...
> Hi All,
> I have many scheduled jobs running that creates a centralized database
> from
> 20 different databases in 20 different locations.
> The jobs say they were successful, but the information is not coming over
> all the time. If I manually run the job in EM, it works fine. Some of
> the
> jobs call Stored Procedures and others are DTS packages. The jobs that
> call
> SP's have between 3 and 20 steps in them, I check each job and all steps
> say
> they were successful.
> The DTS packages delete from the destination table and insert all records
> from the Source. The DTS can have up to 4 tables being transferred.
> TIA,
> Joe
>

Scheduled Backup for SQL Database

Hi All,
Very new user to SQL admin. I need to backup a sql
database, when I run it manually it runs fine and creates
a backup without any issue. However when I try to
schedule the job it does not save the schedule or run the
backup.
I am using Windows 2000 Server and SQL2000. Both are
fully patched and have latest service packs.
Am I doing something wrong or is this a bug. The SQL
agent is running on the local machine, and the job
appears in the backup list but always fails. I am trying
to write to another data disk on the server with the
routine to overwrite the file everytime.
Any help would be greatHi
Make sure that the agent is running on a domain account that has access to
the other server. Are you using '\\machinename\sharename\file.bak' (UNC path)
as your naming standard for the backup file?
You can not make a maintenance plan that uses a UNC share, so you have to
code it manually and setup a job (see BOL for BACKUP DATABASE).
Cheers
Mike
"Chris" wrote:
> Hi All,
> Very new user to SQL admin. I need to backup a sql
> database, when I run it manually it runs fine and creates
> a backup without any issue. However when I try to
> schedule the job it does not save the schedule or run the
> backup.
> I am using Windows 2000 Server and SQL2000. Both are
> fully patched and have latest service packs.
> Am I doing something wrong or is this a bug. The SQL
> agent is running on the local machine, and the job
> appears in the backup list but always fails. I am trying
> to write to another data disk on the server with the
> routine to overwrite the file everytime.
> Any help would be great
>|||Hi,
The backup is just running to another drive on the same
machine. My nightly backup routine goes and copies that
directory to the central server for nightly backup
>--Original Message--
>Hi
>Make sure that the agent is running on a domain account
that has access to
>the other server. Are you
using '\\machinename\sharename\file.bak' (UNC path)
>as your naming standard for the backup file?
>You can not make a maintenance plan that uses a UNC
share, so you have to
>code it manually and setup a job (see BOL for BACKUP
DATABASE).
>Cheers
>Mike
>"Chris" wrote:
>> Hi All,
>> Very new user to SQL admin. I need to backup a sql
>> database, when I run it manually it runs fine and
creates
>> a backup without any issue. However when I try to
>> schedule the job it does not save the schedule or run
the
>> backup.
>> I am using Windows 2000 Server and SQL2000. Both are
>> fully patched and have latest service packs.
>> Am I doing something wrong or is this a bug. The SQL
>> agent is running on the local machine, and the job
>> appears in the backup list but always fails. I am
trying
>> to write to another data disk on the server with the
>> routine to overwrite the file everytime.
>> Any help would be great
>.
>|||Is the SQL Server Agent service startup account the same account as the SQL
Server service?
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
<anonymous@.discussions.microsoft.com> wrote in message
news:45ec01c4a634$9c1441e0$a301280a@.phx.gbl...
> Hi,
> The backup is just running to another drive on the same
> machine. My nightly backup routine goes and copies that
> directory to the central server for nightly backup
> >--Original Message--
> >Hi
> >
> >Make sure that the agent is running on a domain account
> that has access to
> >the other server. Are you
> using '\\machinename\sharename\file.bak' (UNC path)
> >as your naming standard for the backup file?
> >
> >You can not make a maintenance plan that uses a UNC
> share, so you have to
> >code it manually and setup a job (see BOL for BACKUP
> DATABASE).
> >
> >Cheers
> >Mike
> >
> >"Chris" wrote:
> >
> >> Hi All,
> >>
> >> Very new user to SQL admin. I need to backup a sql
> >> database, when I run it manually it runs fine and
> creates
> >> a backup without any issue. However when I try to
> >> schedule the job it does not save the schedule or run
> the
> >> backup.
> >>
> >> I am using Windows 2000 Server and SQL2000. Both are
> >> fully patched and have latest service packs.
> >>
> >> Am I doing something wrong or is this a bug. The SQL
> >> agent is running on the local machine, and the job
> >> appears in the backup list but always fails. I am
> trying
> >> to write to another data disk on the server with the
> >> routine to overwrite the file everytime.
> >>
> >> Any help would be great
> >>
> >.
> >|||Yes they are both started by local administrator and the
local administrator owns the backup job as well
>--Original Message--
>Is the SQL Server Agent service startup account the same
account as the SQL
>Server service?
>--
>Peter Yeoh
>http://www.yohz.com
>Need smaller SQL2K backup files? Use MiniSQLBackup
Lite, free!
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:45ec01c4a634$9c1441e0$a301280a@.phx.gbl...
>> Hi,
>> The backup is just running to another drive on the same
>> machine. My nightly backup routine goes and copies that
>> directory to the central server for nightly backup
>> >--Original Message--
>> >Hi
>> >
>> >Make sure that the agent is running on a domain
account
>> that has access to
>> >the other server. Are you
>> using '\\machinename\sharename\file.bak' (UNC path)
>> >as your naming standard for the backup file?
>> >
>> >You can not make a maintenance plan that uses a UNC
>> share, so you have to
>> >code it manually and setup a job (see BOL for BACKUP
>> DATABASE).
>> >
>> >Cheers
>> >Mike
>> >
>> >"Chris" wrote:
>> >
>> >> Hi All,
>> >>
>> >> Very new user to SQL admin. I need to backup a sql
>> >> database, when I run it manually it runs fine and
>> creates
>> >> a backup without any issue. However when I try to
>> >> schedule the job it does not save the schedule or
run
>> the
>> >> backup.
>> >>
>> >> I am using Windows 2000 Server and SQL2000. Both are
>> >> fully patched and have latest service packs.
>> >>
>> >> Am I doing something wrong or is this a bug. The SQL
>> >> agent is running on the local machine, and the job
>> >> appears in the backup list but always fails. I am
>> trying
>> >> to write to another data disk on the server with the
>> >> routine to overwrite the file everytime.
>> >>
>> >> Any help would be great
>> >>
>> >.
>> >
>
>.
>|||Any error messages for the job? Look in the SQL Server log too, usually
found in the \Log subfolder of SQL Server.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
<anonymous@.discussions.microsoft.com> wrote in message
news:42e701c4a6d0$7ccaf260$a501280a@.phx.gbl...
> Yes they are both started by local administrator and the
> local administrator owns the backup job as well
> >--Original Message--
> >Is the SQL Server Agent service startup account the same
> account as the SQL
> >Server service?
> >
> >--
> >Peter Yeoh
> >http://www.yohz.com
> >Need smaller SQL2K backup files? Use MiniSQLBackup
> Lite, free!
> >
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> >news:45ec01c4a634$9c1441e0$a301280a@.phx.gbl...
> >> Hi,
> >>
> >> The backup is just running to another drive on the same
> >> machine. My nightly backup routine goes and copies that
> >> directory to the central server for nightly backup
> >> >--Original Message--
> >> >Hi
> >> >
> >> >Make sure that the agent is running on a domain
> account
> >> that has access to
> >> >the other server. Are you
> >> using '\\machinename\sharename\file.bak' (UNC path)
> >> >as your naming standard for the backup file?
> >> >
> >> >You can not make a maintenance plan that uses a UNC
> >> share, so you have to
> >> >code it manually and setup a job (see BOL for BACKUP
> >> DATABASE).
> >> >
> >> >Cheers
> >> >Mike
> >> >
> >> >"Chris" wrote:
> >> >
> >> >> Hi All,
> >> >>
> >> >> Very new user to SQL admin. I need to backup a sql
> >> >> database, when I run it manually it runs fine and
> >> creates
> >> >> a backup without any issue. However when I try to
> >> >> schedule the job it does not save the schedule or
> run
> >> the
> >> >> backup.
> >> >>
> >> >> I am using Windows 2000 Server and SQL2000. Both are
> >> >> fully patched and have latest service packs.
> >> >>
> >> >> Am I doing something wrong or is this a bug. The SQL
> >> >> agent is running on the local machine, and the job
> >> >> appears in the backup list but always fails. I am
> >> trying
> >> >> to write to another data disk on the server with the
> >> >> routine to overwrite the file everytime.
> >> >>
> >> >> Any help would be great
> >> >>
> >> >.
> >> >
> >
> >
> >.
> >|||Ok have nearly sorted it out. I Hope!
I have managed to get the job to run by doing a database
maintainance job going. This starts automatically and
runs when scheduled. Only problem is when the SQL server
shuts down it stops the agent. When the server restarts
it doesn't restart the agent. I have set the propeties on
the SQL Agent to restart after 10 minutes and the service
does restart ok.
For some reason the SQL agent under server management
does not restart so the job fails. As soon as I right
click and say start the job runs ok. So my question is
Why doesn't this restart when the service does?
Chris
>--Original Message--
>Any error messages for the job? Look in the SQL Server
log too, usually
>found in the \Log subfolder of SQL Server.
>--
>Peter Yeoh
>http://www.yohz.com
>Need smaller SQL2K backup files? Use MiniSQLBackup
Lite, free!
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:42e701c4a6d0$7ccaf260$a501280a@.phx.gbl...
>> Yes they are both started by local administrator and
the
>> local administrator owns the backup job as well
>> >--Original Message--
>> >Is the SQL Server Agent service startup account the
same
>> account as the SQL
>> >Server service?
>> >
>> >--
>> >Peter Yeoh
>> >http://www.yohz.com
>> >Need smaller SQL2K backup files? Use MiniSQLBackup
>> Lite, free!
>> >
>> >
>> ><anonymous@.discussions.microsoft.com> wrote in message
>> >news:45ec01c4a634$9c1441e0$a301280a@.phx.gbl...
>> >> Hi,
>> >>
>> >> The backup is just running to another drive on the
same
>> >> machine. My nightly backup routine goes and copies
that
>> >> directory to the central server for nightly backup
>> >> >--Original Message--
>> >> >Hi
>> >> >
>> >> >Make sure that the agent is running on a domain
>> account
>> >> that has access to
>> >> >the other server. Are you
>> >> using '\\machinename\sharename\file.bak' (UNC path)
>> >> >as your naming standard for the backup file?
>> >> >
>> >> >You can not make a maintenance plan that uses a UNC
>> >> share, so you have to
>> >> >code it manually and setup a job (see BOL for
BACKUP
>> >> DATABASE).
>> >> >
>> >> >Cheers
>> >> >Mike
>> >> >
>> >> >"Chris" wrote:
>> >> >
>> >> >> Hi All,
>> >> >>
>> >> >> Very new user to SQL admin. I need to backup a
sql
>> >> >> database, when I run it manually it runs fine and
>> >> creates
>> >> >> a backup without any issue. However when I try to
>> >> >> schedule the job it does not save the schedule or
>> run
>> >> the
>> >> >> backup.
>> >> >>
>> >> >> I am using Windows 2000 Server and SQL2000. Both
are
>> >> >> fully patched and have latest service packs.
>> >> >>
>> >> >> Am I doing something wrong or is this a bug. The
SQL
>> >> >> agent is running on the local machine, and the
job
>> >> >> appears in the backup list but always fails. I am
>> >> trying
>> >> >> to write to another data disk on the server with
the
>> >> >> routine to overwrite the file everytime.
>> >> >>
>> >> >> Any help would be great
>> >> >>
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>|||>> I have set the propeties on the SQL Agent to restart after 10 minutes
and the service does restart ok.
>> For some reason the SQL agent under server management does not restart
so the job fails.
I do not quite understand the above two (conflicting) statements. I guess
the SQL Agent service is restarting, but the job is not running. Is it
because the job is not scheduled to run at that time?
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
<anonymous@.discussions.microsoft.com> wrote in message
news:0b2801c4a6f6$21d26a20$a301280a@.phx.gbl...
> Ok have nearly sorted it out. I Hope!
> I have managed to get the job to run by doing a database
> maintainance job going. This starts automatically and
> runs when scheduled. Only problem is when the SQL server
> shuts down it stops the agent. When the server restarts
> it doesn't restart the agent. I have set the propeties on
> the SQL Agent to restart after 10 minutes and the service
> does restart ok.
> For some reason the SQL agent under server management
> does not restart so the job fails. As soon as I right
> click and say start the job runs ok. So my question is
> Why doesn't this restart when the service does?
> Chris
> >--Original Message--
> >Any error messages for the job? Look in the SQL Server
> log too, usually
> >found in the \Log subfolder of SQL Server.
> >
> >--
> >Peter Yeoh
> >http://www.yohz.com
> >Need smaller SQL2K backup files? Use MiniSQLBackup
> Lite, free!
> >
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> >news:42e701c4a6d0$7ccaf260$a501280a@.phx.gbl...
> >> Yes they are both started by local administrator and
> the
> >> local administrator owns the backup job as well
> >> >--Original Message--
> >> >Is the SQL Server Agent service startup account the
> same
> >> account as the SQL
> >> >Server service?
> >> >
> >> >--
> >> >Peter Yeoh
> >> >http://www.yohz.com
> >> >Need smaller SQL2K backup files? Use MiniSQLBackup
> >> Lite, free!
> >> >
> >> >
> >> ><anonymous@.discussions.microsoft.com> wrote in message
> >> >news:45ec01c4a634$9c1441e0$a301280a@.phx.gbl...
> >> >> Hi,
> >> >>
> >> >> The backup is just running to another drive on the
> same
> >> >> machine. My nightly backup routine goes and copies
> that
> >> >> directory to the central server for nightly backup
> >> >> >--Original Message--
> >> >> >Hi
> >> >> >
> >> >> >Make sure that the agent is running on a domain
> >> account
> >> >> that has access to
> >> >> >the other server. Are you
> >> >> using '\\machinename\sharename\file.bak' (UNC path)
> >> >> >as your naming standard for the backup file?
> >> >> >
> >> >> >You can not make a maintenance plan that uses a UNC
> >> >> share, so you have to
> >> >> >code it manually and setup a job (see BOL for
> BACKUP
> >> >> DATABASE).
> >> >> >
> >> >> >Cheers
> >> >> >Mike
> >> >> >
> >> >> >"Chris" wrote:
> >> >> >
> >> >> >> Hi All,
> >> >> >>
> >> >> >> Very new user to SQL admin. I need to backup a
> sql
> >> >> >> database, when I run it manually it runs fine and
> >> >> creates
> >> >> >> a backup without any issue. However when I try to
> >> >> >> schedule the job it does not save the schedule or
> >> run
> >> >> the
> >> >> >> backup.
> >> >> >>
> >> >> >> I am using Windows 2000 Server and SQL2000. Both
> are
> >> >> >> fully patched and have latest service packs.
> >> >> >>
> >> >> >> Am I doing something wrong or is this a bug. The
> SQL
> >> >> >> agent is running on the local machine, and the
> job
> >> >> >> appears in the backup list but always fails. I am
> >> >> trying
> >> >> >> to write to another data disk on the server with
> the
> >> >> >> routine to overwrite the file everytime.
> >> >> >>
> >> >> >> Any help would be great
> >> >> >>
> >> >> >.
> >> >> >
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

Schedule a T-SQL Script

I have a T-SQL script that creates a SQL Backup using Idera's SQLSafe.
It works great in QA, but I now wish to schedule the backup, and I am
not sure the best approach.
The script contains cleartext accounts and passwords, so I would like
to avoid storing it on a drive.
I thought I could create a stored procedure, but I dont know how I
could then schedule it.
Any thoughts?
TIA,
RobDOH, tried right clicking on the "Jobs" item under Management in EM, I
didnt know I could add jobs on the fly like that!
I just pasted my SQL and scheduled the job.
Simple
:)