what is your company backup policy?if something happened to your db how to restore it?
asked Mar 02 '12 at 05:21 PM in Default
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 or this MSDN article on backup and restore - http://msdn.microsoft.com/en-us/library/ms187048.aspx
You can also get syntax help in Books Online - http://msdn.microsoft.com/en-us/library/ff848768.aspx
Good luck with the interviews!
answered Mar 05 '12 at 02:58 PM
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.
answered Mar 05 '12 at 03:13 PM
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:
And finally, consider a layered approach, as discussed anecdotally at: How to Kill a Company in One Step or Save it in Three
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.