interview question

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!

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

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

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.