Voice of the DBA

Author: way0utwest

  • Backing up the log in simple mode

    Someone posted a note saying that their backups were really large and slow. So they were trying to manage their backup scheme and ensure some level of recovery.  I was going back and forth, along with others, talking about the way your recovery would go and the merits of that particular backup scheme. While posting, the person mentioned at one point that his databases were in simple mode.

    Huh?

    AFAIK, or rather, As-Far-As-I-Remember, you can’t do a log backup in simple mode. I think that’s been a part of the product, but before posting, I decided to test.

    Short answer, if you don’t want to read, is that you can’t backup a transaction log if the database is in simple mode.

    First I grabbed a sample database on my local 2008 instance and set it to simple mode. Actually, first I had to set a database to simple mode, and I didn’t want to use the GUI. This isn’t something I do often, so I had to look up the syntax:

    ALTER DATABASE SET OPTIONS – The first example shows setting to full recovery mode.

    ALTER DATABASE db2 SET RECOVERY SIMPLE

    Once this was set, I created a few transactions and ran a backup.

    CREATE TABLE MyTable (id INT)

INSERT MyTable DEFAULT VALUES
    BACKUP LOG db2 TO DISK = 'db2_log_test.trn'

    This gives me the following error:

    Msg 4208, Level 16, State 1, Line 2

    The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.

    Msg 3013, Level 16, State 1, Line 2

    BACKUP LOG is terminating abnormally.

    This is what I expect, as there is no backup chain that can be used to build a backup. What about running a full and then a log right away?

    BACKUP DATABASE db2 TO DISK = 'db2_db_test.bak'
BACKUP LOG db2 TO DISK = 'db2_log_test.trn'

    Once again, the error occurs, and there should be no checkpoint after the full backup starts.

    db_simple_log

    So I think that my posted is mis-informed of his environment, which is a dangerous place for a DBA to be, especially with regard to backup and recovery.

  • Sharing Code

    Sharing of Ideas

    It seems that almost every week I encounter some form of plagiarism it the SQL Server community. Whether its from SQLServerCentral or it’s someone else, it seems that I’m either alerting someone or sending my own takedown notice.  It’s a little sad that I have to even explain the morality of re-publishing someone’s else’s content without giving them credit to anyone over 8 years old, but apparently I do. However this isn’t a piece about plagiarism.

    I have seen people take various additional steps to protect their content, and that’s fine. It’s their right, and I understand that some people derive a level of income from their work. Whether it’s direct or indirect, it doesn’t matter. If someone wants to protect their work and they don’t want you to reuse it, then respect their wishes. If not for legal reasons, for moral ones.

    As we publish information on the web, however, I would think most of us hope our code and ideas are adapted and re-used. Not used in another publishing piece (blog, article, etc.), but used in a piece of production code that makes a system run better. I know when I’ve published code that helps someone track backups or monitor their servers, I was quite honored to hear that someone used my code in their system.

    Sometimes I wonder if  other authors out there feel the same way. Do writers assume their code will be rewritten by someone before it is used. Digital rights and intellectual property are immature fields, and I’m not sure how we ought to best handle the re-use of code. Authors have the right to decide how they want their works to be shared, and should publish their decision. I would hope that others out there would respect that.

    Steve Jones

    (originally published at http://www.sqlservercentral.com/articles/Editorial/72245/)

    The Voice of the DBA Podcasts

  • Coming to SQL Saturday #60 in Cleveland

    It’s just a couple weeks away, on Super Bowl weekend. I can’t believe this is event #60, still in shock that this amazing franchise has grown to large.

    I’ll be at SQL Saturday #60 in Cleveland on Feb 5, 2011, with my good friend Allen White (blog | @SQLRunr), talking about building your brand and the Modern Resume. There are lots of great speakers joining me there, including Aaron Bertrand (blog | @aaronbertrand), Mike Walsh (Blog | @Mike_Walsh), and more.

    I’ve never been to Cleveland, so I’m looking forward to the trip. I don’t know how much time I’ll have to see the city, arriving Fri afternoon and leaving Sun afternoon, but I’m going to try to do something. I know there’s a Sun morning run being set up somewhere, and I’m looking forward to that. It should be about say 879 for me then, so plan on joining us for a short, slow, 2-3 mi one that morning if you want.

    Hopefully I’ll get home in time for the Super Bowl Sun night, but if not, I’ll have it on the DVR and I’ll catch up on it Monday.

  • Help Us Help You

    Nissan GT-R
    Nissan GT-R

    Recently I was debating with some people on the quality of abstracts that were submitted to various conferences and events. There was quite a bit of discussion on the way that the summary of a presentation ought to be worded, whether your name should be in the description and various other considerations of what will attract an audience.

    To a large extent we all have our own personalities, and some of them show through in the session abstracts they write. If you look at how Brent Ozar (blog | @brento) or Buck Woody (blog | @BuckWoody) talk about their sessions, it’s quite different from those of many people. How many speakers use Nissan GT-R or arsenal in their descriptions?

    As a speaker I have a different view of the events and sessions, and more often than not I look at the speaker before the session title or description. Those often mean more to me in deciding if I want to attend. However I’m not sure that my view is the same as most of the audience.

    So…

    I’ve decided to try and help everyone out. We have created a new forum at SQLServerCentral calledPresentations and Speaking. It’s there for speakers to post their abstracts or titles and get feedback from everyone out there. This is one forum that I hope people subscribe to and go look at the entries on a regular basis.

    I’d also ask that those of you that have attended a conference or a SQL Saturday, take a minute and look at a few of the past submissions. Post a note in the comments for this editorial and let us know which abstracts made you want to attend a session and why? Or which ones made you not want to go see the session.

    With some feedback and help, you can help all the speakers out there get better at building presentations, and hopefully teaching you more about SQL Server.

    Steve Jones

    (originally published at http://www.sqlservercentral.com/articles/Editorial/72233/)

    The Voice of the DBA Podcasts