Remove indivdual backups from a backup device

Question

Remove indivdual backups from a backup device

jperret

Newbie

Points: 5
More actions
April 8, 2004 at 9:51 am

#108117

How do I remove individual backups from my backup device (to disk)?
The backup is crated by a scheduled job w/ command as follows:
BACKUP DATABASE Master TO Master_Nightly
WITH
MEDIANAME = 'Master_Nightly',
NAME = 'Master_Nightly',
DESCRIPTION = "Master Nightly Backup",
RETAINDAYS = 8
I would like to only keep at set of nine on my device, but when I looked at "view contents" on the device (Master_Nightly) it shows 20+ backups.

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

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

SQLBill SSC Guru Points: 51440 More actions · Answer 1

Here's the issue:

You create a Backup for the first time on 1 April - retain for 8 days. The backup file has the date of 1 April. You append a backup to the file on 2 April. The backup file now has the date of 2 April. You append a backup to the file on 3 April, the backup file now has the date of 3 April.

Until you stop appending to the file, the date of the file will keep changing and "retain 8 days" will never be met.

SQL Server is ALL OR NOTHING. It has to delete the WHOLE file or none of it. So as the date of the file changes, SQL Server thinks that 0 days has passed.

Solution is to create separate backup files each day. This can be done by appending the date to the backup file name. I don't do that myself, but I've seen the solution posted. Search through the backup forum and I'm sure you'll find the answer.

-SQLBill

jperret Newbie Points: 5 More actions · Answer 2

Thanks SQLBill,

I figure that out fot or posts . . .

I would think that if SQL SERVER can be selective on which backup (in a device) that you can recover with; there must be a way to manipulate the backups within this device.

SQLBill SSC Guru Points: 51440 More actions · Answer 3

When you append a backup, SQL Server adds a header with the backup number. But when it has to delete a file, that's what it's deleting - the whole file. SQL Server uses the Windows file date to determine if the 'Retain x Days' has passed.

-SQLBill

tosscrosby Hall of Fame Points: 3140 More actions · Answer 4

You can also use a maintenance plan and specifiy within there "Remove files older than". If you specify 8 days, this will retain each file for 8 days and then over lay the oldest file.

Terry

jperret Newbie Points: 5 More actions · Answer 5

Does the maintensnce plan create one file that hold all the backups? If this is the case then I would want to look at the internal of the maintenance plan.

tosscrosby Hall of Fame Points: 3140 More actions · Answer 6

One file per backup, so if you retain for 8 days, you'll have 8 files.

Terry