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:
This window will allow the user to further specify options that are to be used during the processing of the SQL Server restore database operation.
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:
This window will allow the user to further specify options that are to be used during the processing of the SQL Server restore database operation.
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.
No comments:
Post a Comment