sql agent JOB to Backup Database and Maintain Backup Files

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Prabhat - 30 Jun 2008 20:25 GMT

Hi friends,

How can I create ONE SQL Agent JOB That will 2 activities: (Using SQL
2005)

1) Backup a specified Database (Full Backup) and store the backup file
in a Specified FOLDER.
2) Manage the Backup Files in the FOLDER, ie. Delete the files that
are 5 week OLD.

I want to schedule the JOB to run every Sunday.

I am able to do this using the SQL Server Maintenance Plan and that
creates a Job. But that JOB refers to the maintenance plan package.
Which I dont want to be depended on.

Please suggest.

Reply to this Message

TheRealRobbie - 01 Jul 2008 08:18 GMT

Hi Prabhat,

Can you explain some more?
Why are you hesitant to use the SSIS maintenance plan?

It should be possible to achieve similar functionality using t-sql backup
commands and using "XP_" stored procedures, but why would you want that??

Regards, Rob

> Hi friends,
>
[quoted text clipped - 13 lines]
>
> Please suggest.

Reply to this Message

Prabhat - 01 Jul 2008 19:04 GMT

On Jul 1, 12:18 pm, TheRealRobbie
<TheRealRob...@discussions.microsoft.com> wrote:

> Hi Prabhat,
>
[quoted text clipped - 25 lines]
>
> - Show quoted text -

I want the TASKs (both backup and delete old files) to be in ONE place
in a Agent JOB so that I can schedule those.... Also If the TASK will
be in SQL query within a JOB (without reference to package) that will
be easy to manage too (instead of managin the package and JOB, manage
ONLY JOB which has all). Hope I am making it clear :)

Thanks

Reply to this Message

John Bell - 02 Jul 2008 07:33 GMT

> Hi friends,
>
[quoted text clipped - 13 lines]
>
> Please suggest.

Hi

You can quite easily put the backup command in a job step, if you want to
tailor it to include a date/time in the file name you can use the EXEC
command and create the command by concatentating a string.

For the file deletion you can use the COM filesystem object and use sp_OA*
procedures.

John

Reply to this Message