Hi,

I want to schedule a backup of three databases on a daily basis.

I've written the code to run the 3 backups in TSQL and was wondering how
best to automate this procedure?

Should I put the code in an sproc and then schedule running that command
in the DTS or should I just add the TSQL into a DTS command?? Or is
there a better way of doing this???

I then prefer to use DTSRUNUI to generate the syntax and then schedule
this from the windows scheduler as opposed to the SQL SCheduler in EM
(is this the best way???)

Help would be appreciated

M3ckon

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

"m3ckon" <anonymous@devdex.com> wrote in message
news:40cdbb60$0$25520$c397aba@news.newsgroups.ws.. .
>
> Hi,
>
> I want to schedule a backup of three databases on a daily basis.
>
> I've written the code to run the 3 backups in TSQL and was wondering how
> best to automate this procedure?
>
> Should I put the code in an sproc and then schedule running that command
> in the DTS or should I just add the TSQL into a DTS command?? Or is
> there a better way of doing this???
>
> I then prefer to use DTSRUNUI to generate the syntax and then schedule
> this from the windows scheduler as opposed to the SQL SCheduler in EM
> (is this the best way???)
>
> Help would be appreciated
>
> M3ckon
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!

Putting the code in a procedure may be best, because it's simpler to manage,
and you don't get awkward problems working with EM or other tools which
allow only a very limited area for viewing code. If you're executing a DTS
package, then your geenral approach above seems OK, although the Windows
scheduler is rather limited compared to the MSSQL one, so unless there is a
very good reason not to, I would use the MSSQL scheduler.

Having said all that, using DTS as a backup mechanism is a little unusual,
unless your backups are part of a larger workflow. For simple backups,
maintenance plans are a convenient solution, although since they have some
limitations, you might have to write your own TSQL code sooner or later. But
scheduling a stored procedure in MSSQL is generally less complicated than
scheduling a DTS package, unless you need some extra functionality.

Simon

Hi

You can do it in serveral ways, but I would control it through a SQL Agent
job. If you run the backup

See sp_add_job, sp_add_jobschedule, and sp_add_jobstep
http://msdn.microsoft.com/library/de...asp?frame=true

If you want see a backup job programmed there is a schedule option at the
bottom of the backup wizard screen in Enterprise Manager.

John


"m3ckon" <anonymous@devdex.com> wrote in message
news:40cdbb60$0$25520$c397aba@news.newsgroups.ws.. .
>
> Hi,
>
> I want to schedule a backup of three databases on a daily basis.
>
> I've written the code to run the 3 backups in TSQL and was wondering how
> best to automate this procedure?
>
> Should I put the code in an sproc and then schedule running that command
> in the DTS or should I just add the TSQL into a DTS command?? Or is
> there a better way of doing this???
>
> I then prefer to use DTSRUNUI to generate the syntax and then schedule
> this from the windows scheduler as opposed to the SQL SCheduler in EM
> (is this the best way???)
>
> Help would be appreciated
>
> M3ckon
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!