Can SQL 2005 Backup to a different file name each night?

In SQL 2000, I could use the DB Maint Wizard to create a backup that created
a new backup file each night.  This basic feature does not appear to be an
option in SQL 2005.  Is there some workaround?  Anyone know if this will be
part of SP01?
I have hundreds of systems that have 4 5GB databases where we do full
backups every night.  I do NOT want to append the previous nights backup to
the existing file, and I do NOT want to overwrite last nights backup because
I don't want to be without a backup if tonight's backup fails.

All suggestions are welcome, but I need a very simple way to set this up on
hundreds of systems by non-computer literate people.

Rob Kraft

Never mind group.  I cannot recreate the problem I had.  Originally in
testing, all of the backups were appending to the same date/time stamped
filename.  I set up a new plan and it seems to work as desired - creating a
new file each time the backup runs.  I have to manually add a cleanup task
to the DB Maint Plan created by the wizard to delete old DB files, but I've
got that figured out too.

Rob Kraft

"Rob Kraft" <robkraft@msn.com> wrote in message
 news:%23nhHf4%23VGHA
.3284@TK2MSFTNGP09.phx.gbl...
> In SQL 2000, I could use the DB Maint Wizard to create a backup that
> created a new backup file each night.  This basic feature does not appear
> to be an option in SQL 2005.  Is there some workaround?  Anyone know if
> this will be part of SP01?
> I have hundreds of systems that have 4 5GB databases where we do full
> backups every night.  I do NOT want to append the previous nights backup
> to the existing file, and I do NOT want to overwrite last nights backup
> because I don't want to be without a backup if tonight's backup fails.
>
> All suggestions are welcome, but I need a very simple way to set this up
> on hundreds of systems by non-computer literate people.
>
> Rob Kraft
>

Rob,

How do you add the delete of the oldest file? I cannot seem to manage this
so far.

Thanks

Chris Wood

"Rob Kraft" <robkraft@msn.com> wrote in message
news:e4R3FI$VGHA.4328@TK2MSFTNGP12.phx.gbl...
> Never mind group.  I cannot recreate the problem I had.  Originally in
> testing, all of the backups were appending to the same date/time stamped
> filename.  I set up a new plan and it seems to work as desired - creating
> a new file each time the backup runs.  I have to manually add a cleanup
> task to the DB Maint Plan created by the wizard to delete old DB files,
> but I've got that figured out too.
>
> Rob Kraft
>
> "Rob Kraft" <robkraft@msn.com> wrote in message
>  news:%23nhHf4%23VGHA
.3284@TK2MSFTNGP09.phx.gbl... 
>
>

Chris, I could not do this using the Maintenance Plan Wizard. Instead, after
creating the plan, right-click on the plan and select Modify.  Then, on the
left hand side (probably) -drag a "Maintenance Cleanup Task" from the
Toolbox window into your plan (make sure you do NOT grab the "History
Cleanup Task").  Right-click on the "Maintenance Cleanup Task" and select
Edit.  They you can specify to delete files in a specific folder that are
older than a n Weeks (or n Days).  (and you can specify to delete files with
just a specific extension such as bak).  Close that window then click on the
"Maintenance Cleanup Task" in the maintence plan and click and drag the
green arrow that appears down to the "Back Up Database Task" you just
created to cause the "Back Up Database Task" to execute after the file
delete.

Technically, I don't see a way using these tools to delete "the oldest
file"; but you can delete "all files over n days old".

Rob Kraft


"Chris Wood" < anonymous@discussion
s.microsoft.com> wrote in message
news:exAKTW$VGHA.5076@TK2MSFTNGP14.phx.gbl...
> Rob,
>
> How do you add the delete of the oldest file? I cannot seem to manage this
> so far.
>
> Thanks
>
> Chris Wood
>
> "Rob Kraft" <robkraft@msn.com> wrote in message
> news:e4R3FI$VGHA.4328@TK2MSFTNGP12.phx.gbl... 
>
>

Thank you Rob.

Works like a treat. Another 2005 nuance out of the way.

Chris

"Rob Kraft" <robkraft@msn.com> wrote in message
news:e7ziF2XWGHA.1564@TK2MSFTNGP03.phx.gbl...
> Chris, I could not do this using the Maintenance Plan Wizard. Instead,
> after creating the plan, right-click on the plan and select Modify.  Then,
> on the left hand side (probably) -drag a "Maintenance Cleanup Task" from
> the Toolbox window into your plan (make sure you do NOT grab the "History
> Cleanup Task").  Right-click on the "Maintenance Cleanup Task" and select
> Edit.  They you can specify to delete files in a specific folder that are
> older than a n Weeks (or n Days).  (and you can specify to delete files
> with just a specific extension such as bak).  Close that window then click
> on the "Maintenance Cleanup Task" in the maintence plan and click and drag
> the green arrow that appears down to the "Back Up Database Task" you just
> created to cause the "Back Up Database Task" to execute after the file
> delete.
>
> Technically, I don't see a way using these tools to delete "the oldest
> file"; but you can delete "all files over n days old".
>
> Rob Kraft
>
>
> "Chris Wood" < anonymous@discussion
s.microsoft.com> wrote in message
> news:exAKTW$VGHA.5076@TK2MSFTNGP14.phx.gbl... 
>
>