Each backup that you create is a backup set, and details of it are stored in the msdb.dbo.backupset table. A backup set can be stored in a single file, or in multiple files. A file can store multiple backup sets.
So, if you back up using the same file name every time, the file will simply grow and grow. A common misconception is that if you use the same file name every time, the old backup set will be overwritten. That is certainly not the case.
How can you tell if a file contains more than 1 backup set? You use the RESTORE HEADERONLY command.
All backup sets contained in the file are listed. How do you then restore a particular backup set? You use the FILE parameter e.g.
This restores the AdventureWorks database using the third backup set contained in the file.
What happens when you don't use the FILE parameter? SQL Server will then restore the database using the first backup set.
Is it possible to delete a particular backup set from a file? No. Is it possible to always back up to the same file name, but overwrite the old backup set i.e. so that the file contains only the latest backup set? Yes, by using the INIT option during a backup, e.g.
If a backup set was created using the EXPIREDATE or RETAINDAYS options, the INIT option may fail to overwrite the previous backup set. When a backup set is created with the EXPIREDATE option, the backup set cannot be overwritten before the date that has been set. Similarly, a backup set created with the RETAINDAYS option indicates that the backup set cannot be overwritten within the specified number of days of its creation. If an attempt is made to overwrite the backup set, a message similar to the following is displayed:
If you still want to overwrite the backup set, regardless of the EXPIREDATE or RETAINDAYS settings, use the FORMAT option e.g.
·You could stick an entire backup set for a week in a single file e.g. a full backup at the start of the week, followed by periodic differential backups and transaction log backups. Then, to restore the database, you simply need that single file, and restore the backup sets that you require.
·All your backups are in a single file. Losing that file, either physically or to disk corruption, would cause you to lose a lot of backup sets.
·If you plan to copy the backup file to a remote location after each backup set, earlier backup sets are copied again each time, as you cannot copy just the latest backup set.