Your backup and recovery strategy should minimize the potential for data loss and maximize data availability. At the same time, it should reflect specific business requirements, and be able to adapt according to resource availability. Backups cannot be used by themselves.
Instead, they should be used along with full and differential SQL Server backups differential backup is optional but strongly recommended.
The full backup serves as the starting point for subsequent differential and transaction log backup, otherwise they won't be created. The main feature of any backup and disaster recovery plan is the ability to recover fast. But what exactly is a fast recovery? The business decision makers would want you to be able to recover the database to the nearest possible state in time, but the fact of the matter is, that you are not always able to perform backups at the wanted schedule.
Thus, to define the right schedule for your backups, you should get to the drawing board and define the essential disaster recovery metrics, the recovery time and the recovery point objectives. These will allow you to define, how critical your MS SQL data is, and propose the backup technique and frequency, that will be optimal for your business case.
If your business requires you to minimize your risk of data loss, perform log backups more frequently. Doing so offers the added advantage of increasing the frequency of log truncation, resulting in smaller log files. Use transaction log backups for point-in-time recovery and maximum data protection in case of a database disaster. If a disaster occurs, you will first recover the full database backup, and then recover the last differential backup.
After that, you can restore the necessary log backups in sequence up to the needed point in time. This is an important feature for point-in-time restores, as it allows the administrator to restore a database to a point just before a problem occurred.
To limit the number of log backups that you need to restore, it is necessary to routinely back up your data. For example, schedule a weekly full database backup and daily differential database backups. A database administrator typically creates a full database backup occasionally, such as weekly, and, optionally, creates a series of differential database backup at a shorter interval, such as daily.
Independent of the database backups, the database administrator backs up the transaction log at frequent intervals. For a given type of backup, the optimal interval depends on factors such as the importance of the data, the size of the database, and the workload of the server.
For more information about implementing a good strategy, see Recommendations in this topic. The sequence of transaction log backups log chain is independent of data backups. For example, assume the following sequence of events. The transaction log backup created at PM contains transaction log records from PM through PM, spanning the time when the full database backup was created at PM The sequence of transaction log backups is continuous from the initial full database backup created at AM to the last transaction log backup created at PM.
If a transaction log is damaged, work that is performed since the most recent valid backup is lost. Therefore we strongly recommend that you put your log files on fault-tolerant storage.
If a database is damaged or you are about to restore the database, we recommend that you create a tail-log backup to enable you to restore the database to the current point in time. By default, every successful backup operation adds an entry in the SQL Server error log and in the system event log. The size of each virtual log file is not fixed. As far as simple recovery model is concerned, the transaction log truncation takes place after each checkpoint.
A transaction log backup chain is a continuous sequence of transaction log backups starting with first full database backup.
Each backup from the chain has its own FirstLSN oldest log record in the backup set and LastLSN the number of the next log record after the backup set , that help to restore transaction log backup files in the right sequence.
Here are the two of such:. On the other hand, here are the other two common instances in which the log backup chain is not broken:. If your database is set to the "Full" or "Bulk-logged" recovery model then you will be able to issue "Transaction Log" backups.
By having transaction log backups along with full backups you have the ability to do a point in time restore, so if someone accidently deletes all data in a database you can recover the database to the point in time right before the delete occurred.
The only caveat to this is if your database is set to the "Bulk-logged" recovery model and a bulk operation was issued, you will need to restore the entire transaction log, so you cannot do a point in time restore using a transaction log backup that contains a bulk-logged operation. A transaction log backup allows you to backup the transaction log.
0コメント