Backup log, no automatic deletion of expired logs?

Hi there,
I use the following scenario-
Every day at 00:00 I backup the database:
BACKUP DATABASE c5win300sql TO c5win300sql_dat WITH FORMAT
Every half hour from 06:00 to 23:00 I backup the log:
declare @.expDate datetime
select @.expDate = dateadd(day,1,getdate())
BACKUP LOG c5win300sql TO c5win300sql_log WITH EXPIREDATE = @.expdate
From the above, you can see that I set a expiration date, RESTORE HEADERONLY
FROM c5win300sql_log confirms, that it works correctly.
However, the c5win300sql_log backup continues to grow and the expired logs d
oes
not get "deleted" from the file. How can I rectify this issue "properly"?
I doubt, therefore I might be.All that EXPIREDATE and RETAINDAYS does it that they prohibit you from overw
riting (using INIT) the
backup file before the desired date and time. It doesn't make any automatic
deletion of old backups.
In fact, you cannot delete only some backups from a backup file. It is all o
r nothing.
Maint Wiz has functionality to create new backup files with names based on d
ate and time (among
other). And Maint wiz can delete files other than x days. You can also write
similar code yourself,
but that takes some coding to do.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kim Noer" <kn@.nospam.dk> wrote in message news:uiiTdKb%23FHA.1032@.TK2MSFTNGP09.phx.gbl...[v
bcol=seagreen]
> Hi there,
> I use the following scenario-
> Every day at 00:00 I backup the database:
> BACKUP DATABASE c5win300sql TO c5win300sql_dat WITH FORMAT
> Every half hour from 06:00 to 23:00 I backup the log:
> declare @.expDate datetime
> select @.expDate = dateadd(day,1,getdate())
> BACKUP LOG c5win300sql TO c5win300sql_log WITH EXPIREDATE = @.expdate
> From the above, you can see that I set a expiration date, RESTORE HEADERON
LY FROM c5win300sql_log
> confirms, that it works correctly.
> However, the c5win300sql_log backup continues to grow and the expired logs
does not get "deleted"
> from the file. How can I rectify this issue "properly"?
> --
> I doubt, therefore I might be.[/vbcol]|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote in message news:%23yIEs7b%23FHA.600@.tk2msftngp13.phx.gbl

> Maint Wiz has functionality to create new backup files with names
> based on date and time (among other). And Maint wiz can delete files
> other than x days. You can also write similar code yourself, but that
> takes some coding to do.
Thanks for your answer. However, can I with get away with a simple 'WITH FOR
MAT'
at 06:00 (when the first log backup is being done)?
I got a 5 day tape backup and 2x 1 day disk backup, so my butt should be cov
ered
somewhat.
I doubt, therefore I might be.|||I never use FORMAT, unless I either need to overwrite a backup before EXPIRE
DATE (which never
happens as I don't use EXPIREDATE), or want to split up a media set. The nor
mal way to overwrite is
to specify INIT. As to whether you can overwrite every time, only you can de
cide based on how
covered you are (if and where you have put your old backups).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kim Noer" <kn@.nospam.dk> wrote in message news:eCapq9n%23FHA.1676@.TK2MSFTNGP09.phx.gbl...[v
bcol=seagreen]
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> wrote in message news:%23yIEs7b%23FHA.600@.tk2msftngp13.phx.gbl
>
> Thanks for your answer. However, can I with get away with a simple 'WITH F
ORMAT' at 06:00 (when
> the first log backup is being done)?
> I got a 5 day tape backup and 2x 1 day disk backup, so my butt should be c
overed somewhat.
> --
> I doubt, therefore I might be.[/vbcol]