TechNet Magazine: Feature article on backups and backup strategy

The July 2009 edition of TechNet Magazine is available on the web and include a feature article I wrote explaining backups. It's the first in a 3-part series, with parts 2 and 3 being on recovering from disasters using backups and recovering from disasters without backups.

The backup article covers:

  • Full backups
  • Differential backups
  • Log backups
  • Putting together a backup strategy
  • Bakcup integrity

It debunks a lot of myths about backups, explains in detail how each backup works, and explains the pros and cons of some different backup strategies.

Check it out at Understanding SQL Server Backups – enjoy!

PS I'll also be presenting a session on backup strategies at SQL Connections in November and covering them in my post-con workshop at PASS in November the week before.

PPS I've just signed a contract with TechNet Magazine so that there will be something on SQL Server in every issue – either my SQL Q&A column or a feature article. Very cool!

5 thoughts on “TechNet Magazine: Feature article on backups and backup strategy

  1. Hello Paul,
    First to all, I would like to thank you all your effort in spreading your knowledge to all who want to be teaches by you. Weren’t you going to stop posting in August? Its hard job to follow you!!! ;-)
    Here is my question:
    In your February article at TechNet Magazine “Understanding Logging and Recovery in SQL Server” you said:
    The check whether log truncation can take place under either of the following circumstances:
    • When a checkpoint occurs in the SIMPLE recovery model or in other recovery models when a full backup has never been taken. (This implies that a database will remain in a pseudo-SIMPLE recovery model after being switched out of SIMPLE until a full database backup occurs.)
    • When a log backup completes.
    And my question is: Why only after log Backups, as you said in this article a full Backup will reset the chain of the log. I might understand the lack of a check after a differential backup but why there is not option to treat it as a Full Backup and reset the log chain.
    Thank you in advance,
    PD: During a checkpoint the entire Database is locked?.

  2. No – a full backup doesn’t reset the log backup chain. Once log backups are being taken, a full or differential backup has no effect on the log. Log backups can be restored in sequence or after a full (or subsequent differential) backup is restored, as long as there is an unbroken sequence of log sequence numbers from the first full (or subsequent differential) restored.
    No – during a checkpoint there are no blocking locks held.

  3. Hello again Paul,
    Sorry I insist but a still have the question unanswered. I might be misunderstood because English is not my mother language.
    In this article you said:
    “A transaction log backup contains all the transaction log records generated since the last log backup (or full backup that starts a log backup chain)”
    So if you make a log backup after a full backup it will not contain any transaction committed before the full backup had been taken. So those entries might not longer be needed in the transaction log like after a log backup.
    And then, Why after a full back up there is not a check for a log truncation?, and why there is not an option in the differential back up that forces the log backup chain to start?
    Thank you very much,

  4. Dude – because that’s the way it works. Go read the article on Understanding Logging and Recovery as well – if you still don’t understand it, you’ll need to get someone to explain it to you with a whiteboard – this isn’t the right medium to explain if you’re not picking it up from the articles.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.