Although backups are important for maintaining
SQL Server, they’re avoided sometimes by DBAs
who assume backups are too complicated. You can actually
implement backups easily with a few simple commands.
In this article I’ll answer your questions about
basic but crucial elements of database backup and include
scripts for saving a copy of the database which you can use
for a successful restore. For more SQL Server tips, check
out my blog at sqlmag.com/go/SQLskills.
Q: What are the types of backup methods in SQL Server?
A: SQL Server has four backup methods: full, transaction
log or incremental, differential, and file or file group. You
should always perform a full backup, in which you make
a complete copy of the database. The transaction log or
incremental backup method copies all the modifications
made to a database. A differential backup makes a copy
of all the changes in a database since the last full backup.
The file or file group backup method copies the actual
database files on disk.
A full backup quickly restores a database to its original
state and is the baseline for the other types of backups. A
full backup contains all of a database’s data, structures, and
security objects, plus the transaction log, which you can use
to restore any changes made to the database while it’s being
backed up. A database restored by the full backup method
doesn’t contain any uncommitted transactions (transactions
which have begun but to which the administrator
hasn’t explicitly committed in the database either by turning
on auto commit or by using the COMMIT statement). The
following code sample shows a full backup:
BACKUP DATABASE AdventureWorks
TO DISK = ‘C:\full_bk_AdventureWorks.bak’
The transaction log or incremental backup helps
recover a database to its latest working condition. To use
this method, you need to set the database to full recovery
or bulk recovery mode. To restore a database to a point
in time, you must have an unbroken chain of transaction
log backups. You need to first restore the database from a
full backup before you can restore from a transaction log
backup. Finally, you must restore each transaction log
backup in the order in which it was taken. The following
code sample shows a transaction log backup:
BACKUP LOG AdventureWorks
TO DISK = ‘C:\log_bk_AdventureWorks.trn’
Performing a differential backup saves time and media
space compared with performing a full backup. If the
database is set to full-recovery mode, restoring the latest
differential backup restores the database state to the time
the last differential backup was completed. A differential
backup can’t be restored independently; it can be restored
only after a full backup is restored. The following code
sample shows a differential backup:
BACKUP DATABASE AdventureWorks
TO DISK = ‘C:\diff_bk_AdventureWorks.dif’
WITH DIFFERENTIAL
The file or file group backup method is an alternative
way to perform a full backup. This method lets you
quickly restore a database to working condition. However,
you can perform a file or file group backup only under
certain conditions. To use this method, the database must
have been created with multiple files or file groups. The
following code sample shows a file backup:
BACKUP DATABASE AdventureWorks FILE =
‘AdventureWorks_data’
TO DISK = ‘C:\file_bk_AdventureWorks.
data’
Q: What are the recovery models in SQL Server?
A: SQL Server has three recovery models: full, bulk-logged, and simple. In the full recovery model, all changes to the
database are logged, so the database can be restored to the
point of failure with full backup and log files. Use the full
recovery model for essential databases that are updated frequently.
The following code sample shows a full recovery:
ALTER DATABASE AdventureWorks
SET RECOVERY FULL
In the bulk-logged recovery model, all changes to the
database except high-speed bulk insert operations are
logged. (Don’t let the name confuse you—this method
doesn’t actually log bulk processes.) Database performance
isn’t compromised when bulk operations are in process. The
following code sample shows a bulk-logged recovery:
ALTER DATABASE AdventureWorks
SET RECOVERY BULK_LOGGED
The simple recovery model lets you restore a database to
the point of its last full backup. A database that isn’t updated
frequently is a candidate for the simple recovery model. The
following code sample shows a simple recovery:
ALTER DATABASE AdventureWorks
SET RECOVERY SIMPLE
Q: Does the RESTORE HEADERONLY command
change or restore the header of a BACKUP file?
A: This command doesn’t change anything in the header.
The clause simply returns all the backup header information
for all the backup sets on a particular backup device.
The following code sample illustrates using RESTORE
HEADERONLY:
RESTORE HEADERONLY
FROM DISK = ‘C:\file_bk_
AdventureWorks.data’
WITH NOUNLOAD;
Q: What’s a tail-log backup, and when do you use it?
A: The tail-log backup requirement first occurs in SQL
Server 2005 and applies to all subsequent versions. When
you recover a database using the full or bulk-logged
recovery model, before you restore it, you might find
that some transaction log entries don’t yet have backups.
Backing up this set of transaction log entries is known
as a tail-log backup. SQL Server 2005 requires a tail-log
backup operation on a database before it’s restored, to
ensure that no data is accidentally lost. The following code
sample shows a tail-log backup:
BACKUP LOG AdventureWorks
TO DISK = ‘C:\taillog_bk_
AdventureWorks.trn’
WITH NORECOVERY
