Understanding SQL Server backup types
Applies to: SQL Server 2000, SQL Server 2005
 Database
backups are at the core of any SQL Server disaster recovery planning for any
production system. Backups may be used to provide a means of recovery to a
point-in-time when the database was last operational. Microsoft® SQL Server™
provides several types of backups that may be combined to formulate a customized
disaster recovery plan depending on the nature of the data and the recovery
requirements. It is highly recommended that all SQL Server databases be backed
up periodically.
SQL Server backup media
A database may be backed up to disk or to tape. The examples in this article
assume a disk backup directly into a disk file (as opposed to a disk backup
device). Any database can be backed up to a random disk file at any time. The
file may either be initialized (using WITH INIT) or appended with the new
backup.
Types of backups in SQL Server
SQL Server provides several different kinds of backups including Complete,
Differential, Transaction Log, and File(s) and Filegroup(s)
backup. A combination of these backups may be used to formulate a robust
disaster recovery strategy. The following paragraphs explain each SQL Server
backup type
Complete database backup
A complete database backup creates a stand-alone image of the entire database. A
complete database backup is self-dependent and may be restored to either the
same or a new database on the same or a different server. This provides plenty
of flexibility at the time when this backup has to be restored. A complete
backup may be restored without the need for any other kind of backup. It may
also be performed for databases in any recovery model. Restoring a complete
database backup typically would be considered a starting point for a disaster
recovery situation where the entire database is lost or damaged. It is
recommended that a complete database backup be performed at regular intervals
for all production databases. It is also recommended that a complete backup
should be performed for system databases if there are any changes performed to
the SQL Server operating environment such as creating or removing databases,
configuring security, creating and modifying DTS/SSIS packages or scheduled
jobs, adding and removing linked servers, etc.
Backup syntax
BACKUP DATABASE Northwind
TO DISK = 'c:\backups\northwind.bak'
WITH INIT
Restore syntax (Same database)
RESTORE DATABASE Northwind
FROM DISK = 'c:\backups\northwind.bak'
Restore syntax (New database and/or server)
RESTORE DATABASE Northwind_new
FROM DISK = 'c:\backups\northwind.bak'
WITH MOVE 'northwind' TO 'c:\new_location\Northwind_new.mdf'
MOVE 'northwind_log' TO 'c:\new_location\Northwind_new_log.ldf'
Differential database backup
A differential backup backs up only modified extents since the last complete
backup. An extent is a group of 8 data pages each consisting of 8 KB (64 KB in
total). By definition, differential backups are cumulative. The most recent
differential backup contains all changes from all previous differential backups
performed since the most recent complete database backup. Differential backups
may be considered as an alternative for databases that are large and are
modified infrequently. These would include data warehouse type of databases.
Differential backups have several limitations including the following:
- They do not provide point-in-time restore capabilities
- They may only be restored after a complete database backup is restored
- They may not be performed on the master database
Backup syntax
BACKUP DATABASE Northwind
TO DISK = 'c:\backups\northwind_diff.bak'
WITH INIT, DIFFERENTIAL
Restore syntax (Same database - Note that a complete database backup is restored
first using WITH NORECOVERY)
RESTORE DATABASE Northwind
FROM DISK = 'c:\backups\northwind.bkp'
WITH NORECOVERY
RESTORE DATABASE Northwind
FROM DISK = 'c:\northwind_diff.bkp'
WITH RECOVERY
Transaction log backup
An SQL Server database consists of two components: data file(s) and
transaction log file(s). A transaction log captures the modifications made to
the database. A simple transaction may place several records in the transaction
log. Each of these records is known as a log record and is assigned a unique
identification number known as the log sequence number (LSN). Log records that belong
to the same transaction are linked together through the LSN. If SQL Server service shuts down unexpectedly, upon restart the recovery
process examines the entries in the transaction log and if there are
transactions that have not been rolled forward completely, the recovery process
rolls back the changes performed as part of these incomplete transactions. This
operation is extremely important as it forms the basis of transactional recovery. Entries in the transaction log are also used if transactional
replication is configured for the specific database.
A transaction log backup backs up all transactions since
either the previous transaction log backup, or the complete database backup if
there have been no transaction log backups performed for the database in the
past. This backup may then be used to apply the backed-up changes, in case
disaster recovery is required. Transaction log backups may only be applied to a
database in an unrecovered state. A database may be in an unrecovered state if
it is being restored from a set of backups as part of a disaster recovery
procedure, or if it is configured as a standby database on a warm backup server. A transaction log backup also truncates the inactive portion of the
transaction log, unless the database is configured as a Publisher in
transactional replication and there are transactions pending propagation to
Subscribers.
Each transaction log backup contains a First and Last log sequence number (LSN).
Consecutive transaction log backups should have sequential LSNs for the boundary
log records. These LSN values may be examined using the RESTORE HEADERONLY
command. If LastLSN from the previously restored transaction log backup does not
match the FirstLSN from the backup that is currently being restored, the restore
operation fails with the following error: "This backup set cannot be restored
because the database has not been rolled forward far enough. You must first
restore all earlier logs before restoring this log". If the above message is generated while restoring a particular transaction
log backup, which is part of a set of transaction log backups that are to be
restored, any attempts to restore further transaction log backups will fail with
this message. There could be several reasons for consecutive transaction log
backups being out of sequence. Some of the most common reasons noted from
support experience have been:
- The database recovery model has been changed to Simple and back to either
Full or Bulk-Logged. Switching the recovery mode to Simple causes the
transaction log to be truncated
- Another transaction log backup was performed between the previous
successfully restored backup and the one generating this message
- The transaction log was manually truncated between the two backups
- The database was in Bulk-Logged recovery model and non-logged operations
were performed
- Transaction log backups are not allowed for databases in Simple Recovery
model. While in Simple Recovery model, a database’s transaction log is
truncated every time a CHECKPOINT is invoked for the database
Transaction log backups provide the possibility of performing a point-in-time
restore or point-of-failure restore. You can also perform a restore to a named
transaction with transaction log backups.
Backup syntax
BACKUP LOG Northwind
TO DISK = 'c:\backups\northwind_log_1.bak'
WITH INIT
Restore syntax (Same database - Note that a complete database backup is restored
first using WITH NORECOVERY, then a sequence of transaction log backups)
RESTORE DATABASE Northwind
FROM DISK = 'c:\backups\northwind.bkp'
WITH NORECOVERY
RESTORE LOG Northwind
FROM DISK = 'c:\northwind_log_1.bkp'
WITH RECOVERY
File(s) and Filegroup(s) backup
Use BACKUP to back up database files and filegroups instead of the full
database when time constraints make a full database backup impractical. To back
up a file instead of the full database, put procedures in place to ensure that
all files in the database are backed up regularly. Also, separate transaction
log backups must be performed. After restoring a file backup, apply the
transaction log to roll the file contents forward to make it consistent with the
rest of the database.
Sources include the Microsoft website
|
