Monday, March 19, 2012

Backup Database Maintenance Plan eats up too much disk space

I created a basic DB maintenance plan to routinely backup our database.
Every backup job that runs through this plan is created using a unique name,
putting the date into the back file name, as in
DBProduction_db_200401120000.bak.
The problem is this creates a new backup file each day - our backups our
faily large and after only a few days, the drive space is completely used
up.
What is the best way to solve this problem? Currently, we manually remove
the older backup files. Is there an automated way to have SQL delete files
older than a given number of days - for example, remove all files older than
3 days. When we do a log ship using a maintenance plan, it asks for this
information, but I haven't seen it when just running basic back ups and
transaction log back ups.
What about changing the default name to something like 'DailyDBBackup.bak'
so that it just overwrites previous backup everytime?> What is the best way to solve this problem? Currently, we manually remove
> the older backup files. Is there an automated way to have SQL delete
files
> older than a given number of days - for example, remove all files older
than
> 3 days.
Run through the maint wiz again and you will find just that option :-).
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dave Slinn" <dslinn@.gms.ca> wrote in message
news:OtxsPCc5DHA.564@.TK2MSFTNGP10.phx.gbl...
> I created a basic DB maintenance plan to routinely backup our database.
> Every backup job that runs through this plan is created using a unique
name,
> putting the date into the back file name, as in
> DBProduction_db_200401120000.bak.
> The problem is this creates a new backup file each day - our backups our
> faily large and after only a few days, the drive space is completely used
> up.
> What is the best way to solve this problem? Currently, we manually remove
> the older backup files. Is there an automated way to have SQL delete
files
> older than a given number of days - for example, remove all files older
than
> 3 days. When we do a log ship using a maintenance plan, it asks for this
> information, but I haven't seen it when just running basic back ups and
> transaction log back ups.
> What about changing the default name to something like 'DailyDBBackup.bak'
> so that it just overwrites previous backup everytime?
>
>|||D-oh! I feel so stupid...
Thanks Tibor!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eU$VRHc5DHA.360@.TK2MSFTNGP12.phx.gbl...
> > What is the best way to solve this problem? Currently, we manually
remove
> > the older backup files. Is there an automated way to have SQL delete
> files
> > older than a given number of days - for example, remove all files older
> than
> > 3 days.
> Run through the maint wiz again and you will find just that option :-).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Dave Slinn" <dslinn@.gms.ca> wrote in message
> news:OtxsPCc5DHA.564@.TK2MSFTNGP10.phx.gbl...
> > I created a basic DB maintenance plan to routinely backup our database.
> > Every backup job that runs through this plan is created using a unique
> name,
> > putting the date into the back file name, as in
> > DBProduction_db_200401120000.bak.
> >
> > The problem is this creates a new backup file each day - our backups our
> > faily large and after only a few days, the drive space is completely
used
> > up.
> >
> > What is the best way to solve this problem? Currently, we manually
remove
> > the older backup files. Is there an automated way to have SQL delete
> files
> > older than a given number of days - for example, remove all files older
> than
> > 3 days. When we do a log ship using a maintenance plan, it asks for
this
> > information, but I haven't seen it when just running basic back ups and
> > transaction log back ups.
> >
> > What about changing the default name to something like
'DailyDBBackup.bak'
> > so that it just overwrites previous backup everytime?
> >
> >
> >
>

No comments:

Post a Comment