question

subramanyam_K avatar image
subramanyam_K asked

interview question

what is your company backup policy?if something happened to your db how to restore it?
sql-server-2008
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
A constructive comment would be nice for the downvote on the question
1 Like 1 ·
KenJ avatar image KenJ commented ·
how would you answer it, subramanyam_K ?
0 Likes 0 ·
subramanyam_K avatar image subramanyam_K commented ·
I did not give this question answer pls if u know pls send the answer
0 Likes 0 ·
KenJ avatar image
KenJ answered
I can't really say what your company backup policy is, and how to restore your DB if something happens to it depends a lot on your backup scheme. To answer an interview question like this takes a bit of knowledge and experience in the environment about which you are being questioned, so we can't, and shouldn't, just supply an answer to help one blindly past an interview. A nice place to get started on backup and restore policies and procedures would be this paper by Javier Loria - [ http://www.dell.com/downloads/global/solutions/public/white_papers/sql2005_backup_wp.pdf] [1] or this MSDN article on backup and restore - [ http://msdn.microsoft.com/en-us/library/ms187048.aspx][2] You can also get syntax help in Books Online - [ http://msdn.microsoft.com/en-us/library/ff848768.aspx][3] Good luck with the interviews! [1]: http://www.dell.com/downloads/global/solutions/public/white_papers/sql2005_backup_wp.pdf [2]: http://msdn.microsoft.com/en-us/library/ms187048.aspx [3]: http://msdn.microsoft.com/en-us/library/ff848768.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Chris shaw avatar image
Chris shaw answered
Hi Subramanyam Questions like this are easy to answer if you understand what the data loss policy is for the company that is asking the question. When and how a server is back'd up, all depends on that amount of data that can be lost and amount of time that the server can be down to restore the database. A higher transactional system requires more frequent backups, but a database that doesn't change very often could have a different stratgy all together. Take a look at this blog post, I hope this helps point you in the right direction. http://chrisshaw.wordpress.com/2012/02/07/what-is-the-best-disaster-recovery-plan-drp/ Chris
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered
Well, the way the question is phrased, I would have to start by saying I absolutely will not discuss the details of the backup policy in my current company with anyone outside the company, but I would be happy to talk about general backup strategies. As far as general backup strategies, my first thing is that I tailor it to the needs of the situation. When I have to make a snap setup my baseline is one daily full backup during an offpeak time, one differential backup during the day, and a log backup every other hour. Then an offsite copy of those backups should be made at least once a day in case of an event (fire, explosion, earthquake, etc) that destroys the site. There would also be a regular restore test, for which I considered once a quarter standard. But that is a baseline. I have dealt with developer/test databases that I never made any backups of because they were recreated regularly. I have had static databases that I placed in read only status and took just one backup, which I verified and then made multiple copies of. I have had others where losing any data would be inconvenient so I took log backups every half hour and made sure multiple offsite copies were created automatically and where I did a restore test once a week. While I've never had to actually make one for real use, I've contemplated scenarios where any loss of data would be disasterous and considered high-availability solutions along with traditional backups to absolutely minimize the chance of ever losing anything significant. In designing the backup solution questions to ask include, but aren't limited to: 1. How much data can the company afford to lose? (If the first answer is "none" I start asking if they are willing to pay for an elaborate HA solution along with the backups) 2. How often does the data change? (Little value in doing a daily full backup of a database that receives only one batch of changes in a month at a scheduled time...) 3. When is the server under stress? (this affects the timing of the backups more than type or frequency, but it needs to be considered) 4. Is point in time recovery needed? (Unless it is a development server, I am *very* reluctant to take no for an anwer here, but for a development database that really doesn't need it, it might make sense to go for simple recovery instead of wasting time and space with log backups) And finally, consider a layered approach, as discussed anecdotally at: [How to Kill a Company in One Step or Save it in Three][1] The bottom line if you don't want to read the whole article is that nothing can ever substitute for a good backup routine which is regularly tested to make sure it works. But, High availability solutions, replication, volume snapshots on the SAN/harddrive level, and things along those lines can provide a useful supplement to backups in a real crisis. [1]: http://www.simple-talk.com/sysadmin/general/how-to-kill-a-company-in-one-step-or-save-it-in-three/
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
How to Kill a Company... was a good read. thanks!
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.