Tell me more ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
up vote 2 down vote favorite

I have a production critical database which is in Simple Recover Model Currently. We take a Full Backup Every night at 2:30AM. Now I have to change the recovery model back to full. I have to design the backup and restore strategy to include the Differential and Transaction log backups. My manager wants a full backup every night, diff backup every 6 hours and tlog backups every 15 min. So what are steps should i take to achieve the above scenarios. Please help, any suggestions will be greatly appreciated.

Thanks.

share|improve this question
How big is the database? How large are the logs generated daily? What backup product are you using? – KCotreau Jun 12 '11 at 23:35
Its about 200GB. We are using 2008 maintenance plan to do the full back up every night. Now i have to change back to Full Recovery model. – faiz Jun 12 '11 at 23:51
The how and when of backing up the database should be driven by the needs of the business. Until you determine those I don't see how you can put an effective plan that meets those needs in place. – joeqwerty Jun 14 '11 at 12:16

2 Answers

up vote 0 down vote

Backup and recovery operations in SQL Server can be a rather daunting task especially when dealing with large databases and multiple backups. Your overall environment plays a pretty big factor in your strategy as well. Do you only have a single database server? Are you running in a cluster? Using mirroring? Log shipping? Are off-site data centers available? What kind of bandwidth is available? Do you have SAN storage available?

If up-time is critical and you cannot deal with a production outage then something like a failover cluster or database mirroring may be what you need. If disaster recovery is your primary goal, then something like log shipping may be more appropriate. If both are important, you might need a hybrid combination of (for example) a failover cluster in a primary data center combined with log shipping to a standby server at an alternate location.

Here are a few resources you may want to have a look at:

There are quite a few branches off of each of the above links which should help provide some point of reference.

share|improve this answer
up vote 0 down vote

It looks like you asked the same question yesterday: Sql Server 2008 Backup and Restore

Have a look at the answer I gave for some additional resources you can read about backup and recovery operations as well as some high availability options.

share|improve this answer
Sorry i did not see your answer yesterday. We have Peer to Peer Replication for High Availability. – Faiz Jun 14 '11 at 12:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.