SQL Server Restore Database

The database jobs are working efficiently and your data is now being backed up on a routine basis so that in case something happens you have a way to restore your database.

Then one day it finally happens, your server crashes and your mission-critical database is now corrupt and cannot be accessed by SQL Server's engine.

Now comes the time to put the database backups to use that you have created in order to get your database working again.

A database can be restored in SQL Server using either Enterprise Manager or with Query Analyzer. Most people are more comfortable using Enterprise Manager and its windows-like graphical user interface to accomplish the database restoration.

However, some people prefer to use Query Analyzer and the Transact-SQL language to accomplish the SQL Server database restore.

The graphical user interface of Enterprise Manager makes the job of restoring a database easier for those who do not know Transact-SQL and do not want to take the time to learn it.

To restore a database from backup using Query Analyzer, open a session of Query Analyzer and enter the restore command. The syntax for the most basic command to restore a database is as follows:

RESTORE DATABASE databasename FROM backupdevice

The databasename parameter is the name of the database that is to be restored. The backupdevice parameter is the name of the backup device to use for the source of the backup.

For example, the following command will restore the Northwind database from the backup device Nwind1.

RESTORE DATABASE Northwind FROM Nwind1

This gives a brief description of how to perform a SQL Server restore database from database backup using Query Analyzer and the Transact-SQL commands to perform the operation.

While some people prefer this method, most people prefer to use Enterprise Manager in order to handle a restoration of a database backup.

To restore a database from a backup set using Enterprise Manager, right click on the database that you want to restore and select the All Tasks menu item in order to get to the Restore Database option item.

The following illustration shows the menu options needed in order to execute the SQL Server Restore Database option:

This option initiates the request to perform the database restoration process.

In essence, this wizard is creating the same Transact-SQL statements that can be used Query Analyzer to perform the restore operation on the database in question. The restore database wizard screens will submit this statement to the SQL Server engine to perform the operation.

After selecting the restore database menu option, the following screen appears:

The first parameter that should be entered is the name of the database that is to be restored. If the database exists already, the database will be overwritten by what is contained in the backup set.

The next option that needs to be entered is the selection of the backup type that is to be restored. The type of restore option used is determined by the type of backup set that you are performing the restoration from.

The possible options are database, filegroups or files or from device. The user can also use the dropdowns on the screen to show the backups that have taken place for the database in question. This can be helpful if the user is unsure of which backup to do the restore operation from.

There is also a dropdown that allows the user to select which backup set to restore first. In some cases restoring a database can require more than one backup set to get the database completely restored to the state that is needed for proper operation while minimizing the loss of data.

The key factor that needs to be considered when a backup strategy is being devised and implemented is the amount of data and time that can be afforded to be lost in the case of a disaster.

For this reason, many companies decide to create different types of backups throughout the day and week in order to ensure that the least amount of data and effort is wasted if a disaster occurs.

In the SQL Server restore database wizard there is also an Options tab that helps to further define the restoration operation that needs to be performed. The Options tab is shown in the following image:

The user can specify whether or not to eject each tape during each step of the restoration process if tapes are being used for the restore operation.

There is also an option to prompt for confirmation before restoring each backup that is being used. The option may also be checked to overwrite an existing database with media used in the process of restoring the database.

After these options are specified, there are choices that can be selected by the user to specify how to leave the database after the recovery operation has been completed.

The first option is to leave the database operational. This selection restores the database and makes it operational for use, however, it also places a restriction on the database so that no additional transaction logs can be restored.

The second option is to leave the database nonoperational after the SQL Server restore database operation but allow the user to restore more transaction logs which can help recover even more data and make the database even more current with what was lost.

The final option is to leave the database in read-only mode and allow the restoration of additional transaction logs.

Once the options are selected, click OK and the restoration of the database will proceed with the options that have specified and will alert the user when the operation is complete or fails.

Return to SQL Server DBA