1. Full backup is the base for other two types of backup. Differential and transactional backup can not be taken until we not take one Full backup of database. We can take Full backup any time.
2. Differential backup is based on last Full backup.
3. Transactional backup is based on last Full or Differential or transactional backup.
![clip_image002[4]](http://sqlreality.com/blog/wp-content/uploads/2010/01/clip_image0024.gif "clip_image002[4]")
Now if you want to restore the database in the consistency state of time 12:00 PM. Following are different cases and there results:
Restore from Scheduled backup sequence:
Case 1: Full 1 > TRAN 1 > TRAN 2 --- will return error.
Case 2: FULL 1 > DIFF 1 > TRAN 3 --- will return error.
From manual sequence:
FULL 2 > TRAN 4 --- will return error.
The only sequence that will work is following:
FULL2 > DIFF1 > TRAN4 > TRAN3
Even you have two backup policies but no one worked independently. Problem here is that one policy is breaking another one. If you have setup log shipping then that would also fail because of manual backups.
This problem is resolved in SQL Server 2005 by introducing the COPY_ONLY option. If we take all the manual backups using the COPY_ONLY option then we would be able to restore the database in both sequences (case1 and case2). The COPY_ONLY option is only required with FULL and Transactional backups. The syntax is as following.
BACKUP DATABASE database_name TO <backup_device> … WITH COPY_ONLY …
BACKUP LOG database_name TO <backup_device> … WITH COPY_ONLY …
2 comments:
The topic you have chosen is good.
Appreciate for that.
Small correction, you can use Full2 > Diff1 > Tran4 > Tran3. This sequence can be used to restore the database in the consistency state of time 12:00 PM.
Please correct the example. Example reflects the Content and Quality of your writing. :)
Hello Sandesh.
I appreciate your deep understanding on this topic. I have updated the post. Thank you for your correction.
Regards,
Suhhash
Post a Comment