| |
 |
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.
| | | | NOTE: 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
Pros:
|
|
·
|
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.
|
Cons:
|
|
·
|
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.
|
Discuss or comment on this article on our Facebook group.
Document history
| 7/1/2008 | Initial release. |
|
