Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've read that a decent backup strategy would be:

1) Full backup weekly
2) Differential backups daily
3) Log backups every 5 minutes.

Now question is how do I write this stored procedure and create a sql job out of it. For eg. For differential backup, it is necessary that full backup must exist. Now when it is Monday 12 AM and it is time for both weekly and differential backup to run. It is possible that differential backup runs before full backup. How do I handle these tricky conditions? Could anybody please share the solution for this?

share|improve this question
    
There is no general approach to backup strategy. It all depends on your data. FWIW there's no point in running differential backup at the same time as full, but you could ensure that they run in sequence using SQL Agent job steps. –  Aryadev Mar 20 '13 at 9:43
add comment

migrated from stackoverflow.com Mar 20 '13 at 14:27

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

2 Answers

up vote 4 down vote accepted

First, I would recommend you go get Ola Hallengren's backup scripts. They are full featured and well documented, supporting exactly the type of operations you are looking to complete. This saves you a lot of effort of writing your own scripts from scratch and the solution is recommended by many notables in the community.

Second, I would create SQL Agent jobs to call the backup stored procedures, with the following jobs:

  1. Full backup job that runs weekly, creating full backups for all databases. For argument's sake, this is at 2 AM Sunday.
  2. Differential backup job that runs on all days other than the day of the full. This would be 2 AM Monday-Saturday.
  3. Log backup job that executes every 5 minutes.

This is a very basic and general schedule and you will probably need to tweak and adjust it based on your environment. The largest area of adjustment will probably be how often you run your log backups. Depending on the size, activity, the number of databases in FULL recovery mode, and the fact that this approach backs up your databases serially (one after the other), all log backups on your instance may not complete in 5 minutes. There are several ways to handle this, such as increasing the window between log backups and creating different backup jobs for different databases (so you can have them execute in parallel).

share|improve this answer
add comment

I would say your backup plan is almost too concise and you may end up with a lot of data that has so much redundency that you are wasting space.

Why not use the Maintenance Wizard to setup a full Backup to run Sunday night (strictly example) at 12:00 a.m. Set your differentials to run at 2:00 a.m. every day and set a rule to have your logs not back up at those times on those days. That was purely for example sake, but everything can be adjusted to your data loads and other maintenance plans. This way you aren't duplicating or interfering with other backup jobs and anything else the SQL Agent may be doing.

share|improve this answer
add comment

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.