January 5, 2009 at 2:20 am
Hi,
I have doubt please tell me suggestion.
My production database backup strategies as follows
1. Daily Midnight taking full backup.
2. Daily Midday taking differential backup.
3. Every off on hours taking transaction log backup.
suppose database crashed on after taking differential backup at time of 3:00 PM, database crashed time was on 3:20 PM. So in this situation how can recovery database?
I need go for restoring first full backup with norecovery option. second thing i need restore the Tlog backup one by one with norecovery and finally restore differential backup with recovery. so how can i recovery 3:00PM to 3:20 PM data? plz give me suggestion
Thanks
January 5, 2009 at 2:47 am
Hi,
First of all how frequently do you backup the transaction log? It is not clear defined.
Secondly, what do you mean when you say "the system crashed"? The server is down, the database is unavailable? The solution depends on the error that happened.
The sequence for restoring is the following:
- Restore from full backup with norecovery
- Restore from differential backup to 3 pm with norecovery
- Restore the logs that have been backed up between 3 pm and 3:20 pm. Only the last transaction log backup must be restored with recovery. If there is no transaction log backup you have possibly lost 20 minutes of data.
Regards,
PSA
January 5, 2009 at 3:04 am
Thanks for your reply....
i am getting clear but my production database TLog Every 30 Min, I recvoery data up last transaction backup at 3:00 PM. if server or database unavailable at 3:20 PM, as you said can not recovery 20 Mints data. is there another way to protect data or recover data in 20 mints
Thanks.
January 5, 2009 at 3:28 am
Hi,
If it is possible you should make a transaction log backup on the database to get as much from the database as possible. This could then be used to restore changes up to the last moment before the crash.
If that is not possible you may well lose the 20 minutes after the last backup.
regards
GermanDBA
Regards,
WilliamD
January 10, 2009 at 10:31 am
You're not saving much by backing up the log every 30 minutes v every 5 or 10. I'd run log backups more often if you're concerned.
Also, be sure you're not putting the backup files on the same disk/array as the data files. That's a recipe for disaster.
December 19, 2014 at 12:58 am
Plymouth Data Recovery specialists in recovering data from a wide range of devices capable of storing data. We recover data from PCs, External HDDs, Laptops, RAID setups and a whole host of removable storage devices. We have 15+ years experience in the field of data recovery and carry out 1000s of recoveries annually.
Data Recovery Services UK[/url]
December 19, 2014 at 1:49 am
You should take Transaction log backup more often, However the frequency of Transaction log backup would be directly related to your RPO (Recovery Point Objective). Loosing 30 minutes of data in a high transaction environment doesn't sound good to me.
I have RPO of 5 minutes, so here is what i do.
1. Full backup at 01:00 AM
2. Differential backups every Hour.
3. Transnational log backup every 5 minutes.
In case my database crash at 3:20 AM :
1. Restore the full backup taken at 01:00 AM (No Recovery)
2. Restore the differential backup taken at 03:00 Am (No Recovery)
3. Restore all the transaction log backup post that in the exact sequence. (Last one with Recovery)
If the database if not available then at max i will loos data worth 5 minutes. In case the database is available i can take a tail log backup and restore the data without any lose.
Hope it helps..!!!
Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply