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