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

Wednesday Jul 3rd 2002 by Michael Aubert

Michael Aubert's tenth article in the SQL Server 2000 Administration in 15 Minutes a Week series takes a look at how to restore a database and also covers some of the database backup/restore options that haven't yet been covered in previous articles.

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

There are two ways we can accomplish the above steps: use Enterprise Manager (sorry, no wizard for this one) or the RESTORE command. We are going to cover how to restore a database using Enterprise Manager, but if you would like more information on the RESTORE T-SQL command, see "RESTORE" in the SQL Server Books Online.

In Enterprise Manager, start by right clicking any database and select "All Tasks" then "Restore Database..."

Click to Enlarge

On the General tab of the Restore database screen we are given a bunch of options:

Starting at the top:

"Restore as database:" - Allows you to specify the name of the database you would like to restore to. If the database name exists, it will be overwritten by the backup.

"Restore:" - Allows you to select what type of restore you would like to perform. SQL Server keeps a record of all backups you make, allowing you to then restore them using the "Database" or "Filegroups or files" options. However, if you are restoring a backup that was taken on another computer or the information backup history is removed from the server, you must use the "From device" option and select each backup manually.

"Database" - Lets you restore all files that makeup a database.

"Filegroups or files" - Lets you restore some or all of the files that markup a database.

"From device" - Lets you restore backups that do not appear on the other two options.

Using the "Database" Restore option, the rest of the "Parameters" are as follows:

"Show Backups Of Database" - Lets you select the database you would like to show the backups for.

"First Backup to restore" - Lets you select the oldest backup(s) you would like to display.

"Point in Time Restore" - Lets you select the time and date you would like to restore up to (EX: right before an error occurred or data was deleted). Restoring to a point in time is only available for databases that use the Full recovery model or the Bulk_Logged  recovery model (as long as no bulk operations have occurred since the last full backup). Clicking the "..." button brings up the following screen:

Last, the list box displays the backups you have taken. A checkmark under the "Restore" column indicates you would like to restore that backup.

Now let's look at some of the options on the "Options" tab:

Starting at the top:

I think the first three are self explanatory :)

"Restore database files as" - Lets you rename or move any of the files that you are restoring from the location they were in when you backed them up.

"Leave database operational..." - Will run recovery once the backup has been restored. If you select multiple backups on the general tab, the recovery process will not take place until after all the backups have been restored.

"Leave database nonoperational..." - Will not run recovery and will not allow users to access the database.

"Leave database read-only..." - Will not run recovery, but will allow users to read the data (not write/edit data).

Once you are done selecting all your options, click OK to restore the database. After the restore completes, you should get the following dialog:

Page 3: Other Backup Options

 » See All Articles by Columnist Michael Aubert

Database Recovery Models 

Other backup options

There are a few other backup options/strategies that I wanted to quickly cover before we move on in the series.

First, SQL Server allows for backup to multiple devices at once, which can speed up the time it takes to backup/restore to/from tape. The screen below shows two files (however it could just as easily be two tape drives) used to backup the EXBackup database. Note that you will need both files if you ever need to restore the backup.

Click to Enlarge

(Note: To access the " SQL Server Backup" screen in Enterprise Manager, first right click the database you would like to backup up, then select "All Tasks..." > "Restore Database.")

There are also a few options I think are important to know on the "Options" tab:

"Verifying backup upon completion" will make sure you have a good backup by reading the tape/file and checking for any corruption of the data. The downside is that this can add a significant amount of time to your backup.

"Remove inactive entries from transaction log" is equivalent to the NO TRUNCATE option of the backup command. If checked, entries in the log file(s) that are no longer needed after a backup will be freed for reuse. If the box is not checked, entries in the log file(s) that are no longer needed after a backup will not be removed (i.e. NO TRUNCATE) for the transaction log.

Also, the Media set name options allow you to give a tape a name that must be the same in order to write to the tape. Additionally, you can set an expiration date before a tape can be overwritten (without a manual override). These options keep you from accidentally overwriting data when you use scheduled jobs.


Speaking of scheduled jobs, you may be wondering how the heck you can edit/delete backup jobs that you have created. You can find them under the "Management" folder of your SQL Server, then "SQL Server Agent", and finally under "Jobs."

By right clicking a job you can do things like: view its history (i.e. if it ran or not), start it manually, or view its properties to set the schedule. Don't worry too much about jobs right now as we will be looking at the SQL Server Agent in much greater detail later in this series.


The last object relating to backups I want to cover are "Backup Devices." So far I have been using physical device names to make backups (ex: e:\backups\SAT.BAK); however, you can also create and use logical device names when creating a backup. The main benefit of using logical devices is that it can help you manage your backups. Let's create a Backup Device for the above file:

To create a Backup Device: Under the Management folder of your SQL Server, right click "Backup" and select "New Backup Device." The following screen appears:

In our example we could put SAT in the "Name" field and then e:\backups\SAT.BAK  for the "File Name."

Now when we backup a database (or add a Scheduled database backup), we can now select "Backup device" and choose the logical device we created that maps back to e:\backups\SAT.BAK.


One final, final, final note about backups...back up the system databases! Objects like the server logons (that we will look at here in the next few weeks) and information such as what other databases are on that server, are/is stored in the master database. Having a complete server failure and only having backups of your user databases is not the end of the world...but it could be a lot of work to add all the lost server info again. So back up your master, model, and msdb databases frequently, especially if you have just made lots of management or configuration changes.

Well that about raps up the backup/restore process, but backups will be back (much) later in this series...mainly a simpler way to manage them and how to deal with backup/restores of individual database files. Next week we are going to turn away from management for a little bit and start looking at how to create tables and other database objects. As always...If you have any technical questions please post them on the SQL message board. Please send any non-technical questions, comments, and feedback to my email. I hope you are finding this series to be a useful one, and I'm looking forward to your feedback.


» See All Articles by Columnist Michael Aubert

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved