Backup History

Categories: Corner, News, Professional, Scripts, SSC
Comments: 5 Comments
Published on: November 30, 2012

This is a quick rewrite of the script I just posted a few days ago.  The script had a few flaws if trying to run it on a SQL 2005 box.  I have updated it to run against SQL 2005 and up.

This version is done using Dynamic SQL to help achieve some of the functionality that I wanted but while allowing the flexibility of the different SQL Versions.

You can see the old script here, shown as a part of investigating why differential backups were failing to restore.



Running the above code, I get the following for one of my SQL instances.

Differential Restore Fail

Categories: Corner, News, Professional, SSC, SSSOLV
Comments: 6 Comments
Published on: November 28, 2012

We have all heard that backup is not complete until you test it by restoring it.  We also know that part of a DR plan involves the testing of those backups through restores.

I recently had the opportunity to run through a test scenario and ran into a substantial failure.  It was nearly catastrophic due to the size of the database, size of the backups and location of the DR site where we were testing the recovery.  Let’s just fast forward to all of the fun rather than bore you with all of the minutia.

I began the test with the restore of the full database backup.  This restore took in excess of 16 hours.  Once complete, the next phase was to restore the latest differential and then log backups from there.

Msg 3136, Level 16, State 1, Line 1
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

This is where the fun began.  I sat there pondering this for a bit.  How could this be the case?  I verified that the Full backup was only run once via the regular schedule.  I even ran the following to script to check.



This code returned only the one full backup that I knew and expected should be there from within the past few days.  So I tried that differential restore again.  I also tried a new differential just in case.  All of that was futile.  Then I looked back to my script and removed the following line



Tada – that revealed the problem.  Suddenly I had a new full backup appear for each day since the full backup that was taken through the regularly scheduled means.  Each new backup had the following nomenclature for the backup path.



And then it became apparent what was happening.  I had been excluding backups with paths following that type of naming convention due to CommVault in the past.  In this case I knew that CommVault was not a part of the picture.  I did know however that SAN snapshots were being taken on a nightly basis.

This was the aha moment – SAN snapshotting was causing a full backup to be registered in SQL Server.  And it makes perfect sense when looking at the backup history and sizes for each of the differentials that had been taken.  The differential backups were almost always the same size.  If you are running SAN Snapshots, you may want to reconsider your differential backup plan.

This script (just the revised version of the above) will help list the Full and Differential backups that have been taken.



Most Interesting People…

Categories: News, Professional, SSC
Comments: No Comments
Published on: November 21, 2012

Here are the most interesting people I met at PASS Summit 2012


Mickey Stuewe (sqlmickey)

Neil Hambly (neil_hambly)

Bob Hovious

Gail Shaw (sqlinthewild)

Ed Watson (SQLGator)






This is the short list of the many many many people I met while at Summit 2012 (the list is too long).  I appreciate the opportunity I had to meet each person whether mentioned in this post or not from the past week (Karla and Rodney Landrum, Tony Davis, Rob Farley, Jason Strate, Robert Davis, Wes Brown, Jes Borland, Martin Catherall, Tim Ford, Jim Murphy, Mike Fal, Louis Davidson, Kevin Kline, Jorge Segarra, etc, etc) .  This short list happens to be a list of people that I had a good amount of time to sit down around a table and chat for long periods throughout the week.  Some of them I even had the opportunity to chat multiple times.  They are all interesting for different reasons.  And some I have known for a fair bit longer than just this last week at Summit through online interactions and so forth – but I never actually met them until this last week.

Why are these the most interesting five?

I found them interesting for different reasons.  You should have your own list of most interesting people you met.  Here are my reasons for them being the most interesting in the order I met them at Summit.

Neil Hambly

I met Neil briefly for the first time at the MCM study group Sunday evening.  Two things that stood out immediately was his accent and that he was blunt and to the point.  Neil strikes me as a person that pulls no punches and tells it straight.  I like that.

Neil is very personable and very funny.

Ed Watson

Ed has an easy going southern kind of charm.  I met him Sunday evening as a bunch of us gathered at The Cheesecake Factory for dinner.  We sat and joked all evening.  We talked about amusement parks for a bit and mostly talked about a lot of stuff that was inconsequential.  Like myself, Ed has no problem having Cheesecake as a meal.  I frequently prefer to have my desert first, so thought this was cool that somebody else was willing to go against the grain.

I ran into Ed several times throughout Summit and we had several opportunities to chat.  I enjoyed meeting him and having so many opportunities to just chat and joke.

Gail Shaw

I have known Gail longer than any of the others on this list.  But I had never actually met her in person.  Like Neil, Gail will tell it to you straight.  She too has a thick accent being from South Africa.

With being busy at Summit, Gail was still finding time throughout the day frequently to continue to help people online.  That stood out to me quite a bit.  There are some very good reasons for why she was doing it too.  But in the true sense of giving of her time, she was making time to continually try and help people.  She was even asked last minute to fill in and present.

If you have the chance to get to know Gail, you will appreciate the opportunity.

Mickey Stuewe

The timeline for meeting Mickey gets a little grey here.  I actually ran into her the first time at the Exceptional DBA awards party.  We didn’t actually sit down and do a lot of chatting until Friday evening though.

Friday evening, a bunch of us met at the Crab Pot.  The food was really good and really too much!  There were about 12 of us in the group and Mickey and I were sitting across from each other.  We had a good long conversation about things from health to Summit to volunteering to performance tuning.

Mickey laughed a lot.  She also happened to be wearing her own design of SQL jewelry. Should you get the chance to meet her, you will probably find her laughing.  If she isn’t laughing – you should probably be able to make her laugh.

Bob Hovious

Bob (known as the Dixie Flatline on SQLServerCentral) is a grey haired gentleman with a lot of insight, good stories and deep knowledge.  Bob is a former lawyer and is a hoot. I enjoyed him prodding at Gail from time to time for a reaction.  Gail just played along – but it was fun.

I really enjoyed many of the stories that Bob shared.  Bob and I shared a lot in common and that is a big reason as to why we talked a lot.  Bob is a personable man with plenty of southern charm.  Bob is a good person and it seems to me that he likes giving back to the community.

Who are the five most memorable people you met at Summit?

Part of going to Summit is the huge opportunity to network with others.  If you are reading this, think about the people with whom you socialized during Summit week.  Who are the most interesting people you met?

Last Known Good CheckDB

Comments: 11 Comments
Published on: November 20, 2012

Diligent DBAs like to check their databases for a little thing called corruption.  Some of those DBAs are masterful at keeping records and logging that the database was checked and came up with a clean bill of health.

There are many different ways of logging this kind of activity.  Today I will share one such way to track when the last successful run of Checkdb happened.

First a little back story

A question came across twitter on the SQLhelp hashtag. The question was “Does restoring a database clear the dbi_dbccLastKnownGood value on the boot page?”

This question prompted me to do a quick test to see.  The test is simple.  Create a small database, backup the database, run checkdb, check to see what the dbi_dbccLastKnownGood value is, restore the previous backup and check the dbi_dbccLastKnownGood value again.

So here is a quick script

[codesyntax lang=”tsql”]


If you run this little test, you should observe that the date value for dbi_dbccLastKnownGood changes with each test.  Now let’s discuss the question and the answer to that original question about whether or not this value gets cleared.

The value does not get cleared.  The value does not get reset.  The cause for the change in the value that you have observed is due simply to the boot page having been restored.  If CheckDB has never been run on the database, you will get the SQL default date of ‘1900-01-01 00:00:00.000’.

And then…

Now that the back story is told, that brings us to how to track this – at least one such method.  I had to cover the back story since it is what prompted the writing of a method to gather this information in a quick script so I could use it to monitor.  Yes, it is just another tool to throw into the toolbox.  And to reiterate, it is by no means the only way to track or gather this type of information.  Some are more elaborate than others.  It is up to you to choose.

Keeping in mind that we can get the last time that Checkdb completed without a report of corruption, I delve into this cursor based method to retrieve the dbi_dbccLastKnownGood value for all databases within your SQL Server Instance.

[codesyntax lang=”tsql”]


You can use this or find something else if you desire.  The sole purpose of this script is to provide a means to gather quickly the last date known to report a clean bill of health from Checkdb.  From there, I will leave it to you to add it to another process in order to track over the long term.

You can read the twitter conversation here.  I also have it on good authority that Bob Pusateri (twitter) will be blogging about the same topic.

T-SQL Tuesday #36 SQL Community

Categories: News, Professional, SSC
Comments: No Comments
Published on: November 12, 2012

Community – What is it?

Community is something that is improperly defined with words alone.  Community is best defined through first person experience.  Community is a feeling and a sense more than it is a definition in “pick your dictionary here.”

So how do you experience this thing we call the SQL Community (or SQLFamily as we have dubbed it)?

Let’s examine that question next…

Experience the SQLFamily

The SQLFamily has been really good about putting itself out there and helping others.  If you use twitter you may have become acquainted with the #sqlhelp hashtag.  If you use twitter and participate in some of the discussions streaming nonstop from various corners of the family, you can see how quickly you might have been adopted into the family.

Still with the twitter theme, you might also see on frequent occasion something posted to the #sqlfamily hashtag.  Personal news might be posted to this hashtag from time to time.  In times of personal duress, the family comes together to show some strength.  Though not as tough as what others may have posted, I saw this first hand last year when away from the family on business and my daughter had an almond experience of the third kind.  You can read about that here.

Outside of twitter, the SQLFamily has been very progressive in getting more training out to the masses.  This training comes in the form of SQL Saturday and PASS Summit.  PASS Summit just wrapped up on November 9 and was a rather large family gathering.

At Summit, you may run into some princesses, some kilts, and maybe even some red lights along with all of those really big names that you see on twitter, the intertubes and so forth.  Here is a bit of what I mean (all pictures used with permission by Pat Wright (twitter) and can also be found on his flickr stream here).

From the Princess (Rumor has it she will be starting a blog soon.  This Princess has a name and it is Andrea Allred (twitter) ).

To a PASS Tradition – yes those are kilts.

Maybe even some flashing red lights (this might have been after SQL Karaoke)

And rounding it out with the Community Zone (I blogged about that here).  You might recognize some of the SQLFamily here (Tim Ford (twitter), Sarah Strate (twitter), Allen Kinsel (twitter), Louis Davidson (twitter) in far background to name some of them).

And this was all from the Emerald City of Seattle.  We will miss you next year Seattle as we visit Charlotte.

Community in the SQL world is not always about straight laced SQL and nerdery about the product we dearly love.  But it is about relaxing and having a good time with your friends too.  And when you are in this family – there are many friends.  The family is very open and accepting.  When they greet you, don’t be surprised if they attempt to hug you – it’s family.


Thanks to Chris Yates for hosting the TSQL Tuesday party this month.  You can read the original invitation here.  The rules for participation are in the invite.  Check it out along with anybody else that may have submitted an entry this month.

Summit 2012 Community Zone

Comments: No Comments
Published on: November 2, 2012





Will you be at Summit 2012?

I will be. I will even be pretty easy to find on multiple occasions.  If you haven’t seen my schedule already – you can check it out here. You’ll probably notice that there are a few times where I will be in a very public spot helping at a booth of some sort (like the Apress booth with my book).

There are two times that you will find me as a volunteer with PASS helping out in the Community Zone. What is the Community Zone you ask? Well, it is mostly a place were you can just sack out on the bean bags (I hope they are LoveSacs – they are sooo comfy!). Not just kidding – though you could relax on the bean bags if you wanted.

From the PASS Summit Community Zone page, the CZ is a place to help you stay involved.

Want to stay involved in the SQL Server community year-round? Check out the Community Zone at PASS Summit 2012 and talk with PASS Community Evangelists, user group leaders, SQLSaturday organizers, and other SQL Server community groups about how you can participate.

The CZ is also a place where you can get some swag or get your game card to have a chance at some cool prizes.

The game card is for a networking game that involves you talking to and meeting other people at Summit.  Here is the full description in the email sent to me:

Networking Game:  We will start handing out the cards at the CZ when it opens on Wednesday. The game is actually two parts.

a.       Meet 10 people from outside of the country where you live and learn something interesting about their country. They will be required to write these individuals names down, along with what they learned, and what country they are from. We will have “icebreakers” and networking tips listed on the back of the game card to help them out. Once they have all 10, their card will go into the raffle drawing, which will be held right before the BoF lunch in the Exhibitors Hall (our drawings will be immediately after the Exhibitor raffles are done)

b.      Part two, the spin-off game from the above involves pics. The attendee who manages to get the most pictures taken of themselves with the people they meet, will win top prize. If we have several who get all 10, then we will of course do a drawing. We want to encourage folks to bring their pics by often, so we can copy off and possibly use in slides before the keynote Thursday, but also so we can get them up to flicker. Social Media will also be doing some picture contests, as well as several other fun social media games. The Community Zone will also be on FourSquare among other HOT Summit spots!

c.       Now the prize details:

For the completed networking game card drawing, the first winner drawn will get their choice between a Surface tablet or a $250 Amex Gift Card. We will draw a second person for this first game, they will win a MSFT Arc Mouse.  For the picture contest portion of this, the first drawn winner will receive whatever the first winner didn’t select (either the tablet or the gift card).  And we’ll do a runner up to this game as well, who will also receive a MSFT Arc Mouse. Great prizes, don’t you think!

I don’t know about you, but that sounds like some pretty good stuff to win just for talking to a few people.

If that kind of networking game isn’t your cup of tea, there is still the opportunity to get some daily swag. Each day there will be various new activities tweeted or written on the boards in the CZ.  Go do that activity and you can get some swag.  Easy Peasy Lemon Squeezy – right?

So where is this Community Zone?  Glad you asked.  The CZ will be near the SQLClinic, Bookstore, Exhibit Hall and the Lunch Hall.  It is in the Skybridge on the 4th floor. Directly across from the CZ you will find the PASS kiosks.  I hope you are able to find it and come visit!

*PASS Summit logo is hosted by the PASS Servers and is merely present via a link to the PASS website.

Summit 2012

Categories: News, Professional, SSC, SSSOLV
Comments: No Comments
Published on: November 1, 2012

In case you have been living under a rock (I know it’s not under my rock), here is your notice that Summit 2012 is just a few days away at this point.

I will soon be boarding a jet bound for Seattle for a week full of all things SQL.  When I say all things SQL – there isn’t much exaggeration to it.

During my week at summit, I will be busy from pre-dawn to post-dusk every day.  That is  not an atypical experience for Summit – but the reality hit me firmly as I finalized my daily schedule for next week.

Since I put it together, I decided to share it so others may see all that may be available to them.

I arrive Sunday.  That evening after getting settled in, I plan to join the MCM study group (and maybe have the Sunday night NFL game on in the background).

Monday is a free day of learning hosted by Red Gate and their SQL in the City series. You can see the agenda here. After the event there is the after event social party.  That party overlaps with the Networking Dinner hosted by Andy Warren (twitter) and Steve Jones (twitter) at Gordon Biersch.  This dinner is a pre-registration event (as is SQL in the City).  You can register here (if they have slots available).

Tuesday is a big day.  Tuesday I will be taking the MCM Lab exam in the morning.  Upon completion I hope to decompress for a few hours prior to the evening events.  In the evening we have the Welcome reception/Quiz Bowl sponsored by SQL Sentry.  Immediately following that, there are two events at the same time.  The first is the Speaker/Volunteer party.  The other is another Red Gate event – The Exceptional DBA Awards and SQLServerCentral party. For those from the US, this is also Election Tuesday.  I hope for those from out of town you voted early or are sending in an absentee ballot.

Wednesday we jump into the sessions that I planned on attending (XE, SQLOS and Perf Tuning on my docket).  But in addition to those sessions, I will be at the Apress booth (booth 342) from 12:30 – 1:00 and Wayne Sheffield (twitter) representing our book. And before anything even gets going Wednesday, I will also be at the Summit Community Zone from 7AM to 8AM.  In the evening there is the Exhibitor Reception.  And for anybody into Karaoke – you can do either of the two Karaoke events at about 9PM that evening.  Don’t forget to wear your SQL Saturday shirt(s) on Wednesday.

Thursday I will be at the Community Zone again from 7AM to 8AM.  If you have a kilt – wear it Thursday.  I plan on attending the Query Tuning Mastery, Transaction Isolation Levels, and Recovering Lost Data sessions on Thursday. That brings us to the evening when we have the Community Appreciation Party that runs from 7PM to 10PM.

Friday we are on our last leg wrapping things up and hoping to not be overloaded by this point.  The session I am most looking forward to is the last one of the day by Paul White (twitter). I mean who wouldn’t want to end summit by attending a deep dive session into the Query Optimizer.  In fact I know Wayne Sheffield is presenting during the same time and he was hoping nobody would come to his session so he could attend Paul’s session instead.  Of course he said that in jest, because he wants to do his presentation.  But at the same time he really wants to get to Paul’s session. At the conclusion of this session, my agenda is concluded leaving Friday night my only open night.

I am looking forward to meeting you and chatting. If you see me, say hi. But please don’t try to interrupt my exam. 😉

page 1 of 1

November 2012
« Oct   Dec »

Welcome , today is Tuesday, April 7, 2020