Database Systems with SQL - Full Course - Database Systems - Backup and Recovery, Log Files, and Failure Types

A database recovery log file contains a record history of all database operations. The log is stored on a different storage device than the database so it is accessible in the case of a storage media failure. After a failure, the database recovery system can use the log to restore the database. The log contains: Update record – logs that a tradnsaction data has changed or updated Compensation record (undo record) – logs that data had rollback Transaction record – logs the start, commit and rollback of a transaction Checkpoint record – logs when all data is saved from main memory to storage media. Failure types Database recovery supports atomic transactions by making sure that partial transactions are not saved, and also supports durable transactions by making sure that completed and committed transactions are not lost if the hardware or database fail. The following are 3 failure types: Transaction failure – Data rollback to original values if there are logical errors, deadlocks, hardware failure, or even if the database ran out disk space. If there is a transaction failure, the database concurrency system tells the recovery system to rollback the transaction. The recovery system does a rollback by reading the recovery log file starting from the end of the file, as it contains the latest records. System failure – Data blocks are stored on media storage periodically stored on hard disk or other storage media, which are safe in the case of a system failure. If the systems main memory is lost before the blocks get written to storage, the recovery system can recover the committed transactions, and also rollback uncommitted transactions from storage. Recovery from a system failure has 2 phases: the redo phase which restores transactions that were committed or rolled back since the last checkpoint, and the undo phase which reads the recovery log file in reverse, and figures out how to undo any issues and recover the database. Storage media failure – This is when your database connection is lost or your database file gets corrupted. Most database systems automatically make a backup of your data so that you can recover the data. However, if you have this issue then you will not be able to connect to your database. The recovery system restores any committed transaction and rollbacks the uncommitted transactions. But if your system was down for a long time, most databases provide some kind of media failure recovery. There are 2 ways to recover from storage media failures: A cold backup creates checkpoints and database backups at periodic times such as when there is low database activity, for example at 2:00am. A hot backup keeps up a secondary database that is in sync with the primary one, and if the primary fails, then just set the secondary to the new primary. Subscribe to Appficial for more programming videos coming soon. Also, don't forget to click LIKE and comment on the video if it helped you out!