May 12, 2009 at 3:04 am
Hi all,
I have some query in database backup.
I am taking full database backup everday once and differential backup every one hour. If I take transaction log backup instead of differential backup, will it make any difference from restoring point of view?
According to me it will not. Please let me know if I am correct.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
May 12, 2009 at 3:27 am
If you have full and diff backups you need to restore the latest full backup and the latest diff backup after the full backup. That means 2 backups to restore.
With Log backups instead of diff backups you need to restore latest full backup and all log backups after the full backup.
Edit - Having log backups help truncate the transction log.
"Keep Trying"
May 12, 2009 at 6:25 am
It will change the restore operations, as outlined above.
You need to determine the level of recovery that the business needs. Currently, with a full backup and an incremental backup once an hour, you could lose up to an hour of data if something went wrong and you had to do a restore. You should probably be adding in log backups in addition to the two backups you're currently running. For example, add a log backup once every five minutes and only retain the backups for one hour so that you're covered in between incremental backups. That's just an example, there's other ways you could put it together.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 13, 2009 at 8:34 am
Thanx,Grant for very useful and practical approach.
We also have to keep in mind that in case of transactional backups:-
1.It is a must to restore them SEQUENTIALLY as they occur.
2. ALL transactional backups ( after the last Differential/Full backups) are required to restore the database.
And frequency of T. backups can be adjusted as per your needs or requirement for in time restore.
🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply