Voice of the DBA

Tag: Backup/Recovery

  • Two Types of Tail Log Backups

    In a recent thread I noted that a tail log backup is essentially a regular log backup, but made with the intention of restoring the database because something is wrong with your data file. Gail Shaw (blog | @SQLIntheWild) pointed out that that’s not quite true. There are two parameters that you need to add to the BACKUP LOG command. Thanks to Gail for the correction, and here’s a little more data.

    There are three options you have with a tail log backup are:

    • WITH NORECOVERY
    • WITH CONTINUE_AFTER_ERROR
    • WITH NO_TRUNCATE

    I covered the third one in doing some practice backing up of the tail log. The second one is noted in Books Online as one that you should use in the event that the database is offline and inaccessible. That will allow you to recover the last log backup (hopefully).

    The first one is recommended as the one you use when the database is going to be restored and you want the end of the log.

    As I mentioned in my previous post, this is a core DBA skill. It is what will allow you to recover a database with zero data loss.

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

  • #1 Skill Needed – Backups

    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.

    1. Backups

    The first skill that you really need is the ability to back up a database. No matter what else you can do, or others will do, if you have backups, you can recover from issues.

    The is the first thing you should do with a new database. Make a backup if there isn’t one, and create a new schedule. Even if you haven’t added any objects or data, just setup a backup schedule. It’s easy to put off, and forget. I can’t tell you how many times I’ve run into a developer or DBA that is looking to recover a database and doesn’t have backups.

    Backups are insurance. Just like you have Geico, or Aviva, or any other insruance company in case of situations like this:

    Car Accident

    You never know when this will happen, so you have to be prepared. In the database world, part of your preparation is having a backup.

    Even if you don’t know how to do a restore, make sure you have a backup. You can always find someone to help you with a restore, but another person can’t help you with a restore without a backup file.

    Learn how to use the Backup T-SQL command.

  • A Really Bad Day

    I was out of town for work one day, literally on the other side of the country, in a car bound for the remote office when my cell phone rang. I had flown into LaGuardia Airport in New York City and needed to drive to Connecticut, to an office just outside of Hartford. The company was paying, and it was more convenient for me to fly into NYC and then take a chartered car to CT.

    I glance at caller ID and it’s a client of mine back in Boulder, CO who I do some occasional SQL Server work for. I answer, thinking that he needs help with some SQL command and I can score some brownie points here without charging him.

    “Steve, I have a problem”

    The words that anyone in technology is loath to hear. Even if you’re being paid by the hour, clients that cause issues are often going to be more trouble than the payment is sometimes worth. I much prefer to hear “Steve, we’d like to do x. How can we do that?”

    I ask him to tell me what’s wrong and he said that RAID card was throwing errors on the development server, so he performed a SQL Server backup, copied the backup to a file server and then replaced the RAID card, rebuilding the array. When he went to restore the server, he got an error.

    I am the backup and restore champ, at least of my little world. “Read me the error, “ I say, confident I can solve this for him before we arrive at the office..

    “The media set has 2 media families but only one are provided”

    Uh, oh, I’m thinking, and not because of the poor grammar. This is not good and I’m not going to be able to fix this. Breaking bad news to a client is never fun, even if it’s not your fault. I’ve lost a couple because they were so upset they let me go at some bad news. That’s actually OK with me because if they can’t tell the difference, they are likely to leave me when I least expect it anyway and I’d rather know sooner than later.

    I explain that he must have used two backup files and he needs to add the second one in there. The guy is insistent that he didn’t pick two files when he made the backup and he copied the right one off. He even included the date in the filename (like I showed him) and reads it back to me.

    I know what’s happened. He’s made an inadvertent striped backup, and since the RAID array was blown away, he’s likely lost the other file. I try to keep from laughing as I explain patiently what happened. He’s upset, then he’s confused, then he’s unhappy. I can picture the sad panda face on him from 2,000 miles away as he realizes that he’s not getting this restore to work. The driver must hear it in my voice as well since he’s smirking in the rear view mirror.

    To add misery here, he wasn’t copying backups off this development server to save space on the file servers. He’s lost a few weeks of work now, and he has to break the news to the developers, who likely don’t have backups of their own.

    I sign off, not sure what to do as I get to my destination. It’s a good example of why SQL Server still needs a DBA, or someone with decent DBA skills. Despite the ease with which you can get many things done, not understanding why you do them cause big problems.