Backup with time expired

hello, i'm newbie at here. Nice to be here. I wanna ask about a query to backup database with time expired. I use SQL Server 7.0, 2000, and 2005. Thanx for response...
You need to provide more information. What do you mean by "with time expired".|||I mean with "time expired" is when you set backup option with overwrite existing media, you also can set a backup expire time.
Like when you set 1 day at the option then it will not backup automatically after 1 day.
|||

Hi,

Are you writing script or using wizard? If you are writing script provide and we will review. If you are using the wizard, it's pretty self explanatory, but the older versions 7 & 2000 don't have expiration, but you can delete files older than 1 day, etc. on the "specify backup disk directory" page. How we do our backups is after the backup is created it deletes previous day's backup. If you cannot afford to lose any data then do differencials frequently (hourly) with logs every 5 minutes. Deleting the differencials every 2 hours and logs every 3 hours. All of our backups are done to disk first and then to tape. Tapes are used for a week, with the weekend being save until the next week, the month-end saved until you have a year-end and year-ends saved infinitely.

Something like that.

Books on line have the scripts: MSSQL 2000

BACKUP

Syntax

Backing up an entire database:

BACKUP DATABASE { database_name | @.database_name_var }
TO < backup_device > [ ,...n ]
[ WITH
[ BLOCKSIZE = { blocksize | @.blocksize_variable } ]
[ [ , ] DESCRIPTION = { 'text' | @.text_variable } ]
[ [ , ] DIFFERENTIAL ]
[ [ , ] EXPIREDATE = { date | @.date_var }
| RETAINDAYS = { days | @.days_var } ]
[ [ , ] PASSWORD = { password | @.password_variable } ]
[ [ , ] FORMAT | NOFORMAT ]
[ [ , ] { INIT | NOINIT } ]
[ [ , ] MEDIADESCRIPTION = { 'text' | @.text_variable } ]
[ [ , ] MEDIANAME = { media_name | @.media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @.mediapassword_variable } ]
[ [ , ] NAME = { backup_set_name | @.backup_set_name_var } ]
[ [ , ] { NOSKIP | SKIP } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
]

In the code you can see EXPIRATIONDATE, RETAINDAYS, all is described in the books on line.

BACKUP WITH RETAINDAYS

How can I delete from a bakup all expired backups.
I am using BACKUP WITH RETAINDAYS=5 but all expired backups are still there
and the backup file grows very fast.
Sorin SanduI had a similar issue... ...although we have bags of disk space, we don't
have bags of space on our tapes.
We have full backups, differential backups and transaction log backups, the
full backup occurs once a week.. ..I have a vb script that runs before the
full backup that moves the existing .bak file to a subfolder and deletes the
previous .bak file, then the backup creates a new clean .bak file.. ..I end
up having 2 weeks on hand on the disk, and the current backup archived to
tape nightly.
"Sorin Sandu" wrote:

> How can I delete from a bakup all expired backups.
> I am using BACKUP WITH RETAINDAYS=5 but all expired backups are still ther
e
> and the backup file grows very fast.
> Sorin Sandu
>
>|||I understand that but what use has then RETAINDAYS OR EXPIREDATE '
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:BB966444-1A6A-4CFB-9857-D79A264C7F8C@.microsoft.com...[vbcol=seagreen]
>I had a similar issue... ...although we have bags of disk space, we don't
> have bags of space on our tapes.
> We have full backups, differential backups and transaction log backups,
> the
> full backup occurs once a week.. ..I have a vb script that runs before the
> full backup that moves the existing .bak file to a subfolder and deletes
> the
> previous .bak file, then the backup creates a new clean .bak file.. ..I
> end
> up having 2 weeks on hand on the disk, and the current backup archived to
> tape nightly.
> "Sorin Sandu" wrote:
>|||I was under the impression retaindays and expiredate restrict sql from
overwriting the backup, not flaging the backup to be overwritten, therefore
if there's additional space it won't overwrite it by default...
"Sorin Sandu" wrote:

> I understand that but what use has then RETAINDAYS OR EXPIREDATE '
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:BB966444-1A6A-4CFB-9857-D79A264C7F8C@.microsoft.com...
>
>|||Little use. It just won't allow you to do INIT before the specified date/tim
e. Like not putting in
the wrong tape by mistake and overwrite a log backup that would break the lo
g backup chain.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sorin Sandu" <jbilau@.yahoo.com> wrote in message news:%23XwwQMhaGHA.4060@.TK2MSFTNGP02.phx.g
bl...
>I understand that but what use has then RETAINDAYS OR EXPIREDATE '
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:BB966444-1A6A-4CFB-9857-D79A264C7F8C@.microsoft.com...
>

BACKUP WITH RETAINDAYS

How can I delete from a bakup all expired backups.
I am using BACKUP WITH RETAINDAYS=5 but all expired backups are still there
and the backup file grows very fast.
Sorin SanduI had a similar issue... ...although we have bags of disk space, we don't
have bags of space on our tapes.
We have full backups, differential backups and transaction log backups, the
full backup occurs once a week.. ..I have a vb script that runs before the
full backup that moves the existing .bak file to a subfolder and deletes the
previous .bak file, then the backup creates a new clean .bak file.. ..I end
up having 2 weeks on hand on the disk, and the current backup archived to
tape nightly.
"Sorin Sandu" wrote:
> How can I delete from a bakup all expired backups.
> I am using BACKUP WITH RETAINDAYS=5 but all expired backups are still there
> and the backup file grows very fast.
> Sorin Sandu
>
>|||I understand that but what use has then RETAINDAYS OR EXPIREDATE '
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:BB966444-1A6A-4CFB-9857-D79A264C7F8C@.microsoft.com...
>I had a similar issue... ...although we have bags of disk space, we don't
> have bags of space on our tapes.
> We have full backups, differential backups and transaction log backups,
> the
> full backup occurs once a week.. ..I have a vb script that runs before the
> full backup that moves the existing .bak file to a subfolder and deletes
> the
> previous .bak file, then the backup creates a new clean .bak file.. ..I
> end
> up having 2 weeks on hand on the disk, and the current backup archived to
> tape nightly.
> "Sorin Sandu" wrote:
>> How can I delete from a bakup all expired backups.
>> I am using BACKUP WITH RETAINDAYS=5 but all expired backups are still
>> there
>> and the backup file grows very fast.
>> Sorin Sandu
>>|||I was under the impression retaindays and expiredate restrict sql from
overwriting the backup, not flaging the backup to be overwritten, therefore
if there's additional space it won't overwrite it by default...
"Sorin Sandu" wrote:
> I understand that but what use has then RETAINDAYS OR EXPIREDATE '
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:BB966444-1A6A-4CFB-9857-D79A264C7F8C@.microsoft.com...
> >I had a similar issue... ...although we have bags of disk space, we don't
> > have bags of space on our tapes.
> > We have full backups, differential backups and transaction log backups,
> > the
> > full backup occurs once a week.. ..I have a vb script that runs before the
> > full backup that moves the existing .bak file to a subfolder and deletes
> > the
> > previous .bak file, then the backup creates a new clean .bak file.. ..I
> > end
> > up having 2 weeks on hand on the disk, and the current backup archived to
> > tape nightly.
> >
> > "Sorin Sandu" wrote:
> >
> >> How can I delete from a bakup all expired backups.
> >> I am using BACKUP WITH RETAINDAYS=5 but all expired backups are still
> >> there
> >> and the backup file grows very fast.
> >> Sorin Sandu
> >>
> >>
> >>
>
>|||Little use. It just won't allow you to do INIT before the specified date/time. Like not putting in
the wrong tape by mistake and overwrite a log backup that would break the log backup chain.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sorin Sandu" <jbilau@.yahoo.com> wrote in message news:%23XwwQMhaGHA.4060@.TK2MSFTNGP02.phx.gbl...
>I understand that but what use has then RETAINDAYS OR EXPIREDATE '
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:BB966444-1A6A-4CFB-9857-D79A264C7F8C@.microsoft.com...
>>I had a similar issue... ...although we have bags of disk space, we don't
>> have bags of space on our tapes.
>> We have full backups, differential backups and transaction log backups, the
>> full backup occurs once a week.. ..I have a vb script that runs before the
>> full backup that moves the existing .bak file to a subfolder and deletes the
>> previous .bak file, then the backup creates a new clean .bak file.. ..I end
>> up having 2 weeks on hand on the disk, and the current backup archived to
>> tape nightly.
>> "Sorin Sandu" wrote:
>> How can I delete from a bakup all expired backups.
>> I am using BACKUP WITH RETAINDAYS=5 but all expired backups are still there
>> and the backup file grows very fast.
>> Sorin Sandu
>>
>