Today we will discuss about transaction log with backup and recovery
So, let's begin
The importance of the transaction log to both backup and recovery operations.
First we should know what are they ?
-Transaction log is use to save the change of the database, when you update, insert, delete data of the database the change of data will store in the log file and it is separated from the data file.
- Backup is bigger than transaction log, it is a copy of entries database include the data and transaction log.
So why is it importance ?
- Transaction log is use to restore data in some case like the system has error or you check the change of data or restore the data change.
- The backup can help you restore all data at the time you has save the database to it include data and transaction log.
So, now we can see the transaction log and the backup and the importance of them. But there are a few things like this:
- The transaction log always has in the database, it is a part of the database but the backup is not, if you want to have a backup you must plan for it like what you need to store, where you should store, when you must store and why you should store, it really necessary ?
- More than that the frequency of transaction log and the backup is very different, the transaction log always have when some change of the database take place may be in one table or some table of the database, it is smaller than the backup , the backup include the all data of the database and the transaction log, so we can't do that often, it very time-consuming, a large data we must save to the backup, so we can do that often, we should save all data to the backup twice a week, one month,...
- When a real error system has come, every thing in the database has loss, you can easy import the database again with the backup but it more difficult because if the last thing you has change in the database is a insert or delete in one table, it just save the data of that table, you must apply each of the transaction log to restore the database. it very hard right ? One more thing if the system is broken, the backup is useful to recovery the database, but the transaction is not because you can access to the transaction log, the transaction log stored as the same systems as the database.
- But some time the backup is not good enough, example if you have save the data to the backup twice a week, and the error of system has become after one week, so every thing you have change after the last time you have save to the backup will be loss. But with transaction log is different, the transaction log allows you to restore the database to a more recent point in time, potentially minimizing data loss.
- The transaction log and the backup both have advantages and disadvantages, so i think we should use tow of them to protect the database. Minimize the data loss, ensure the data integrity.
If the database transaction log was not present within the database
If that has become true, it is very difficult to restore the data, because the transaction log is use to save the change of the database right a way. It is easy and not take much time like save data to the backup. The transaction log is necessary with the database to guarantee the database when has the error system.
References :
Transaction Log backup || Log file shrink || Difference b/w differential and Log backups || Ms SQL [video]
What is the difference between a database backup and a transaction log backup in SQL Server
https://www.quora.com/What-is-the-difference-between-a-database-backup-and-a-transaction-log-backup-in-SQL-Server
Backup and Restore of SQL Server DataBase
https://learn.microsoft.com/vi-vn/sql/relational-databases/backup-restore/back-up-and-restore-of-sql-server-databases?view=sql-server-2017
The Transaction Log
https://learn.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server?view=sql-server-ver16