anthony.green (2/14/2013)

---

You dont have an INIT and FORMAT so it is appending the diff backup to the media set not overwriting it, so it contains all 13 days diff backups.

Highly risky move, should be backing up to individual files not one big file, as should anything happen to that 1 file you cannot restore to any of the subfiles contained within.

Abu Dina (2/14/2013)

---

I do FULL backups every two weeks and daily differentials on a number of SQL instances.

The backup command I use for the differential backups in my sproc is like the below:

BACKUP DATABASE MyDatabase TO DISK = 'MyDatabase.Differential' WITH DIFFERENTIAL,  NAME = 'MyDatabase Differential Backup' , BUFFERCOUNT = 1024, CHECKSUM

At the end of a two week cycle I end up with two files

MyDatabase.FULL - Day 1
MyDatabase.Differential - DAY13

When I do my restores I start with the FULL obviously and then my latest differential. at this stage I am given the option to choose from 13 files, 1 for each day of the differential backup. This is great because it means I can restore to any day I want but what I don't get is, I am not creating a new differential backup file. I am overwriting the backup file using the same name. How does SQL Server work this out?

Hope I make sense!

anthony.green (2/14/2013)

---

Highly risky move, should be backing up to individual files not one big file, as should anything happen to that 1 file you cannot restore to any of the subfiles contained within.

SQL Show (3/7/2013)

---

Anthony Green, Could you please explain me? If 1 file corrupted, can not I restore 5th differential file?