How to restore database in SQL Server 2008 R2 from .bak file

Summary: No doubt that the backup and restore the database is the main task of any SQL Server DBA. We will discuss here the various ways to restore the database in SQL Server 2008 R2.

Permissions needed:

If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database.

(for the FROM DATABASE_SNAPSHOT option, the database always exists).

RESTORE permissions are given to roles in which membership information is always readily available to the server. Because fixed database role membership can be checked only when the database is accessible and undamaged, which is not always the case when RESTORE is executed, members of the db_owner fixed database role do not have RESTORE permissions.

Restore database in SQL Server 2008 R2:

We can use the following steps to restore the database:

1. Open Microsoft SQL Server Management Studio, and navigate to Databases:

2. Right-click Databases, and click Restore Database

Here, in the first section (Destination for restore)

• If the database being restored does not exist, do not select any database in the combo box (To database) as it will be updated automatically once you select the backup file.
• If you are restoring from a backup device, you can leave it as default (Most recent possible) to be restored to the last backup time.
  Or choose what time you need to restore the Database (You have the option to select desired Specific time to restore the database)

Then, in the second section (Source for restore)

• If you selected from database option, this will list all backups taken for that database before. So, you can choose to copy that database to the new one using the previous backups.
• Or, you can select from device to restore from backup device (.bak file). Then Click Add in the Specify Backup window. Browse to the location of your recently restored flat files. Choose the Full backup file which should be the first backup file in the list:

• Click OK; the Specify Backup window displays:

3. Click OK. In the Destination for restore section, select the database to which you wish to restore, and in the Select, the backup sets to restore section, select the backup file you selected above:

4. In the left pane, click Options, and you can select one of the following:

In the Restore options section:

you can select one of the following options:

• Overwrite the existing database (WITH REPLACE):

It allows you to write over an existing database when doing a restore without first backing up the tail of the transaction log.  The WITH REPLACE basically tells SQL Server to just throw out any active contents in the transaction log and move forward with the restore.

In some cases when you try to do a restore without replace option you may get an error that says

" Msg 3159, Level 16, State 1, Line 1
The tail of the log for the database "AdventureWorks" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally. "

• Preserve the replication settings (WITH KEEP_REPLICATION):

It preserves the replication settings when restoring a published database to a server other than the server where the database was created. This option is relevant only if the database was replicated when the backup was created.

This option is available only with the Leave the database ready for use by rolling back the uncommitted transactions option (described later in this table), which is equivalent to restoring a backup with the RECOVERY option.

• Prompt before restoring each backup:

That specifies that after each backup is restored, the Continue with Restore dialog box will be displayed to inquire whether you want to continue the restore sequence. This dialog box displays the name of the next media set (if known) and the name and description of the next backup set.

This option allows you to pause a restore sequence after restoring any of the backups. This option is particularly useful when you must swap tapes for different media sets; for example, when your server has only one tape device. When you are ready to proceed, click OK.

• Restrict access to the restored database (WITH RESTRICTED_USER):

It makes the restored database available only to the members of db_owner, dbcreator, or sysadmin.

In the recovery state section:

You must select one of the following options to determine the state of the database after the store operation:

• Leave the database ready to use by rolling back uncommitted transactions. Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY):

It recovers the database after restoring the final backup checked in the Backup sets to restoregrid on the General page.

• Leave the database non-operational and do not roll back uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY):

This leaves the database in the restoring state. This allows you to restore additional backups in the current recovery path. To recover the database, you will have to perform a restore operation by using the RESTORE WITH RECOVERY option in the last backup to be restored.

If you select this option, the Preserve replication settings option is unavailable.

• Leave the database in read-only mode. Undo uncommitted transactions but save the undo actions in a standby file so that recovery effects can be reversed. (RESTORE WITH STANDBY):

This leaves the database in a standby state, in which the database is available for limited read-only access.

Choosing this option requires that you specify a standby file in the Standby file text box. The standby file allows the recovery effects to be undone.

So for just one restore for the database, we can just select Overwrite the existing database (WITH REPLACE) option and Leave the database ready to use by rolling back uncommitted transactions. Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY) option.

All of the above steps can be done in the happy scenario, But you may have a corrupted backup file. In that case, you do not have any option to restore this backup then, you can use Stellar Toolkit for MS SQL.

You would like to read on what SQL MVP Damian Widera says about the tool and his critical Review regarding it.

Stellar Toolkit for MS SQL is a combination of three tools developed to repair corrupt SQL server database, extract database from corrupt SQL backup file and to reset SQL Server user and Admin Password.

Conclusion

When we say back & restore the database in SQL Server 2008 R2, which is one of the mandatory tasks for each DBA, we have discussed here some of the options were existed in SQL server 2008 restore. I hope this article has been informative for you.