Voice of the DBA

Tag: Backup/Recovery

  • Recovery Models

    In SQL Server we have three basic recovery models: full, bulk-logged, and simple. By default we find that most databases use the defaults, which mean that they are in the full recovery model.

    In that case, you need to be sure that you are performing log backups, otherwise the log will grow until it reaches it’s limits, or you run out of disk space on that drive. If the log cannot record SQL Server transactions, the database cannot accept any more transactions.

    The basics of recovery models are covered nicely in this article from Gail Shaw, which includes some common myths and misconceptions out there. However for the average person, the important thing is that you understand which recovery model to pick.

    You Need Point in Time Recovery

    Point in time recovery means recovery in between the full or differential backups. Quite a few DBAs will ask customers if they really need to recover to a point in time, and get the answer that they don’t, but that’s not often the right question to ask.

    Ask your clients if the database failed at 5:00pm today, and all the work done today was lost because you restored to last night’s backup at midnight, how would they feel?

    Sometimes they’re fine with the data loss, most times they aren’t. If you need to get back to a point in time between backups, make sure you use the full recovery model.

    You Can Reload the Database

    There are some databases, usually data warehouses, that can be rebuilt from other sources. If you take a backup of your database and then load data every day that rarely changes during the day, you might not need point in time recovery. In fact, many ETL processes are not designed for this anyway, and could not restart themselves in the middle of a load if you restored to the point in time when the database had an issue.

    In this case, use the Simple recovery model.

    You are space constrained with the log

    If you run index rebuilds, or large data loads and find yourself with a transaction log that grows very large, you might want to investigate the Bulk-logged recovery model. This model is more confusing, so I don’t want to give you a general rule here. If you think you might benefit from less logging, investigate the bulk-logged recovery model, practice restores with it and make sure you fully understand the implications of using it before you set a database in this mode.

  • SQL Server Backups – When is it current?

    I saw a post recently where someone was asking about the restore sequence for a series of backups. The scenario was this:

    1. Full backup starts at 3:00am, and takes 30 minutes
    2. Log backup 1 starts at 3:05am, and takes 2 minutes
    3. A second log backup starts at 3:35 and takes 2 minutes

    What do you restore?

    The short answer is that it doesn’t matter. If you use NORECOVERY (Always use NORECOVERY) and restore the logs in order, SQL Server will sort things out. If the transactions from backup 2 (the log backup from 3:05) are in the full backup, they won’t be applied twice and the system will let you know.

    The same thing occurs for the second log backup. This is why SQL Server uses the Log Sequence Numbers. They ensure that SQL Server can track which transactions occurred when and in which order.

    When is the full backup consistent?

    If we are wondering when the full backup is complete, or at what point during your system’s life is the full backup going to return you to, it’s easy.

    It is consistent as of the time when the data reading portion of the full backup is complete. We don’t necessarily know when that is, but at that point, the full backup will copy enough log records to get consistent to that point in time. If it’s a lot of log records, it’s possible that this is quite a bit of time before the backup completes and the timestamp goes on the backup file.

  • Create a Log Backup Schedule – #1 Skill You Need

    This series of blog posts are related to my presentation, The Top Ten Skills You Need, which is scheduled for a few deliveries in 2011.

    You Need Log Backups

    By default databases in SQL Server are created with the Full Recovery Model. That means that without log backups, the log will continue to grow and grow until it hits the growth limit you’ve set, or it fills the disk. I see questions on this constantly at SQLServerCentral from people who have a 10MB database and a 653GB log file.

    A log backup will mark the transactions in the log as backed up, and that space can be re-used. The log backup aids in space management and also provides recovery to points in time in between full backups. With that in mind, you need to do a few things.

    Schedule Log Backups

    As soon as you create a database, and you create a full backup schedule, schedule log backups as well. It’s easy to schedule a single daily log backup along with your full backup. They don’t block each other, and for most non-production systems, this works fine.

    Note that the total space used for the log backups is the same whether you schedule 1 a day or 86,400 a day. There is a slight overhead to each file, but essentially the space used is the same. However backups scheduled more often result in a smaller log files size for the LDF.

  • When Do You Take a Full Backup?

    This series of blog posts are related to my presentation, The Top Ten Skills You Need, which is scheduled for a few deliveries in 2011.

    When is a Full Backup Taken?

    Most people might answer this with “every day”, or “as often as needed”, but those are a little nebulous, and not necessarily correct.

    I do recommend that you backup as often as you can, which is every day for most people. Those with large databases might go once a week, or even once a month, but you want to get a full backup periodically. If you have a really large database, say 50TB or greater (in 2011), you might opt for a yearly SAN snapshot of some kind, but you need some full backup as a base.

    So when do you backup your database? Here’s a list of some times you might think about:

    • As often as you can on a regular schedule (daily, weekly, monthly, etc)
    • Before applying a Service Pack or CU*
    • Before applying a Windows patch*
    • Before upgrading your application*
    • Before a large data load*
    • After restoring your instance in a DR situation

    That last one might throw people, but I’ve seen more than a couple situations where someone recovered a database in a DR situation and never setup backups. And there was another failure.

    Whenever you create a database, either from scratch or after a restore, make a backup.

    * Note that if you have Enterprise Edition or above, you can use Database Snapshots to substitute for a full backup in some of these situations.