Recovering and Restoring a SQL Server Database

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

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.

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…”

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 and you can 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 this 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 this dialog.