September 10, 2009 at 12:33 am
Hi,
I have SQL Server 2005 database which has very important data. For protection of data I am following Full Recovery model So that I can take the tail log backup to point of database failure.
Now I want to know that, If sql server gets crashed or Hard disk which contains .mdf and .ldf life that it self gets crashed then how will I recover data.
In short I want to know that, in real time scenario how to recover data from server crash or hard disk crash? How should be disaster recovery plan ?
Any suggestion will be appreciated.........
Thanks & Regards
Vinod.km
September 10, 2009 at 12:41 am
a good and well documented recovery plan is the first and foremost requirement for disaster recovery. You've got to ask questions on how much data can the company afford to lose in case of disk crash? based on this, you can setup your transaction log backup interval.
Also, you may want to think over many disaster recovery scenario available wiht sql 2005 and may want to implement one of them for better protection.
1. mirroring - this would be be a good choice for DR. make sure ur mirror server is almost of identical configuration so that in case of disaster, it can take over the role from the principle without hiccups. Still you need to decide whether u go for high safety mode or high performance mode. In high safety, performance could be an issue, in case of high performance, chances of some data loss is there. Based on the requirement you can decide.
2. log shipping - this is similar to your backup/recovery scenario but you'll lose data in case your ldf gets corrupted till the last t-log backup.
3. transactional replication - little tough to maintain but you can consider this as well.
4. clustering - if you can pump in some money to buy SAN/NAS, you can consider this method as well.
September 10, 2009 at 4:55 am
Agree with Pradeep, Good DR plan is neccesesary to protect the data. We have setup replication for most of our production boxes. Even good backup strategy will also help. Try to backup to different location instead of same HDD.
Cheers,
Got an idea..share it !!
DBA_Vishal
September 10, 2009 at 6:50 am
Then when you have your disaster recovery strategy and documetation - with a copy of the plan stored safely offsite with your offsite backups, test, refine, test refine, test refine.
If you are not testing and practicing your DR plan - you have no DR plan worth the name.
Also test hte DR plan and documentation by having people undertake roles they would not normally undertake. Is your plan and documentation good enough so that the network guy or one of the devs can go through the steps if the DBA is not available?
Can you set up the servers and network links if the network guy has just been rushed to hospital with appendicitis the day before your server blows up?
September 10, 2009 at 12:38 pm
i think that vinod.km was talking about something else ... "how to use my datatabase (which is in full recovery model) to bring my systemems online"...
the answer is:
1) are you doing log backups/database backups?
2) DR:
restore full backup, restore log backups, restore taillog backup.
and of course ... you need a properly configured server for this operation (sql in the same version, system databases, logins, jobs and other server objects)
September 10, 2009 at 1:03 pm
Since you have your main Db with full recovery mode, set up a Warm Standby. So if your Main Db is gone, the warm standby can take over.
-Roy
September 12, 2009 at 12:53 am
vinod.km (9/10/2009)
Hi,I have SQL Server 2005 database which has very important data. For protection of data I am following Full Recovery model So that I can take the tail log backup to point of database failure.
If you think your data is so important to the business, I believe you are also doing some integrity checks against the database?
Now I want to know that, If sql server gets crashed or Hard disk which contains .mdf and .ldf life that it self gets crashed then how will I recover data.
As I quoted above, when a hard disk gets crashed, its not just that your database gets offline, sometimes its also possible that your data gets corrupted but still your database being online and running normally..
In short I want to know that, in real time scenario how to recover data from server crash or hard disk crash? How should be disaster recovery plan ?
Any suggestion will be appreciated.........
Thanks & Regards
Vinod.km
In short, as suggested above by folks, a Strong DR plan is a Strong base for the whole DBA activity and also for the business.
You should be thinking about HA, as of Loghipping, DB Mirroring, Clustering technologies.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply