Cumulative backups

Prev Page Next Page
Recovery models
Main backup types
Backing up the database files by copying
The transaction log
Transaction log restore sequence
Log sequence numbers
Truncating and shrinking the transaction log
Backing up the tail
Inside the transaction log
So, what's in a backup file?
Test: A full backup does not contain deleted data
Verifying backup files
Verifying backup files on a budget
Cumulative backups
Recovering individual tables
Backup and restore history details
Backup reads and writes
Speeding up backups
Backup speed details
Speeding up restores
Restore state affects speed too
Backup and restore rights
Log shipping
Log shipping in SQL Server 2000
Setting up log shipping using Enterprise Manager
Checking the set up
Log shipping in SQL Server 2005
Setting up log shipping using Management Studio
Checking the set up
Log shipping status report
Log shipping in SQL Backup
Using the CopyTool utility
3rd party backup applications
VDI versions
VDI errors
SQL Backup - beyond compression
Restoring a chain of transaction log backups
Restoring to the latest possible state
Backing up multiple databases
Backup retention
Making a copy of the backup file
Backup file naming conventions
Restoring the latest backup set
Network resilience
Integrated database verification
Database file relocation
Improved backup retention
High-availability group support
Common SQL Backup issues
Installation checklist
Setting up rights
Configuring service rights
Backup data
Hanging issues
Common backup and restore errors
Error 3201 - when performing a backup to a network share
Full database backup file is larger than database size
Error 3205 - Too many backup devices specified for backup or restore
Error 4305 - an earlier transaction log backup is required
Bringing a database that is in recovery or read-only mode online
Using bulk-logged recovery model but transaction log backup is still large
Error 14274 - unable to delete SQL Server Agent job
Error messages when restoring from different versions of SQL Server.
vdi error codes
Restore speed details
Help, my transaction log file is huge!
Mirror or log ship

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.

RESTORE DATABASE AdventureWorks FROM DISK = 'g:\backups\AdventureWorks_full.bak' WITH FILE = 3

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.

If your users complain that the database they are restoring is not the same database that was backed up, it's probably because the backup file contains multiple backup sets, and the wrong set is being restored.  I mean, it can't possibly happen to you, right?  Never happened to me...

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.

BACKUP DATABASE AdventureWorks TO DISK = 'G:\backups\full\AdventureWorks.bak' WITH INIT

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:

Server: Msg 4030, Level 16, State 1, Line 1
The medium on device 'e:\temp\testdb.bak' expires on Jun 23 2008 11:28:50:000PM and cannot be overwritten.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

If you still want to overwrite the backup set, regardless of the EXPIREDATE or RETAINDAYS settings, use the FORMAT option e.g.

BACKUP DATABASE AdventureWorks TO DISK = 'G:\backups\full\AdventureWorks.bak' WITH FORMAT

Pros and cons of cumulative backups


·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.

Document history
7/1/2008    Initial release.    
Copyright 2008 - 2021 Yohz Ventures Sdn Bhd. All rights reserved.
All product and company names are trademarks or registered trademarks of their respective owners.