Today's Offer - SQL Server Certification Training - Enroll at Flat 10% Off.

- SQL Server Blogs -

How to Restore a Database Backup from SQL Server Management Studio

SQL Server Management Studio is a tool that is used to backup and restores database files. To restore the database you must have the full backup files and transaction logs for which there is a complete procedure. If you will have a complete backup of the database then you can restore that at the time of new database installation. This blog covers the method of restoring database backup files using SQL Server Management Studio tool of Microsoft. You can follow the below-mentioned step by step procedure to restore the database backup files.

Steps to Restore the Database Backup Files

  • You will be having an instance of Microsoft SQL Server Database Engine in the object explorer. Here you may locate your server by its name and when you will click on it then a tree will be expanded.
  • In the next step, there will be Databases in the same panel which will have System Database and User Database. Expand this option and select System Database from there.
  • On this System Database option right click it and take the pointer to Tasks and from right click listed options select Restore option.
  • Click your Database through which the Restore Database dialog box will be opened.
  • In General page, all restoring database names will appear from which you will have to select one from the ‘To database list box’. For restoring purpose you will have to create a new database to create the new one and enter its name
  • In the text box under ‘To a point in time’, you can set the date and time that you can either keep it default or change it through browse button. Point in Time Restore dialog box can be used to set the exact or desired time.
  • Now you can set location and source for the backup sets to restore it. Following options will be listed for this:
    • From database: Here you will have to specify the database name in the list box
    • From device: From Browse button Specify Backup dialog box will open, from where you can specify the Backup Moreover from Backup media list box you can select the listed device types. You can add more devices to Add option.
  • Now you will get the option of Select the backup sets to restore in grid form from here select the desired backups for any specified location. A default recovery plan will be suggested here.
  • From the options in the Select a page pane option, you can access advanced options
  • Now in Restore options following will be listed from which you can select any required option:
    • Overwrite the existing database
    • Preserve the replication settings
    • Prompt before restoring each backup
    • Restrict access to the restored database
  • Through Restore the database file as an option you can restore your database to a new location

If you want to restore the data from an older version database to the latest version then an automatic upgrade option can be selected. In case if you want to restore any encrypted database then you must have the certificate or asymmetric key.

SQL Server Curriculum

Read: What is the SQL Insert Query? How to Insert (Date, Multiple Rows, and Values in Table)

Restoring Database from SSMS

The database can also be restored from another instance. In case if you have to restore any encrypted database then you must have the certification or encryption key of the database that is used to encrypt the database. Without certification or asymmetric key, you cannot restore the database. Even to save the backup file you will have to retain the encryption key. From an older version to the latest version database is upgraded automatically.

In case if both backup and restore processes have to be performed on the same machine then command line process can be used while if both are on separate machines then you should use SQL Server Management Studio that makes the task easier.

Here on this tool the backup or.BAK file will have to be copied on destination machine’s hard drive. Now you can open the SSML tool and log in either with Window administrator or as a user. Now select the Restore Database option as shown in the following window: Restore a Database Backup from SQL From here you will be asked to enter the source device. Then select the path of the source device. After which you will have to specify the File as Backup media option like shown in the following window: Restore a Database Backup from SQL Now browse and select the BAK or backup file that you want to restore like shown in the following window: Restore a Database Backup from SQL Now you will have to select the name of the database in which you want your database should be backed up.  Here there will be two cases:

Read: RDBMS Interview Questions
  • If existing database will be selected then it will be just replaced with the new one
  • If any new database will be selected then it will be created as the fresh one

Now you will have to select the Restore point that you want to use. Here more than one restore point will be listed because SQL backup can restore and hold multiple backup files. Restore a Database Backup from SQL

Here enough information has been entered into database restore option. SQL backup files also store the information about the place where data files will be copied.

SQL Server training

Read: What is SQL Delete Query? How to Delete Duplicate (Records, Rows, Tables)

Conclusion

SQL Server Management Studio makes the process of restoring quite easier. For all the versions you can select the options and make the restoring process easier. Here you will have to set many options for backup and restoring purpose. There are more options and operations that you can perform through this tool like you can retear and backup the transaction log. Database logs can be restored through SSMS tool either from the existing or same machine or also from different machines.

As database backup and restore are the two main options and you can perform these operations easily. Through this tool, you can easily execute the restoring process and perform all the operation,  By specifying the source and the destination.

Read: Top 50 SAS Interview Questions and Answers For Fresher, Experienced

Read Related Article on SQL Server

SQL Tutorial Overview


    Janbask Training

    JanBask Training is a leading Global Online Training Provider through Live Sessions. The Live classes provide a blended approach of hands on experience along with theoretical knowledge which is driven by certified professionals.


Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

4 days 24 Nov 2019

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

5 days 25 Nov 2019

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

5 days 25 Nov 2019

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

6 days 26 Nov 2019

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

14 days 04 Dec 2019

Course for testing

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

34 days 24 Dec 2019

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

13 days 03 Dec 2019

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

5 days 25 Nov 2019

SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

8 days 28 Nov 2019

Comments

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews