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'm busy reviewing my SQL Maintenance plans and I'm a bit confused with the recovery model option on the databases, specifically related to point in time recovery.

Here is my scenario:

  • I have two maintenance plans
    • One that does hourly transaction log backups
    • another that does a daily full backups (but first a transaction log backup before the full backup)
  • All databases are set to use the simple recovery model.

Here are my Questions

  • Seeing that the recovery model is set to simple and msdn states that the simple recovery model does not support point-in-time recovery - Does this mean that I won't be able to use my transaction log backups to restore the database in a disaster to an hour before it happened?
  • Which backup should be done first, the database backup or the transaction log backups?

Articles that I'm busy reading say I should do the database backup first and then the transaction log backup else I will get maintenance plan errors, but I'm currently first backing up my transaction logs and then data databases and I'm not getting any errors.

My final solution (Maintenance Plan)
Set all databases to use the FULL recovery model. Then:
Hourly Maintenance Plan:
- Check sufficient hard drive space for backups
- Make Transaction Log Backups
- Archive transaction log backups (zip the .trn files)
- FTP the archived transaction log backups offsite

Daily Maintenance Plan:
- Check sufficient hard drive space for backups
- Do a full database backups followed by transaction log backups
- Archive backups (zip the .bak and .trn files)
- FTP the backups offsite

Weekly Maintenance Plan:
- Check database integrity
- Rebuild indexes
- Cleanup history older than 4 weeks
- Delete .bak and .trn files older than 4 weeks
- Delete archived backups (zip file) older than 4 weeks

If any task fails a notification email is sent. A success email is also sent when the daily and weekly task finish successfully.

Note of testing your maintenance plans. My first backups got corrupted by the ms windows ftp.exe not configured correctly to use passive mode. SO CHECK YOUR BACKUPS :)

share|improve this question

migrated from stackoverflow.com Oct 2 '12 at 6:39

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

2  
A backup plan where you don't ever attempt to use the backups to do a recovery is a broken backup plan. So, try out your restore scenarios, and see whether they work. You won't get any errors during backups. –  Damien_The_Unbeliever Oct 1 '12 at 7:37
    
very true - working on that. I'm a developer who accidentally fell into the role of DBA a short while ago, so busy testing and configuring everything correctly now (and building up knowledge at the same time). That's kinda how it goes with small companies :) –  Daniel Brink Oct 2 '12 at 8:46

3 Answers 3

up vote 4 down vote accepted

Seeing that the recovery model is set to simple and msdn states that the simple recovery model does not support point-in-time recovery - Does this mean that I won't be able to use my transaction log backups to restore the database in a disaster to an hour before it happened?

Even taking a transaction log backup is not supported for databases using the SIMPLE recovery model. This is a restriction of the database engine based on how this recovery model works, and the recovery features it doesn't support, as you mentioned.

A transaction log backup maintenance plan task automatically skips databases in SIMPLE recovery to avoid causing errors.

Which backup should be done first, the database backup or the transaction log backups? Articles that I'm busy reading say I should do the database backup first and then the transaction log backup else I will get maintenance plan errors, but I'm currently first backing up my transaction logs and then data databases and I'm not getting any errors.

For the reasons I mentioned above, it won't matter for databases using SIMPLE recovery, as they will be skipped by the transaction log backup task.

For databases in the other two recovery models, a full backup must exist before you start taking transaction log backups (just the first time), or you will get an error -- this is probably what the articles refer to.


Point-in-time recovery ability is normally driven by business need -- in other words, you determine how critical the data is and how much you can afford to lose, then set the appropriate recovery model to meet those needs, and finally create a backup solution.

Even though SIMPLE recovery does not support point-in-time recovery, if an hour of data loss is okay, perhaps a differential backup solution could work for you. (There are far too many variables that go into developing this kind of solution to give you a complete picture with what was provided in the question.)

share|improve this answer
    
Thank you Jon, what you stated above is matching up with my findings/testing. Things are slowly coming together now. –  Daniel Brink Oct 4 '12 at 8:36

Keep in mind that the frequency of transaction log backups depends on how busy your database is and how much data you can afford to lose.

If your databases are quite busy, consider creating transaction log backups every 15 minutes. That will also provide that the maximum potential data loss is less than 15 minutes of data.

If you don't create transaction log backups often enough, besides more data you can lose, the online transaction log might grow more than you prefer.

Here's the SQL Server Management Studio Backup dialog for a database in the Simple recovery model

enter image description here

share|improve this answer

According to MSDN, transaction log backups are only available to Full (and Bulk-Logged) Recovery Model:

"Transaction log backups are used only with the Full and Bulk-Logged Recovery models"

http://msdn.microsoft.com/en-us/library/aa173551(v=sql.80).aspx

Indeed, the option in the picture is not available if your database recovery model is set to Simple:

Backup options in SQL Server

Your second question is answered in that MSDN article as well:

"Restoring a database using both database and transaction log backups works only if you have an unbroken sequence of transaction log backups after the last database or differential database backup. If a log backup is missing or damaged, you must create a database or differential database backup and start backing up the transaction logs again. Retain the previous transaction logs backups if you want to restore the database to a point in time within those backups."

share|improve this answer
    
Thank you Jaime. A whole bunch of lights went on this morning in my head. I got a bit of information overload from all the books and articles, but concepts are starting to come together now. –  Daniel Brink Oct 2 '12 at 8:54
1  
@DanielBrink You are welcome, Daniel. If you found my answer helpful, please mark it as useful and valid answer :) Thanks –  Jaime Oct 2 '12 at 14:21

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.