Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I often like to create backups when testing the software I work on, and will sometimes create a differential backup if I want to be able to get back to multiple previous states. However, sometimes I realize that I forgot one thing I wanted to include in a differential backup, or I no longer need a previous differential backup. Sometimes I simply want to create a new scenario from the original base image and start working with a new series of differential backups. So I'd like to be able to delete some older differential backups so I don't get confused about which ones I'm using. But I can't find any way to delete just the differential backups, selectively or all at once.

share|improve this question

3 Answers 3

Easiest way to do this, is to just script out the BACKUP and change the name of the file in the "TO DISK" option for each of the differential backups you want to save. e.g.:

BACKUP DATABASE [AdventureWorks] 
TO  DISK = N'D:\Backup\AdventureWorks_Differential_01.bak' 
WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N'AdventureWorks-Differential Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM

GO

Then, you can just delete the files on the file system as needed.

HTH, Dan

share|improve this answer
    
Any friendlier way to do this through Management Studio? –  BlueMonkMN Feb 2 '11 at 23:49
1  
In your "Back Up Database" dialog box through Management Studio, change the "Destination" to Disk and then click "Add" and give it a folder and unique file name. You will need to do this each time though. –  SQL3D Feb 3 '11 at 18:05

Where are you saving them to? It's really as simple as right-click/delete (or highlighting the ones that you don't want and hitting the delete key). They're just files in your file system.

Edited to add:

It sounds like you're only creating one backup set and under options you have it set to append to that backup set.

alt text

Instead you'll want to set your differentials and fulls for that matter, as their own backup sets without appending. Then it's a matter of simply deleting the differential files that it creates. Otherwise you'll have to create a maintenance plan, which isn't very conducive to one off backups.e

share|improve this answer
    
Well, I'm using MS SQL Server 2005 Management Studio, and I select an existing database that I have performed a full backup on before, and it tells me the backup filename. Then I tell it to add a differential backup, and it seems to go into the same file. Later if I return and click the "Contents" button to see what's in the file, it shows the full backup and all differential backups, and I can't seem to delete any. –  BlueMonkMN Apr 2 '10 at 17:47
    
Added some information based on your additional input. –  GregD Apr 2 '10 at 18:51
    
So do I want to select "back up to a new media set and erase all existing backup sets"? I don't like the sound of that "erase all existing backup sets". It sounds like its going to delete my base backup when I do the differential backup (as nonsensical as that is). It's just confusing -- what does it really mean? –  BlueMonkMN Apr 2 '10 at 19:47
    
I tried adding a backup device, doing a differential backup and selecting "back up to new media set". The size of my original backup went from 126,158 KB down to 10,381 KB (and a new file was created with the same size). Fortunately I made a copy of the original backup file, but based on the size, it looks like it lost the base backup, so this seems like really bad advice that could have people losing their backups. –  BlueMonkMN Apr 12 '10 at 16:26

The best solution I've found so far is to remove the existing backup destination (so that the new backup won't overwrite it) and create a new one in a new file, then create a differential backup to that new file with the "back up to a new media set" option selected. But I currently don't understand this solution or even know if it works for sure (I haven't done a lot of backing up and restoring lately). How does it know what the base backup looks like if I've removed that file from the destination set?

I'm starting to think the best way to delete all my differential backups might actually be to skip all that and just restore to my base backup, then re-backup overwriting all existing backup sets. This method doesn't let me pick and choose which differential backups to delete, but at least I can understand what's going on and rely on it.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.