If you are a Database Administrator (DBA), I have no doubt
in my mind that you have been called away from home, dinner, or just plain in
the middle of the night to answer a page on a disaster with your database(s).
In this field, it is the nature of the beast to get called away at the most
inappropriate time. Aren’t you glad you backed up your database(s)? Or, did
you? In this article, we will introduce you to the different types of backup
strategies available in SQL Server 2000.
Have a Plan?
Before developing your backup strategy, you need to develop
a plan. What I mean by this is that you need to evaluate your specific company
and decide on what the maximum amount of downtime is. Once this is established,
you can decide on the appropriate course of action.
Backups 101
In backups 101, we explore the different types of backup
strategies and how to back them up using the graphical user interface (GUI) and
Transact SQL (TSQL).
Full Backups
A full backup is the easiest type of backup to implement in
SQL Server. This type of backup takes a complete picture of your database. This
picture includes backing up users and permissions. In addition, this backup can
occur while transactions are still occurring in your system. When the backup
begins, the date is recorded, the data pages are backed up and all transactions
that occur while the backup runs are appended to the backup.
To backup a database using Enterprise Manager, expand the
SQL Server to display your databases. Right-click on the database and select
All Tasks | Backup Database. Select your Backup Type (Figure A). If you have a
tape drive, you can choose whether to backup to disk or tape. If it is the
first backup of your database, you will have to create a backup device or file.
Figure A.
A backup device is simply a location that stores your
backups. A backup file can also hold multiple backups but a drawback is that
the file is not created until the backup starts. To create a backup device,
choose New Backup Device, define your path, and click Ok.
Note: A full backup backs up your users but not your logins.
If you want to backup your logins, remember to backup the Master database.
Furthermore, if you ever restore your database to a different server, you must
synchronize your logins by using the sp_change_users_login. Please see a more
detailed explanation of this stored procedure in SQL Books Online.
You can choose to overwrite your backup or append to your
backup. This means that you can add multiple backups to your device or
completely overwrite your backups with one backup. If you plan to schedule your
backup, select the schedule checkbox 
and
choose Change. You can choose from the following types:
- Start Automatically when SQL Server Agent Starts
- Start Whenever CPU becomes Idle
- One Time
- Recurring
On the Options tab, you can also choose the following.
- Verify Backup upon Completion
- Eject Tape After Backup
- Remove Inactive Entries from Transaction Log
- Check Media Set Name and Backup Set Expiration
- Backup Set will Expire
- Initialize and Label Media
Backups in T-SQL
You can also backup a database using T-SQL; it has options
that are not available when backing up data in Enterprise Manger. The syntax is
as follows:
BACKUP DATABASE { database_name | @database_name_var }
TO < backup_device > [ ,…n ]
[ WITH
[ BLOCKSIZE = { blocksize | @blocksize_variable } ]
[ [ , ] DESCRIPTION = { ‘text’ | @text_variable } ]
[ [ , ] DIFFERENTIAL ]
[ [ , ] EXPIREDATE = { date | @date_var }
| RETAINDAYS = { days | @days_var } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] FORMAT | NOFORMAT ]
[ [ , ] { INIT | NOINIT } ]
[ [ , ] MEDIADESCRIPTION = { ‘text’ | @text_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
[ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
[ [ , ] { NOSKIP | SKIP } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
For a definition of each option, please see the description
in SQL Books Online. In the following example, I will backup the Pubs database
(Figure B).
Figure B.
Note: By adding NOINIT, I appended the information to the
end of the backup and by adding Stats to the argument, I received a percentage
of the backup completion.
Differential Backups
A differential backup is a backup that only backs up the
data that has changed since the last full backup. These backups are generally
smaller and can be used frequently since they run much faster than a full
backup. To perform a differential backup, choose the differential backup radio
button when backing up the data in Enterprise Manager.
To perform a differential backup using Transact SQL, use the
Backup command but add the differential argument (Figure C.)
Figure C.
Transaction Log Backups
A transaction log backup will backup all transactions that
occur in the database and purge or clean up the log after the backup completes.
Using a transaction log backup is what truly gives you point-in time-recovery
for a database. To perform transaction log backups, choose the transaction log
backup option from Enterprise Manager.
Note: You must be using Full or Bulk-Logged Recovery Models
to perform transaction log backups. This will be discussed later in the
article.
Backing up a transaction log
To perform a transaction log backup use the following syntax:
BACKUP LOG { database_name | @database_name_var }
{
TO < backup_device > [ ,…n ]
[ WITH
[ BLOCKSIZE = { blocksize | @blocksize_variable } ]
[ [ , ] DESCRIPTION = { ‘text’ | @text_variable } ]
[ [ ,] EXPIREDATE = { date | @date_var }
| RETAINDAYS = { days | @days_var } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] FORMAT | NOFORMAT ]
[ [ , ] { INIT | NOINIT } ]
[ [ , ] MEDIADESCRIPTION = { ‘text’ | @text_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
[ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
[ [ , ] NO_TRUNCATE ]
[ [ , ] { NORECOVERY | STANDBY = undo_file_name } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOSKIP | SKIP } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
For a definition of each option, please see the description
in SQL Books Online. To perform a transaction log backup using Transact SQL,
use the Backup Log command (Figure D).
Figure D.
File Group Backups
A file group backup lets you backup individual files or file
groups. If you are unfamiliar with this, it basically allows you to backup a
large database by spreading your backups out over time.
Using a Database Recovery Model
The database recovery model is a new feature that was added
to SQL Server 2000. This new model allows you to control your backups and your
disaster recovery options. Options from SQL Server 7.0 like select into/bulk
copy and trunc. log on checkpoint have been replaced in SQL 2000 by
either choosing Simple, Full, or Bulk-Logged recovery models.
Note: To select a database recovery model, right-click on
the database and choose properties. Click the Options tab and select your
recovery model.
Simple
By selecting the Simple Recovery Model, you are basically
choosing the trunc. log on checkpoint option that appeared in SQL Server
7.0. This recovery plan always truncates your transaction log and constantly
removes your transactions that have been committed. Because of this, the
transaction log cannot be backed up which leaves you with only two backup
options: full and differential.
Note: If you try to backup the transaction log when using a
Simple recovery model, you will receive an error stating, “The backup log is
not allowed while the recovery model is Simple.”
Full and Bulk
These options add an administration and space burden but
provide the maximum protection for your data.
Note: The Full Recovery model provides you with better flexibility for
recovering databases to an earlier point-in-time and the Bulk-Logged model
provides higher performance and lower log space usage.
Now that we have introduced you to backups 101, explore the possibilities
and begin to get familiar with the basics.
