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...
>
Showing posts with label retaindays. Show all posts
Showing posts with label retaindays. Show all posts
Monday, February 13, 2012
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
>>
>
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
>>
>
Backup w/RETAINDAYS just keeps growin', and growin'
Hi all;
I need to make daily backups to a computer in another domain, where I can't map a drive, so I use the TSQL to create a dump device, which works fine:
EXEC sp_addumpdevice 'disk', 'MyBackupDevice', [ipaddress & directory/file]
Then I schedule a server job which faithfully backs up my database, with one hitch - it never deletes days older than 5 as per the code - it just keeps growing. The command couldn't be much simpler:
BACKUP DATABASE ProductionETC to MyBackupDevice WITH RETAINDAYS = 5
For now, I just create a new backup device every now and then and go back and forth, deleting the oldest one when I create a new one. But it's annoying! Any ideas what I'm doing wrong?
Thanks,
L. Rap
RETAINDAYS only prevents SQL Server from overwriting the file unless the specified number of days have elapsed. It doesn't mean that SQL Server will automatically delete the file or erase the media after the specified number of days. So you will have to delete the files using your own scheduled job or use a database maintainence task (you can create from Enterprise Manager or SSMS).|||Thanks Umachandar, that's very helpful. I misunderstood the instructions.
Would you happen to know if it is possible to delete the earliest backup from the set using T-SQL or some other preset command, or if I need to schedule an OS job to simply delete the file and start over?
Regards,
L. Rap
Subscribe to:
Posts (Atom)