Delete certain backups from a file

Question

Post reply

Delete certain backups from a file

Toby White

SSCertifiable

Points: 6187
More actions
October 14, 2010 at 9:01 am

#229645

If I append TLog backups to a file can I delete all the backups from that file that are older than a certain date? It seems like the only procedure that can do something like this is xp_delete_file, but that deletes the entire file, and I just want to delete certain backups on the file.
I know there are other ways of accomplishing this like using a different file every day or week, but I would like to know if there is a way to do a partial file delete.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

Toby White (10/14/2010)
I just want to delete certain backups on the file.

What does it mean?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

Bhuvnesh (10/15/2010)
Toby White (10/14/2010)
I just want to delete certain backups on the file.
What does it mean?

Backup Device ?

Lowell SSC Guru Points: 323518 More actions · Answer 3

Bhuvnesh (10/15/2010)
What does it mean?

when you do a backup that does not include the WITH INIT clause, the new backup is appended to the same physical file. So after two weeks of daily FULL backups for example, a single .bak file might contain 14 individual backups.

The OP question is suppose he doesn't need backups from 8-14 days ago from my example....can he remove them and leave the last 7.

His situation might be more complicated, since the backup file could contain a mix of different backups...full and transaction for example...if he has two FULL backups in the stack, he doesn't need the oldest FULL anymore, right?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

Lowell (10/15/2010)
Bhuvnesh (10/15/2010)
What does it mean?
when you do a backup that does not include the WITH INIT clause, the new backup is appended to the same physical file. So after two weeks of daily FULL backups for example, a single .bak file might contain 14 individual backups.
The OP question is suppose he doesn't need backups from 8-14 days ago from my example....can he remove them and leave the last 7.
His situation might be more complicated, since the backup file could contain a mix of different backups...full and transaction for example...if he has two FULL backups in the stack, he doesn't need the oldest FULL anymore, right?

Ohh got it thanks but now it seems like bad backup planning

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Toby White SSCertifiable Points: 6187 More actions · Answer 5

Lowell, you are exactly correct, Bhuvnesh, I am only interested in whether it can be done, and if so what the procedure(s) is/are.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

Michael Valentine Jones

SSC Guru

Points: 64818

October 15, 2010 at 7:27 am

#1237522

The simple answer is no.