In the example, shown in picture 2, an unscheduled full database backup is taken at 9 AM. We can use any other available full backup, provided that we have available all transaction log backups created after that full backup, up to recovery point. This doesn’t mean that we cannot use the last full database backup as a starting point when performing point in time recovery. The only exception to this rule is the first full database backup taken after the database is created. This is logical, since a full database backup operation doesn’t truncate the transaction log of a database, and all transaction log backups contain transactions committed since previous transaction log backup only. Unlike in the previous case, taking a full database backup outside of the backup schedule won’t affect the transaction log backup chain. Storing and maintaining log backups requires additional resources, but once the process gets automated, the benefits often outweigh the effort. This also brings us to the main drawback of the full recovery model: it is necessary to perform regular transaction log backups to prevent uncontrolled growth of the database’s transaction log. One of the greatest advantages of full recovery model databases is the ability to recover a database to a specified point in time, provided that all recovery resources are available. Picture 1: Effects of unscheduled full database backup on the differential log backup chain Database uses full recovery model
#Backup server syncovery manual
In this case, if 6 AM manual full database backup got lost, all further differentials would be useless, and recovery would not be possible for the time up until the next full backup. Therefore, the 12 AM full backup cannot be used to restore 7 AM differential backup. All differentials created after the manual backup at 6 AM use this backup file as a base. All differentials created before 6 AM use the 12 AM backup as their base.
This manual database backup ended the backup chain that starts with the full backup file at 12 AM and started the new chain. Unfortunately, the restore operation failed with the following message: “This differential backup cannot be restored because the database has not been restored to the correct earlier state”.įurther investigation of backup history showed that another full backup was taken that day at 6 AM. The disaster occurred around 7:30 AM, and DBAs reasonably decided to restore the last differential database backup that was created at 7:00 AM. This way, the RPO of one hour is ensured. The full database backup is taken daily (at midnight for example) and regular differential backups are taken every hour. Let’s look at the few examples where manual database backup performed out of the regular schedule affects the daily routine: But using this approach can prove not always helpful, in the case of a disaster, if we need to restore a backup file that is created after this manual out-of-the-schedule backup operation. These backup files can be created manually, in a regular way, with the usual BACKUP DATABASE command. There are many situations when backup files need to be created outside of the regular backup schedule. By setting the backup schedule, the continuity of the backup process is ensured, and most of the job is performed automatically on a regular basis. Regular database backup schedules are set in order to create continuous backup chains, that can later be used to recover a database in the case of a disastrous event. Most DBAs tend to automate the majority of tasks related to database backup plans. After the Recovery Point Objective (RPO) and Recovery Time Objective (RTO) are defined, a database backup plan should be created to match these objectives. Creating a foolproof disaster recovery solution is imperative for every business.
0 kommentar(er)
