Backups

I have created a backup schedule for all databases on a
SQL server. I have Created a Backup device for each db.
and a single stored procedure that is called with database
name, device & retain days passed as parameters. All
Devices are network locations. I didn't want these backups
to swallow the entire disk space so i set the retain days
to 14. After 2 weeks i hoped that each backup would have
been overwriten. Well that was my logic. 2 weeks are up
and the files created are still growing. After some
investigation it is apparent that the expiry dates have
been passed but the backups sets have not been
overwritten. So much for my plan. I have now re-read BOL
and realised that i have got my wires crossed, the entire
media is overwriten when the expiry dates of all backups
within have been reached. To me this is topsy turvy, why
would i wish to overwrite an entire backup file, maybe if
i had taken a back up of the backup then i would wish to
delete it. Have i yet again misunderstood BOL. What i want
to do is maintain a dynamic history of backups. I back up
my database to a device, this backup lasts for 2 weeks and
then is overwriten. so the actual physical file is never
deleted, only the contents within when the expiry date is
reached.
Help!!!!!!!Expiredays and retaindays are only there to not allow you to overwrite using
the INIT before a certain day. If you aren't using INIT or if you are using
NOINIT, it will always be append. And, it is all or nothing.
If you want generation handling, either use the Maint Wizard, a 3:rd party
like www.dbmaint.com or some TSQL programming to handle this (using more
than one backup device).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:a5ce01c40a8e$5c4c13e0$a601280a@.phx.gbl...
> I have created a backup schedule for all databases on a
> SQL server. I have Created a Backup device for each db.
> and a single stored procedure that is called with database
> name, device & retain days passed as parameters. All
> Devices are network locations. I didn't want these backups
> to swallow the entire disk space so i set the retain days
> to 14. After 2 weeks i hoped that each backup would have
> been overwriten. Well that was my logic. 2 weeks are up
> and the files created are still growing. After some
> investigation it is apparent that the expiry dates have
> been passed but the backups sets have not been
> overwritten. So much for my plan. I have now re-read BOL
> and realised that i have got my wires crossed, the entire
> media is overwriten when the expiry dates of all backups
> within have been reached. To me this is topsy turvy, why
> would i wish to overwrite an entire backup file, maybe if
> i had taken a back up of the backup then i would wish to
> delete it. Have i yet again misunderstood BOL. What i want
> to do is maintain a dynamic history of backups. I back up
> my database to a device, this backup lasts for 2 weeks and
> then is overwriten. so the actual physical file is never
> deleted, only the contents within when the expiry date is
> reached.
> Help!!!!!!!
>|||Thanks Tibor
Are there any sys SP's or XP's that can be used to edit
backup files? Maybe i could remove expired files..
I have created a stored procedure that looks at a backup
device and tells me which full, DIff and TL backups need
to be applied to restore to a specified point in time. It
looks as if this will nor work if i have to create new
devices..rats...
I pull my hair out some times with the illogical-ness of
SQL server

>--Original Message--
>Expiredays and retaindays are only there to not allow you
to overwrite using
>the INIT before a certain day. If you aren't using INIT
or if you are using
>NOINIT, it will always be append. And, it is all or
nothing.
>If you want generation handling, either use the Maint
Wizard, a 3:rd party
>like www.dbmaint.com or some TSQL programming to handle
this (using more
>than one backup device).
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"mat" <anonymous@.discussions.microsoft.com> wrote in
message
>news:a5ce01c40a8e$5c4c13e0$a601280a@.phx.gbl...
database
backups
days
entire
why
if
want
up
and
is
>
>.
>|||There are no tool with which you can remove selective backups inside a
backup file, I'm afraid.
One alternative is to do append, say over one day (assume db backup once per
day and log backup once per hour). Then after one day, you rename the Active
to give it a timestamp (so you have generations) and then do INIT to the
active one. This is how we did it in Db Maint up until the current version,
where we decided to not do append anymore.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:d7ca01c40a92$7eb09060$a101280a@.phx.gbl...
> Thanks Tibor
> Are there any sys SP's or XP's that can be used to edit
> backup files? Maybe i could remove expired files..
> I have created a stored procedure that looks at a backup
> device and tells me which full, DIff and TL backups need
> to be applied to restore to a specified point in time. It
> looks as if this will nor work if i have to create new
> devices..rats...
> I pull my hair out some times with the illogical-ness of
> SQL server
>
> to overwrite using
> or if you are using
> nothing.
> Wizard, a 3:rd party
> this (using more
> message
> database
> backups
> days
> entire
> why
> if
> want
> up
> and
> is|||Thanks tibor, thats great. Part of my backup script now
contains code to create a backup device every time it is
run that is named depending on a variable passed.
Alternating every week the physical file names change and
the retain days are set to 7. So as you advised i create a
file and add my backups. After a week i swich to a second
file and use this for a week. After another 7 days i
switch back to the original file that is now ready tbe
overwriten.
My SP that advises me of what backup files to aply now
works in pretty much the same way. It creates a device
based on the parameters and returns the backup history. it
then re-creates the device with the second file name and
apends this to the first run.
Thanks so much, you have been a great help..

>--Original Message--
>There are no tool with which you can remove selective
backups inside a
>backup file, I'm afraid.
>One alternative is to do append, say over one day (assume
db backup once per
>day and log backup once per hour). Then after one day,
you rename the Active
>to give it a timestamp (so you have generations) and then
do INIT to the
>active one. This is how we did it in Db Maint up until
the current version,
>where we decided to not do append anymore.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"mat" <anonymous@.discussions.microsoft.com> wrote in
message
>news:d7ca01c40a92$7eb09060$a101280a@.phx.gbl...
It
you
on a
db.
have
are up
have
BOL
backups
maybe
wish to
back
weeks
never
date
>
>.
>|||I'm glad I could help, mat. Just one thing: As far as I can see, retains
days doesn't really buy you anything. I only mention this so you don't read
anything into this parameter which isn't there... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:d55d01c40aad$fb535920$a501280a@.phx.gbl...
> Thanks tibor, thats great. Part of my backup script now
> contains code to create a backup device every time it is
> run that is named depending on a variable passed.
> Alternating every week the physical file names change and
> the retain days are set to 7. So as you advised i create a
> file and add my backups. After a week i swich to a second
> file and use this for a week. After another 7 days i
> switch back to the original file that is now ready tbe
> overwriten.
> My SP that advises me of what backup files to aply now
> works in pretty much the same way. It creates a device
> based on the parameters and returns the backup history. it
> then re-creates the device with the second file name and
> apends this to the first run.
> Thanks so much, you have been a great help..
>
> backups inside a
> db backup once per
> you rename the Active
> do INIT to the
> the current version,
> message
> It
> you
> on a
> db.
> have
> are up
> have
> BOL
> backups
> maybe
> wish to
> back
> weeks
> never
> date