• Home
  • DBA Scripts
    • Oracle Scripts
    • SQL Server Scripts
  • Knowledge Base
    • Oracle Database
    • MS SQL Server
    • MongoDB
    • MariaDB
  • Troubleshoot
    • Oracle Database Issues
    • SQL Server Issues
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • Courses
    • Oracle Database
    • Oracle DBA L1
    • Oracle DBA L2
    • Oracle DBA L3
  • Home
  • DBA Scripts
    • Oracle Scripts
    • SQL Server Scripts
  • Knowledge Base
    • Oracle Database
    • MS SQL Server
    • MongoDB
    • MariaDB
  • Troubleshoot
    • Oracle Database Issues
    • SQL Server Issues
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • Courses
    • Oracle Database
    • Oracle DBA L1
    • Oracle DBA L2
    • Oracle DBA L3
home/Knowledge Base/MS SQL Server/Restore Backup in SQL Server
Popular Search:Oracle, SQL Server, MongoDB

Restore Backup in SQL Server

98 views 0 September 7, 2019 April 2, 2021 admin

How to Restore Backup in SQL Server

RESTORES: A MECHANISM TO USE EXISTING BACKUPS AND RECOVER THE DATABASE.

PHASES OF RESTORE:
1. COPY PHASE : CONTENTS OF THE BACKUP FILE ARE COPIED TO SQL SERVER
2. REDO PHASE : COMMITTED TRANSACTIONS FROM THE LOG FILE ARE ROLL FORWARDED [REDO] TO DATA FILE.
3. UNDO PHASE : OPEN TRANSACTIONS FROM THE LOG FILE ARE ROLLEDBACK [UNDO].

TYPES OF RESTORE:
1. FULL DATABASE RESTORE
2. FILEGROUP LEVEL RESTORE
3. FILE LEVEL RESTORE
4. PAGE LEVEL RESTORE

RESTORE OPTIONS:
NORECOVERY : DATABASE IN “RESTORING STATE”, READY FOR FURTHER RESTORES. USE THIS OPTION IF THERE ARE MORE BACKUPS TO RESTORE.
** RECOVERY : DATABASE IS ONLINE STATE, NOT READY FOR FURTHER RESTORES. USE THIS OPTION IF THERE ARE NO BACKUPS TO RESTORE.
STANDBY : DATABASE IS ONLINE STATE, READ ONLY.

RESTORES CAN BE PERFORMED USING:
OPTION 1: USER INTERFACE
OPTION 2: T-SQL SCRIPTS

HOW TO RESTORE A DATABASE ?
LAUNCH SSMS > CONNECT TO THE SERVER > RIGHT CLICK DATABASES > RESTORE > SPECIFY THE BACKUP FILE PATH > OK.

HOW TO RESTORE A DATABASE TO A DIFFERENT FILE PATH ?
LAUNCH SSMS > CONNECT TO THE SERVER > RIGHT CLICK DATABASES > RESTORE > SPECIFY THE BACKUP FILE PATH >
FILES > RELOCATE / REPOSTION THE DATA FILES AND LOG FILES TO REQUIRED LOCATION(S) > OK.

HOW TO PERFORM PITR = POINT-IN-TIME RECOVERY ?
LAUNCH SSMS > CONNECT TO THE SERVER > RIGHT CLICK DATABASES > RESTORE > SPECIFY THE BACKUP FILE PATH >
TIMELINE > SPECIFY THE REQUIRED TIMELINE > OK.

HOW TO RESTORE A DATABASE IN A STANDBY (READ ONLY) FORMAT ?
LAUNCH SSMS > CONNECT TO THE SERVER > RIGHT CLICK DATABASES > RESTORE > SPECIFY THE BACKUP FILE PATH >
OPTIONS > SPECIFY “STANDBY” IN THE RESTORE OPTIONS > OK. THIS AUTO CREATES ON STANDBY BACKUP FILE. DATABASE IN READ ONLY STATE.

HOW TO RESTORE DATABASES IN PARTS ? PARTIAL RESTORE OR PIECE-MEAL RESTORE ?
THIS OPTION IS POSSIBLE ONLY USING T-SQL SCRIPTS

SCENARIO : ASSUME BANKING DATABASE CRASHED. YOU NEED TO RESTORE THE ACCOUNTS FILEGROUP ASAP.

SOLUTION:
STEP 1: RESTORE METADATA
STEP 2: RESTORE REQUIRED FILEGROUPS
STEP 3: RESTORE THE LOG FILE [IF ANY], GET DATABASE ONLINE RECOVERY

DURING LATER POINT TIME::
STEP 4: CONVERT ONLINE DATABASE INTO RESTORING STATE NORECOVERY
STEP 5: RESTORE THE REMAINING PART(S) OF THE DATABASE
STEP 6: RESTORE THE REQUIRED LOG BACKUP(S). GET DATABASE ONLINE RECOVERY

Tags:recovery

Was this helpful?

Yes  No
Related Articles
  • MS SQL Server Notes
  • Important Counters (perfmon.exe)
  • Restoring Backup via Scripts
  • SQL Server JOBS
  • Backup using T-SQL
  • Steps to Take Backup from GUI in SQL Server
Leave A Comment Cancel reply

MS SQL Server
  • Restore Backup in SQL Server
  • MS SQL Server Notes
  • Important Counters (perfmon.exe)
  • Restoring Backup via Scripts
  • SQL Server JOBS
  • Backup using T-SQL
View All 20  
Popular Articles
  • Upgrade Oracle Database from 11.2.0.3 to 12.1.0.2
  • Upgrading MariaDB on Windows
  • Oracle DBA Basic Interview Questions Part A
  • Purging Oracle Sysaux Tablespace
  • Client – Server Architecture
KB Categories
  • Oracle Database
    • Oracle RAC
    • Oracle ASM
    • Oracle GoldenGate
    • Oracle Tuning
    • Oracle 11g Database
    • Oracle Database Upgrade
    • Oracle 12c Database
    • ALL KB Oracle
    • Oracle 18c Database
    • Oracle Standby Database
  • MongoDB
  • MS SQL Server
  • MySQL
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • MariaDB
Database Organization

Database Organization (DB ORG) is knowledge base for DBA to learn and execute the fundamental of different databases under one website. DB ORG is a non-profit initiative. ORACLE, MS SQL Server, MongoDB, MariaDB, Couchbase

Join Our Community
  • KnowledgeBase
  • Documentation
  • Troubleshoot
  • FAQ
Information Links
  • About DBOrg
  • Licenses
  • Terms
  • Privacy Policy
Contact Us
    DB ORG - Database Administration,
    Knowledge Base for DBA
    Mail: support@databaseorg.com
    WhatsApp: (+91) 9306440957
    Monday to Friday: EST - 11:30 AM to 06:30 PM (IST - 9:00 PM to 4:00 AM)
  • © 2023 Database Organization - DB ORG. All Rights Reserved.

Popular Search:Oracle, SQL Server, MongoDB

WhatsApp DB Org