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.


migrated from stackoverflow.com Jun 14 '11 at 6:15

This question came from our site for professional and enthusiast programmers.

  • Are you using maintenance plans please? – gbn Jun 12 '11 at 18:29
  • Yes. We created the maintenance plan to take the full backup – faiz Jun 12 '11 at 18:39
  • This should be moved to dba.stackexchange.com – lazyDBA Jun 14 '11 at 4:31


I would strongly discourage you to use off the shelf maintenance plans. If you are using the SSIS to build custom maintenance plans then that is another story (which I still don't recommend). Its best to use T-SQL scripts and if you are NOT comfortable writing them from scratch there are many such scripts written by well known folks in the community and widely accepted by many companies.

Look for Ola.Hallengren, Michelle Ufford, Tara Kizer and out of these Ola's script are the most popular.

Once you setup the sql jobs to do daily full backup, 6 hrs diff backups and 15 mins log backups, your job is NOT complete yet.

Your job is done ONLY when you take those backups and test your restores. This is a very critical step IMHO you shouldn't skip.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy