x

interview question

what is your company backup policy?if something happened to your db how to restore it?
more ▼

asked Mar 02, 2012 at 05:21 PM in Default

subramanyam_K gravatar image

subramanyam_K
-1 1 1 1

how would you answer it, subramanyam_K ?
Mar 02, 2012 at 07:37 PM KenJ
I did not give this question answer pls if u know pls send the answer
Mar 03, 2012 at 11:47 AM subramanyam_K
A constructive comment would be nice for the downvote on the question
Mar 05, 2012 at 10:09 PM KenJ
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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][3]

Good luck with the interviews!

[3]: http://msdn.microsoft.com/en-us/library/ff848768.aspx
more ▼

answered Mar 05, 2012 at 02:58 PM

KenJ gravatar image

KenJ
19.3k 1 3 11

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Mar 05, 2012 at 03:13 PM

Chris shaw gravatar image

Chris shaw
530 2 3 5

(comments are locked)
10|1200 characters needed characters left

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/
more ▼

answered Mar 05, 2012 at 06:04 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

How to Kill a Company... was a good read. thanks!
Mar 05, 2012 at 07:10 PM KenJ
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1834

asked: Mar 02, 2012 at 05:21 PM

Seen: 691 times

Last Updated: Mar 05, 2012 at 10:09 PM