Voice of the DBA

Tag: TopTenSkills

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

  • The Top Ten Skills You Need for SQL Server

    This talk is designed to talk about the most common skills that you need to work with SQL Server on a daily basis. It is mostly for administrators, but quite a bit of this information would benefit developers if they understood it.

    The top ten skills are:

    1. Perform a Backup
    2. Restore a Database
    3. Setup Security
    4. Join Tables Together
    5. Create an Index
    6. Run Database Maintenance
    7. Schedule a Job
    8. Send an Email from SQL Server
    9. Import Data
    10. Search BOL

    Length: 75 minutes (can be done in 60)

    Talk specific slides are here:
    I have a series of blog posts under the TopTenSkills tag that relate to these items.

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

  • The Top Ten Skills You Need

    One of the things I’ve found is that so many people struggle to get started with SQL Server. There are regular questions about how to learn to work with SQL Server, so I built this quick talk that should help you get started.

    This talk is built on the 80/20 rule, the idea that 80% of your job needs 20% of the DBA skills, and vice versa. So these are a series of skills, presented quickly, that should get you through most of the things that you need to deal with in SQL Server as an administrator.

    Developers will find that spending a little time learning these skills will also let them easily setup a SQL Server instance that can maintain itself for the most part without them. And it will protect them in the event there are issues.

    The talk moves quickly, but there are lots of links and references in the deck.

    Slides

    The slides are on my SkyDrive.

    Length

    This talk is built for 60-75 minutes

    Related Blogs

    These are the blog posts related to this talk: