Recovering and Restoring a SQL Server Database

By Mike Aubert, May 31st, 2002 Posted in SQL Server. Subscribe to our RSS Feed

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).

Written by Mike Aubert - Visit Website

Go To Page: 1 2 3 4

All Tutorials by Category:

Entire site Copyright © 1999-2007 2000Trainers.com, all rights reserved.
Content on this site may not be copied or reproduced in any way without permission.

IT Showcase