How To Restore Database In SQL Server 2008 R2 From .bak File Step By Step
There is no doubt in stating that ‘backup and restore’ these two database operations are important for every database administrator of the SQL server. In this post, we are going to discuss different methods through which admins can restore database in SQL server 2008 R2 from .bak file.
Core Prerequisites Needed
The individual must have ‘Create Database’ permissions if the regained database does not exist. This is essentially needed to execute ‘RESTORE’ operation in the SQL server. If the database is available, RESTORE rights defaults to users of dbcreator and sysadmin fixed server role 2008 R2 and the database owner.
The permissions provided with RESTORE are provided to users in which the membership data is all time available on the server. Members of the db_owner fixed database role don’t have RESTORE permissions. It is so because the membership of the fixed database role can be analyzed only when the server database is the inaccessible and undamaged state.
Time to Restore Database in SQL Server 2008 R2
In order to restore database in SQL server 2008 R2 from .bak file, DBAs have to follow below mentioned steps:
- Launch Microsoft SQL Server Management Studio on your system screen and right-click on Databases. This explores a menu in which you have to click on the Restore Database option.
- Herein appears a screen where you are going to encounter Destination for restore field firstly.
a) If the source database being retrieved doesn’t exist, don’t choose any database into a database combo field. The server will automatically load the database after the selection of the database file.
b) If a backup device is used to perform restoration operation, leave it with default settings. In case you desire to restore BAK file of a specified time duration, click on the box that has three dots in the line of to a point in the time field. This opens a ‘Point in time restore’ dialog box where you have to define the desired time duration.
- Next comes the section with name Source for restore.
a) If you have chosen from database radio button, a list of all backups done for that particular database will be displayed. You can select for copying that database at a new location via already existing backups.
b) Or, if you have selected From device field to restore the master database in SQL Server 2008 R2 from the .bak file then, click on three dots button that is just in the line of ‘From device’ option.
c) This opens a window on your PC where you have to browse towards the location where currently restored flat files are saved. Select the Full backup file, which should be the initial backup file in the displayed list.
d) After defining the location, click on OK to proceed further
e) Next opens a Specify Backup window where you simply have to click on OK button
- Now you will be directed back to the main restoration window where you have to navigate back to ‘Destination for restore’ section, click on the drop-down button to select the database to be restored.
- Its time to enter into the ‘Select the backup sets to restore’ section and choose the backup file that you have chosen in step (4)
- Move the mouse cursor towards the left-hand side of the panel and click on Options
- First of all, you are going to encounter Restore options field where you have to choose any one of the following listed options:
a) Overwrite the Existing Database – It enables administrators to overwrite the data on the already existing database of the server when performing restore operation in absence of the first backup transaction log tail. The term ‘WITH REPLACE’ explains that the SQL server will simply eliminate the active data present in the transaction log file and move further with restoration.
b) Preserve The Replication Settings – This option at the time of restoring database in SQL Server 2008 R2 from BAK file holds the predefined settings of replication. It works when a published database is being restored to the server other than the one where the database was programmed. Also, the respective option is effective to activate only if the server database was replicated at the time of backup creation.
c) Prompt Before Restoring Each Backup – The name of this third option itself specifies its functionality. After restoring each backup file, a ‘continue with restore’ dialog box will appear. This is to check whether you still want to proceed further with restoration or not. The name of the next upcoming media set with name and details of the next backup set will be displayed in this dialog box.
d) Restrict Access to Restored Database – It creates the restored database present only for the members of sysadmin, dbcreator, or db_owner.
- After selecting anyone option in ‘Restore options’ section, now its time to move further with the ‘Recovery state’ section. Here also, you have to select only one of the following options for determining the database state after the store operation.
a) Restore With Recovery – It regains back the database after retrieving the complete backup checked in the sets of Backup to restore it on the General server page.
b) Restore With Nonrecovery – This option leaves the server database in restoring state. It enables you to restore more backups in the recent recovery path. In order to perform database recovery, you have to attempt to restore operation with ‘Restore With Recovery’ option in the final backup to be regained.
c) Restore With Standby – Through this option, recovery process leaves the SQL server database in a standby mode. In this mode, the database is available only for limited read-only use. Selection of this particular option demands for a definition of a standby file in ‘Standby file’ text box. It allows users to undo recovery effects.
- Well, simply for one-time database restoration, we can just choose Overwrite the existing database (WITH REPLACE) option and Restore with recovery option in ‘Restore options’ and ‘Recovery state’ section respectively.
Time to Wind Up The Post
Each and every step to restore database in SQL server 2008 R2 from .bak file is mentioned in this post. We have explained the importance of using options provided in SQL management studio application. Finally, DBAs can read them thoroughly and accomplish their task of database restoration with recovery without any hurdles.
