SQL Server 2000 Administration in 15 Minutes a Week: Restoring and Recovering Databases



Welcome to the tenth article in
my series SQL Server Administration in 15 Minutes a Week.
Last week we finished creating a disaster recovery plan. This week we are going to look at how to restore a database
in order to test our plan. We will also cover some of the
other database backup/restore options that were not covered
previously. The topics for this week include:

– Restoring from a backup
– Other backup options
 

Restoring and Recovering
from a backup

Restoring a database from a
file or tape backup is quite simple, but there are a few
things you must think about when using the Full and
Bulk_Logged recovery models. Note that we are not going
to cover restoring a database that uses the simple
recovery model.
The first thing to understand
is that restoring a database and recovering a database are
two different operations entirely. Restoring is simply the
process of copying data from backups into the database.
Recovering, on the other hand, is the process of using the
transaction log to bring the data in the database to a
consistent state. Rather than just give you a one sentence
description, let’s take a closer look at exactly what the
recovery process is.

If you remember back a few
articles (Understanding
Transaction Logs
), I talked about how the recovery
process was used to recover modifications if SQL Server was
improperly shut down, known as restart recovery. Well a
similar process to the one SQL Server
uses if it’s improperly shut down is also
used when we restore a database — known as restore
recovery. If a SQL Server is improperly shut down we end up
with a chunk of data that is in an inconsistent state; that
is, we don’t know what modifications have been saved or
what modifications were not saved before the unexpected
shutdown. In addition to a chunk of inconsistent data, we
also have a log file that contains a list of all the
changes that were made to the data — this is where the
recovery process comes in.

In order to bring the data into
a consistent state all transactions in the log that are
marked as having been completed are re-applied to the data,
known as "rolling forward;" whereas all transactions in the
log that had not been completed at the time of the
unexpected shutdown are undone to the data, known as
"rolling back." By rolling forward completed transactions
and rolling back uncompleted transactions, we are left with
data that is in a "consistent state"…meaning we do not
end up with half a transaction completed which could result
in, for example, funds being deducted from account A and
not being added to account B (i.e. a big mess).

So, that is all good, but what the
heck does recovering from an improper shut down have to do
with a recovery of a backup? Everything…they are
virtually the same
process. If you remember back to Database Backups, I said that SQL Server makes "fuzzy
backups," because the backups are not from a single point
in time. Due to the ability of database data modification
during a backup, the backup is left in an inconsistent
state – we can end up with half the data on our backup that
is from before a large modification and the other half that
is from after a large modification. To overcome this
problem SQL Server can use the portion of the log file it
captured during the backup to recover the data from the
backup. After the data is restored from a backup, the
recovery process can be used to roll forward all the
transactions that took place during the backup – leaving
the data in a consistent state.

There is one more important
thing to know about restore recovery; once you recover a
database you can’t apply any more transaction log backups.
Due to the way log backups are made, it is possible to end
up with part of a transaction on one log and the other half
on the next log. If we ran the recovery process after
applying the first log backup, SQL Server would see that we
had a transaction that was only half done and therefore
roll back the uncompleted transaction. Now, speaking
theoretically here
, if we could apply the next log
backup what would happen during recovery? Because we have
already run the recovery process after the first log
backup, we would only have the second half of a transaction
and SQL Server would have no clue what to do with it. Also,
you can’t just skip this first transaction…later
transactions may depend on this first modification. In
order to overcome this problem and apply multiple backups,
SQL Server gives us the option to run the recovery process
or not. This allows you to restore the first log backup
(which contains the first half of our example transaction)
and then apply the second log backup (which contains the
second half) before running the recovery process.

In summary, after restoring a
backup if you choose to recover the database, all completed
transactions will be rolled forward, all uncompleted
transactions will be rolled back, and the database will be
accessible to users. If you choose not to recover the
database after restoring a backup, the database will be
left in an inconsistent state and it will not be accessible to
users; however, you will be able to restore more log
backups.

Enough with the techno-babble,
let’s look at how to restore/recover a database:

  1. Backup the current
    transaction log, if you can
    – this will allow you to restore right up to the point of
    failure.
  2. Restore the most recent
    full backup without recovering the database
  3. If you have a
    differential backup, restore the most recent one without
    recovering the database
  4. Restore all the
    transaction log backups, in order, from the last full
    backup (or differential backup if you had one) without
    recovering the database
  5. Restore the log backup
    you made in step 1 and recover the database.

Note: If you can’t make the log
backup in step 1, you will need to recover the database
after the last log backup in step 4.

Page 2: Restore Database Screen

 »


See All Articles by Columnist
Michael Aubert

Latest Articles