Voice of the DBA

Tag: disaster recovery

  • 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.

  • #2 Skill – Performing a Restore

    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 Will Restore a Database

    At some point you’ll need to restore a database. It might be a database on your local instance of SQL Server to correct a problem with a query or a patch, but you’ll need to restore data.

    This goes along with the first skill of backing up a database as the counterpart. A backup saves the data (and objects) and a restore brings that data back.

    Restores are fairly easy, but there are a couple of things you need to learn right away:

    Always use NORECOVERY

    By default the RESTORE command brings a database online by going through the recovery (redo and undo) processes. For a full database restore, this means you cannot restore additional logs. You might not to this time, but at some point you will.

    So always use WITH NORECOVERY.

    You need this in database mirroring, log shipping, and more scenarios. Always include this in your restore commands. To bring the database online when you are sure you are done restoring (even if this is only one restore), use the RESTORE command and WITH RECOVERY, as in:

    RESTORE DATABASE db1 WITH RECOVERY

    Learn to move files

    I find that many restores take place for practice, or on servers other than the original ones. In that case, the paths might not exist. Often the production servers, or the main servers you use, will have more drives than the test servers. In that case, having a file stored on the z: drive for a server doesn’t match up with a development server containing only a C: drive.

    The WITH MOVE option is used to move the existing logical files in your restore to a new location. Here is the sample command from Books Online.

    Script Restores

    It’s easy to make mistakes with the GUI in SSMS. Learn to script restores and run the scripts. Even if you use SSMS to setup the restore, don’t click OK. Instead click this:

    scriptrestore