Unit Three: � Database Recovery� Points & Procedures

short note for this all ppt

1. Unit Three: Database Recovery Points & Procedures Name:Aemayehu & Aster Date:Monday , July 8, 2024:

2. Database recovery point Definition Database recovery is the process of restoring the database to a correct (consistent) state in the event of a failure. It is the process of restoring the database to the most recent consistent state that existed shortly before the time of system failure.

3. There are many situations in transaction may not reach a commit or abort point. Some of them include; An operating system crash can terminate the DBMS processes The DBMS can crash System failure(e.g. power outage) Affects all transactions currently in progress but does not physically damage the data (softcrash)

4. 5) Media failures(e.g. Head crash on the disk) damage to the database (hard crash) need backup data 6) The system might lose power 7) Human error can result in deletion of critical data. In any of these situations, data in the database may become inconsistent or lost.

5. When a DBMS recovers from a crash , it should maintain the following It should check the states of all the transactions , which were being executed. A transaction may be in the middle of some operation; the DBMS must ensure the atomicity of the transaction in this case . It should check whether the transaction can be completed now or it needs to be rolled back. No transactions would be allowed to leave the DBMS in an inconsistent state .

6. In case of any type of failures, a transaction must either be aborted or committed to maintain data integrity. Transaction log plays an important role for database recovery and bringing the database in a consistent state in the event of failure.

7. Transactions represent the basic unit of recovery in a database system. The recovery manager guarantees the atomicity and durability properties of transactions in the event of failures. A transaction begins with successful execution of a Dbms executes a begin transaction system often abbreviated as begin <T, BEGIN>” (begin transaction) statement.

8. Database Recovery Techniques For fast restoration or recovery of data, the database must hold tools which recover the data efficiently. It should have atomicity means either the transactions showing the consequence of successful accomplishment perpetually in the database or the transaction must have no sign of accomplishment consequence in the database.

9. So, recovery techniques which are based on deferred update and immediate update or backing up data can be used to stop loss in the database.

10. Immediate Update : As soon as a data item is modified in cache, the disk copy is updated. Deferred Update : All modified data items in the cache are written either after a transaction ends its execution or after a fixed number of transactions have completed their execution.

11. Shadow update: The modified version of a data item does not overwrite its disk copy but is written at a separate disk location. In-place update : The disk version of the data item is overwritten by the cache version.

12. Two approaches of Recovery 1. Manual Reprocessing In a Manual  Reprocessing recovery approach , the database is periodically backed up (a database  save ) and all transactions applied since the last save are recorded If the system crashes, the latest database backup set is restored and all of the transactions are re-applied (by users) to bring the database back up to the point just before the crash .

13. Several shortcomings to the Manual Reprocessing approach: Time required to re-apply transactions Transactions might have other (physical) consequences Re-applying concurrent transactions in the same original sequence may not be possible.

14. 2. Automated Recovery with Rollback / Roll forward Introduce a Log file – this is a file separate from the data that records all of the changes made to the database by transactions. The transaction log records key details to aid recovery: Transaction ID Timestamp User information Before and after images of data changes These logs enable rollback of incomplete transactions and replay of committed ones.

15. Before Image : A copy of the table record (or data item) before it was changed by the transaction. After Image : A copy of the table record (or data item) after it was changed by the transaction. The Automated Recovery process uses both rollback and roll forward to restore the database.

16. 4) Rollback : Undo any partially completed transactions (ones in progress when the crash occurred) by applying the  before images  to the database. UNDO the transactions in progress at the time of failure. 5) Roll forward : Redo the transactions by applying the  after images  to the database. This is done for transactions that were committed before the crash. REDO the transactions that successfully complete but did not write to the physical disk. 6) Checkpoint is a mechanism where all the previous logs are removed from the system and stored permanently in a storage disk. Checkpoint declares a point before which the DBMS was in consistent state, and all the transactions were committed. Checkpoints  can also be taken (less time consuming) in between database saves.

17. the steps for recovery using the checkpoint and transaction log: STEP 1: Initialize the recovery process UNDO-list = list of transactions from the checkpoint record = {T2, T3} REDO-list = empty STEP 2: Search forward through the log, starting from the checkpoint If a 'BEGIN TRANSACTION' is found, add it to the UNDO-list = {T2, T3, T4, T5}

18. If a 'COMMIT' is found, remove the transaction from the UNDO-list and add it to the REDO-list UNDO-list = {T3, T5} REDO-list = {T2, T4} STEP 3: Redo the transactions in the REDO-list Redo the transactions in the REDO-list in the order they were added: T2, T4 STEP 4: Undo the transactions in the UNDO-list Undo the transactions in the UNDO-list in reverse order: T5, T3

19. SO: Initialize UNDO-list from checkpoint, REDO-list is empty Scan forward, adding new transactions to UNDO-list, moving committed ones to REDO-list Redo the transactions in the REDO-list in order Undo the transactions in the UNDO-list in reverse order This recovery process uses the checkpoint and log to restore the database to a consistent state.

20. Testing restore process Database recovery testing is used to ensure that a database can be successfully restored and recovered in the event of a failure or data loss.

21. Backup testing is critical to ensure a smooth data recovery process. The main goal is to verify that the business can retrieve its data and continue operations. Businesses should test that they can restore: Individual files Folders Entire volumes Backup testing should be a regular and routine process, though testing every single backup may not be practical.

22. Common Steps in Database Backup and Recovery Testing The common actions performed in Database Recovery Testing: Testing of database system Testing of the SQL files Testing of partial files Testing of data backup Testing of Backup tool Testing log backups These policies should set out the recovery point objective (RPO) and the recovery time objective (RTO) .

23. Restore a database to a point in time The main benefits of point-in-time recovery are: Ability to undo mistakes or data loss by restoring to a previous functional state. Protects against human errors, software bugs, and other issues that can corrupt data. Minimizes data loss and downtime compared to a full database restore from the last backup. Provides an extra layer of protection for critical business data and applications

24. Recovery Model Requirements: Point-in-time recovery requires the database to be in either the Full or Bulk-Logged recovery model. It is not possible in the Simple recovery model. Bulk-Logged Model Limitations: When using the Bulk-Logged recovery model, point-in-time recovery may fail. This is because bulk-logged operations are minimally logged, so there may not be sufficient data in the transaction logs. An error will be thrown if any bulk-logged operations were performed.

25. RESTORE Command: When issuing a RESTORE DATABASE or RESTORE LOG command, the WITH RECOVERY option is used by default. Full backup: RESTORE WITH RECOVERY (default) Multiple backups: RESTORE WITH NORECOVERY (except last) Restoring state: RESTORE DATABASE WITH RECOVERY

26. Use WITH NORECOVERY when restoring multiple backups to update the database. Allows you to restore backups before final recovery. Commonly used for Full backup plus Transaction Log backups. Forgetting NORECOVERY means restarting the full restore process.

27. Restore a database using T-SQL Restore full backup and one transaction log backup The first command does the restore and leaves the database in a restoring state and second command restores the transaction log backup and then makes the database useable . RESTORE DATABASE database_name FROM DISK = ' full_backup_file.bak'WITH NORECOVERY

28. The steps to restore a database using the NORECOVERY option: Restore the full database backup: RESTORE DATABASE database_name FROM DISK = 'full_backup_file.bak' WITH NORECOVERY This leaves the database in a restoring state. Restore the transaction log backup: RESTORE LOG database_name FROM DISK = 'log_backup_file.bak' WITH RECOVERY This applies the transaction log backup and makes the database usable. Use NORECOVERY on the first restore to leave it in a restoring state Then use RECOVERY on the log restore to bring the database online

29. Restoring a database with a full backup and two transaction log backups: Restore the full database backup: RESTORE DATABASE << DatabaseName >> FROM DISK = 'C:\BackupName.BAK' WITH NORECOVERY GO This restores the full backup and leaves the database in a restoring state. Restore the first transaction log backup: RESTORE LOG << DatabaseName >> FROM DISK = 'C:\BackupName.TRN' WITH RECOVERY GO This applies the first transaction log backup and makes the database usable.

30. Restore the second transaction log backup: RESTORE LOG << DatabaseName >> FROM DISK = 'C:\BackupName1.TRN' WITH RECOVERY GO This applies the second transaction log backup. SO: Restore full backup with NORECOVERY Restore first log backup with RECOVERY Restore second log backup with RECOVERY

31. Restore full backup, latest differential and two transaction log backups This restores the first three backups using NORECOVERY and then RECOVERY for the last restore .

32. database with a full backup, a differential backup, and two transaction log backups: Restore the full database backup: RESTORE DATABASE << DatabaseName >> FROM DISK = 'C:\BackupName.BAK' WITH NORECOVERY GO This restores the full backup and leaves the database in a restoring state. Restore the differential database backup: RESTORE DATABASE << DatabaseName >> FROM DISK = 'C:\BackupName.DIF' WITH NORECOVERY GO This restores the differential backup and keeps the database in a restoring state.

33. Restore the first transaction log backup: RESTORE LOG << DatabaseName >> FROM DISK = 'C:\BackupName.TRN' WITH NORECOVERY GO This applies the first transaction log backup and keeps the database in a restoring state. Restore the second transaction log backup: RESTORE LOG << DatabaseName >> FROM DISK = 'C:\BackupName1.TRN' WITH RECOVERY GO This applies the second transaction log backup and makes the database usable.

34. SO: Restore full backup with NORECOVERY Restore differential backup with NORECOVERY Restore first log backup with NORECOVERY Restore second log backup with RECOVERY

35. The document discusses database recovery procedures, emphasizing the importance of maintaining data integrity through rollback and roll forward techniques. It covers automated recovery using transaction logs to restore databases to consistent states after failures. The process involves restoring databases to specific points in time, ensuring smooth data recovery. Additionally, it highlights the significance of testing the restore process and backup testing for database recovery. The document also outlines the requirements for point-in-time recovery in specific database recovery models. It provides step-by-step instructions for restoring databases using T-SQL commands, focusing on rollback and forward actions to ensure database integrity.