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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers