SQL Server 2000 Administration in 15 Minutes a Week: Restoring and Recovering Databases - Page 2
July 3, 2002
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
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
"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
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