Ayman Elnory (Sr. DBA) | Updated on August 17, 20trăng tròn | Toolkit for MS Squốc lộ | 1 Comment | 6 min read Summary: No doubt that the backup and restore the database is the main task of any Squốc lộ Server DBA. We will discuss here the various ways to lớn restore the database in Squốc lộ Server 2008 R2.Quý Khách đã xem: Hướng dẫn backup/restore tệp tin bak trong sql server


Permissions needed:

If the database being restored does not exist, the web12_user must have CREATE DATABASE permissions to lớn be able to lớn exedễ thương RESTORE. If the database exists, RESTORE permissions default khổng lồ members of the sysadmindbcreator fixed VPS roles & the owner (dbo) of the database.

Bạn đang xem: Hướng dẫn restore file bak trong sql server 2008

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

RESTORE permissions are given to lớn roles in which membership information is always readily available to lớn the server. Because fixed database role membership can be checked only when the database is accessible & 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:

xuất hiện Microsoft Squốc lộ Server Management Studio, và navigate to Databases:


Right-cliông xã Databases, và click Restore Database


Here, in the first section (Destination for restore)

If the database being restored does not exist, bởi vì not select any database in the combo box (To database) as it will be updated automatically once you select the backup tệp tin.If you are restoring from a backup device, you can leave sầu it as default (Most recent possible) to lớn be restored khổng lồ the last backup time.Or choose what time you need to restore the Database (You have sầu the option to lớn 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 khổng lồ copy that database khổng lồ the new one using the previous backups.Or, you can select from device khổng lồ restore from backup device (.bak file). Then Clichồng Add in the Specify Backup window. Browse lớn the location of your recently restored flat files. Choose the Full backup tệp tin which should be the first backup file in the list:


Click OK; the Specify Backup window displays:

Cliông chồng OK. In the Destination for restore section, select the database to lớn which you wish to restore, and in the Select, the backup sets lớn restore section, select the backup file you selected above:

In the left pane, click Options, & 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 Squốc lộ Server lớn just throw out any active sầu contents in the transaction log and move forward with the restore.

In some cases when you try khổng lồ do a restore without replace option you may get an error that says

" Msg 3159, Level 16, State 1, Line 1The tail of the log for the database "AdventureWorks" has not been backed up. Use BACKUPhường LOG WITH NORECOVERY to backup the log if it contains work you vày not want to lớn đại bại. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to lớn just overwrite the contents of the log.Msg 3013, Level 16, State 1, Line 1RESTORE DATABASE is terminating abnormally. "Preserve sầu the replication web12_settings (WITH KEEP_REPLICATION):It preserves the replication web12_settings when restoring a published database khổng lồ a hệ thống 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 khổng lồ inquire whether you want to continue the restore sequence. This dialog box displays the name of the next truyền thông mix (if known) & the name và mô tả tìm kiếm of the next backup phối.

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 truyền thông media sets; for example, when your VPS has only one tape device. When you are ready to lớn proceed, cliông chồng OK.

Restrict access khổng lồ the restored database (WITH RESTRICTED_USER):

In the recovery state section:

You must select one of the following options khổng lồ determine the state of the database after the store operation:

Leave the database ready lớn use by rolling baông xã 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 lớn restoregrid on the General page.

Xem thêm: Tổng Hợp Và Hướng Dẫn Up Rom Tiếng Việt Xiaomi Redmi Note 2 N7100

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 khổng lồ restore additional backups in the current recovery path. To recover the database, you will have sầu khổng lồ perform a restore operation by using the RESTORE WITH RECOVERY option in the last backup to lớn be restored.

If you select this option, the Preserve sầu replication web12_settings option is unavailable.

Leave the database in read-only mode. Unvày uncommitted transactions but save sầu 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 tệp tin in the Standby tệp tin text box. The standby tệp tin allows the recovery effects to lớn be undone.

So for just one restore for the database, we can just select Overwrite the existing database (WITH REPLACE) option and Leave sầu the database ready lớn use by rolling baông chồng uncommitted transactions. Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY) option.

All of the above sầu steps can be done in the happy scenario, But you may have sầu a corrupted backup file. In that case, you vị not have any option lớn restore this backup then, you can use Stellar Toolkit for MS Squốc lộ.

You would lượt thích to read on what SQL MVP.. Damian Widera says about the tool và his critical Review regarding it.

Stellar Toolkit for MS Squốc lộ is a combination of three tools developed to lớn repair corrupt Squốc lộ VPS database, extract database from corrupt SQL backup file & to reset SQL Server web12_user and Admin Password.


When we say back và 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 Squốc lộ hệ thống 2008 restore. I hope this article has been informative for you.