Restoration of SQL server Database Backup using GUI

Step 1:  Connect to DB ---> Right click on database ---> Restore database

Step 2: On the General page  under source section Select one of the following options:

Database

Select the database to restore from the drop-down list. The list contains only databases that have been backed up according to the msdb backup history.

Device
Click the browse (...) button to open the Select backup devices dialog box. In the Backup media type box, select one of the listed device types.

Step 3:  To select one or more devices for the Backup media box, click Add.

After adding backup files below window will appears which contains  DB name , DB type , LSN number etc.

Step 4:  Click Files in the Select a page pane to access the Files dialog box.

select new location for database files , if required. by clicking on (...) button

Step 5 : select the advanced options, on the Options page, in the Restore options panel, you can select any of the following options, if appropriate for your situation:

Overwrite the existing database [WITH REPLACE]

The restore operation will overwrite the files of any database that is currently using the database name.

Preserve the replication settings [WITH KEEP_REPLICATION]

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 , which is equivalent to restoring a backup with the RECOVERY option.

Restrict access to the restored database [WITH RESTRICTED_USER]

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

Step 6 : Select an option for the Recovery state box. This box determines the state of the database after the restore operation.

RESTORE WITH RECOVERY is the default behavior which leaves the database ready for use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored. Select this option if you are restoring all of the necessary backups now.

RESTORE WITH NORECOVERY which leaves the database non-operational, and does not roll back the uncommitted transactions. Additional transaction logs can be restored. The database cannot be used until it is recovered.

RESTORE WITH STANDBY which leaves the database in read-only mode. It undoes uncommitted transactions, but saves the undo actions in a standby file so that recovery effects can be reverted.

Click on next to start restoration.

Thats It :)



More :-
➤ Restoration of SQL server Database Backup using T-SQL