Quick Script – Finding Most Recent Backup

Recently I have had to restore a number of databases into our training environment.   Looking up each backup individually would be time consuming and I prefer to save time whenever possible.  So, instead of lookup up the backup for each database I needed, I thought a quick script would be handy.  Of course, there are multiple ways to gather this information, but this is my way.

I like scripts that generate commands for me, I find it makes things easier.  Therefore this query will generate a generic restore command with some options already supplied.  Feel free to modify the query to include whatever options you need.

Note: this query will only find the last full backup of the database.

Disclaimer:  Do not execute code you find on the internet blindly on Production servers.  Test the script first and then decide if it is appropriate to execute in Production.  

  Author: John Morehouse
  Date: 2016/09/19

  Summary: This script will find the most recent backup as well as generate the RESTORE command for the given database. 
  You may alter this code for your own purposes. You may republish altered code as long as you give due credit. 

;WITH mycte AS(
, MAX(backup_start_date) 'start_date'
, MAX(media_set_id) AS 'Media_set_id'
FROM msdb.dbo.backupset b
b.type = 'D'
AND b.is_copy_only = 0
AND b.database_name NOT IN ('tempdb')
GROUP BY database_name
, myCTE.start_date
, b.physical_device_name
, 'RESTORE DATABASE ' + mycte.database_name + ' FROM DISK = ''' + b.physical_device_name + ''' WITH RECOVERY, REPLACE, STATS=5'
INNER JOIN msdb.dbo.backupmediafamily b ON b.media_set_id = mycte.Media_set_id
ORDER BY mycte.database_name asc

As you can see, the script will give you the last backup and restore command for all of the databases on the instance.  You could easily modify the WHERE clause to only select a single database or multiple if you desire.

I have also used this against linked servers (I know, I know.  Linked servers) to get backup information on databases that reside on other servers that need to be restored.


Posted in Administration | Tagged | Leave a comment

April MVP – Chris Yates

Microsoft_MVP_logoThe April MVP nomination is Chris Yates (B|T).

Chris is one of those individuals that always have a positive outlook on life which infectious.  He hails from Louisville, Kentucky and over the past year I’ve had the pleasure of really  getting to know him.   Chris has a serious passion about the SQL community and if you spend 5 minutes with you’ll be hooked as well.

Chris is involved in many different ways within the SQL community. He’s a presenter, a mentor, blogger and a published author just to name a few things.  In addition to all of this he has recently just stepped up to help me lead the Louisville SQL Server user group which just adds to his vast resume of activities within the SQL Family

Chris is very much into, not only the technical side of things, but also providing leadership and mentorship to the SQL Family.  His blog posts around the subject matter are very insight full and motivational.

Additional Highlights:

  • Chris has been a Friend of Red Gate for 3 years running
  • Contributor for SQL Server Central blog
  • His articles are syndicated on SQL Server Pedia (Toad World)
  • Forum moderator at SQL Brit
  • Marketing Director for the HA/DR virtual chapter
  • Co-author of the DBA Jump Start Free e-book
  • Helps organize/execute the Louisville SQL Saturday events
  • Written case studies for Red Gate
  • PASS Summit Live Blogger

Chris is well deserving of this nomination simply due to his passion on sharing his knowledge and leadership with the community.  I know that he will continue to bring the passion in everything he touches.  The MVP program would do well by adding him to the roster.

Nominate Someone

As I have done previously, I challenge you to nominate someone in your circle that is worthy.  Even if it isn’t for the MVP award, nominate them for something, anything.  Even if you just simply email them telling them Thank You, I assure you it’ll make a difference to them.

If you do want to nominate someone for the Microsoft MVP award, you will need to know their email address and can submit the nomination here.

Posted in Community | Tagged , | 1 Comment

Changing Of the Guard

ArlingtonAs many of you know,  I stay very active within the PASS community.  In late 2007 I got my start, when I helped to organize and the Omaha SQL/BI user group.  I am very proud to say that group is still going strong as of today.  Unfortunately, I had to give up the leadership role when I moved from Omaha, Nebraska to Louisville, Kentucky.  Although the move offered up new opportunities,  it cost me that  role I loved.

When I arrived in Louisville, the local SQL Server user group had already been around for a good 10 years.  Originally started by my friend Malathi Mahadevan (B|T) and then later taken over by a new friend, Dave Fackler (B|T).  Both individuals have done a fantastic job of representing the passion for learning & knowledge through the power of the user group.  I was happy to see the user group is strong and healthy simply due to the time and effort they each have provided.

User group leaders are similar to the soldiers guarding the Tomb of the Unknown Soldier.  Standing steadfast before the group in the name of knowledge, passion, community, stewardship, learning and respect.  The Guardians of the group ensure that knowledge is delivered in the best way possible and offer the community many avenues to learn – through blog posts, meetings, SQL Saturdays, and free training material just to name a few.

There comes a time though, where the Guards need a break.  Standing steadfast for years on end can become overwhelming or life just takes a new direction for them.  So, much like at the tomb, even user groups have a changing of the guards at times.

The time has come.

Changing of the Guard

I’m very happy to announce that along with my fellow guard, Chris Yates, starting in May 2016 we will be taking over the leadership of the Louisville SQL Server user group.  We are honored that Dave has entrusted us to continue to be leaders in our community.  Both Chris and I have lofty goals for the group and have zero intention of letting Dave down.

Chris and I will continue to be watchful of the group.  We will continue to bring in valuable knowledge to each and every meeting.  We hope to expand our horizons a bit with some new ideas,  so stay tuned for those announcements.

I’d like to send a very special thanks to Dave for his leadership of the group for the past years.  Dave has worked very hard to bring a solid avenue for knowledge into our community and his hard work definitely shows in the continued attendance of the monthly meetings. Dave will still be around as I’m sure that both Chris and I will lean on him occasionally for guidance as we begin this new journey.

I’d also like to send a special thanks to my new partner in crime, Chris Yates.  Chris has offered up his valuable time to help run the group with me and I’m looking forward to see where we can take this.

Posted in Community | Tagged , | Leave a comment

March MVP – Gareth Swanepoel

Microsoft_MVP_logoThe March nomination is Gareth Swanepoel (B|T).

If you don’t know Gareth you should make it a point to get to know him.  He originally hails from South Africa but now resides in the United States with his family.

Gareth is a frequent presenter in the SQL community.  He’s given presentations on various topics such as Parallel Data Warehouse, Extended events, column store indexes and others at SQL Saturday’s as well the PASS Summit.  Last year he did a whirl wind tour in South Africa, speaking at two different SQL Saturday events during his trip.  He is also a PASS Regional Mentor (+4 years), the co-leader of the MagicPASS Chapter and a member of the PASS 2016 Summit Program Committee.   In addition to all that, he was also a PASS 2015 Summit volunteer serving as a site ambassador for the event.

Gareth is an individual that always has a smile on his face when you see and usually is accompanied by some sort goodie, quite possibly biltong.  I suggest you to try some, not sure where to find it though, you may have to ask him.

Having seen several of his presentations, Gareth is just as sharp technically as he is witty and friendly.  He reflects what an MVP should strive to be and is very deserving of this nomination.

Nominate Someone

As I have done previously, I challenge you to nominate someone in your circle that is worthy.  Even if it isn’t for the MVP award, nominate them for something, anything.  Even if you just simply email them telling them Thank You, I assure you it’ll make a difference to them.

If you do want to nominate someone for the Microsoft MVP award, you will need to know their email address and can submit the nomination here.

Posted in General Conversations | Tagged , | Leave a comment

Nothing To See Here

In a galaxy far, far, far away a developer of a third party application somewhere in the Hoth system had to implement cell level encryption for sensitive data.  Their weapon of choice was to do this utilizing C# code within SQL Common Language Runtime (CLR).  Though the galaxy is full of other alternatives, this choice was not a bad one.  CLR can be a robust tool to handle such things if needed.

Enter stage left.  The password stored in a table.  In clear text.  

While it is very good that the data was encrypted using a solid encryption methodology, the keys to the kingdom where left in plain sight.  If you follow any known white hacker, you’ll probably know right off that leaving any bread crumbs around makes it easier to crack encrypted values.

The only saving grace is that fact that the table that holds the password is actually in a separate database; one that is controlled by the Rebellion.  Armed with this knowledge, we can trick the application by using a view instead of a table in conjunction with a built-in SQL function, DecryptByAutoCert.  Books Online tells us that this function,

Decrypts by using a symmetric key that is automatically decrypted with a certificate.”

 Essentially, this means that if you have a symmetric key encrypted using a certificate, this function will automatically decrypt the values without any additional input.

So the process would look something like this:

  1. Add a VARBINARY column to the existing table.
  2. Create a database master key (if one doesn’t exist)
  3. Create a certificate using the database master key
  4. Create a new symmetric key using the certificate.  This will be used to encrypt the actual password.
  5. Rename the existing table to something else, like dbo.NothingToSeeHere
  6. Create a new view using the function referencing the newly renamed table
  7. Test. Test. Test. Test. Test. (Did I mention test?)

First, let’s look at the table structure as it stands.  This is just an example, no real passwords were hurt in the making of this post.


We will create the encryption parts that will be used to further protect the information.

USE [DatabaseNameGoesHere]
-- add in a database master key with a strong password

-- create a certificate
WITH SUBJECT = 'Test Certificate',
EXPIRY_DATE = '20991231';

-- create a symmetric key using the above cert

From below we see that the certificate and corresponding symmetric key were created.


Now that those are present, we can add in a new VARBINARY(128) column.  This new column will be used to store the encrypted password.

-- open the key
-- add a varbinary column to the table first
ALTER TABLE dbo.Table1
ADD Encrypted VARBINARY(200)
--update the altered table with the encrypted value using the symmetric key
SET Encrypted = ENCRYPTBYKEY(KEY_GUID('DBEncryption'),[password]) --clear_text_password is the passwd column in Table1

Using the ENCRYPTBYKEY function, it will do the heavy lifting of encrypting the data.


Once the data has been encrypted, we can move forward with creating a new view that will be used to “head fake” the application. The view is named the same as the original table therefore the change is seamless to the application.  The application doesn’t know if it’s calling a table or a view so that’s why this works.

-- close the key

-- rename the table1 to table1_Updated
-- create a view called Table1 so that the view matches the old table name; this allows this to be seamless to the application
EXEC sp_rename 'dbo.NoNo', 'dbo.NoNo_Maybe'
CREATE VIEW dbo.NoNo -- same name as the former table
FROM dbo.NoNo_Maybe
-- Voila!

You can see below that the view returns the decrypted value.


The password is now fully encrypted utilizing SQL Server encryption.  Still, in order to use the view properly, the database users would need to be granted:

  • VIEW DEFINITION on the symmetric key
  • CONTROL on the certificate

This further secures the information allowing you to add in more granular control on who can decrypt the data.


This isn’t the data you are looking for

As a side note,  in the event that this database is captured from the R2-D2 unit, the encrypted value won’t be able to be decrypted by the Empire until the database master key is regenerated with the new corresponding service account master key of the new instance.

Of course, you would have to test this very thoroughly in your environment to ensure that nothing breaks.

Posted in Encryption | Tagged | 1 Comment

February MVP – Melody Zacharias

Microsoft_MVP_logoThe February MVP nomination goes to Melody Zacharias (B|T).

Melody hails from our neighboring country to the north, beautiful Canada. While we all joke Melody about her quick and frequent apologies (Sorri), she definitely has nothing to apologize for when it comes to her dedication to the SQL Community.  She is what I refer to as a gem among the rough for sure. The SQL Family is definitely better with her a part of it.

A little history on this up comer….

At Summit 2014, I was honored when Melody approached me and asked me to help mentor her in public speaking.  Having spoken at many SQL Saturdays, I had someone experience in the topic I could definitely share.  So, I was more than willing to jump at the chance to help her out.

Shortly after that Summit, I convinced her to submit to one of my favorite events, SQL Saturday Albuquerque.  To her surprise, she was selected and presented her first SQL Saturday in 2015.

From that point on, she was off and running like a bolt of lightning. Melody quickly got the speaking bug, as most of us do, and never looked back.  Not only has Melody become a frequent speaker, she is now an international speaker as well recently speaking in Australia.

Some highlights…

  • Regional Mentor (RM) for PASS
  • Chapter Lead
  • She is active in the Women In Technology (WIT) activities for PASS
  • PASS Summit Volunteer
  • Outstanding PASS Volunteer award recipient
  • Blogger
  • SQL Saturday speaker,
  • 2015 PASS Summit Speaker
  • International speaker

It has been an extreme honor to watch Melody grow within the SQL Family.  She has a passion for technology, speaking, and is now also mentoring others along the way.

I think Melody embodies what a Microsoft MVP should consist of and I am happy to nominate her for this award.

Nominate Someone

As I have done previously, I challenge you to nominate someone in your circle that is worthy.  Even if it isn’t for the MVP award, nominate them for something, anything.  Even if you just simply email them telling them Thank You, I assure you it’ll make a difference to them.

If you do want to nominate someone for the Microsoft MVP award, you will need to know their email address and can submit the nomination here.

Posted in Community | Tagged , | Leave a comment

January MVP – Mike Fal

Microsoft_MVP_logoIn case you missed it, back in November I blogged about the importance of nominating individuals within the SQL Server community for the prized Microsoft MVP award. I vowed, then,  each month to nominate one deserving individual.  This is the first of 12 nominations that I’ll be blogging about.  I am aware that it is March so please forgive me on my tardiness, I promise to play catch up.

My January nomination for Microsoft MVP is Mike Fal (B|T).  If you don’t know who Mike is, you should.  He is, in my opinion, not only a PowerShell expert but also an outstanding DBA.   He is a frequent speaker at a multitude of events, including SQL Saturday’s, PASS Summit, as well as SQL Server user groups both onsite and remote.  Mike is also an avid blogger at http://www.mikefal.net.  You’ll find he is usually blogging about Powershell in conjunction with SQL Server but he has a plethora of topics.  I highly recommend you add him to your RSS feeds.

Mike is more than willing to help out a fellow DBA in just about any topic.  I have personally consulted with him on several occasions.. There is no doubt, in my mind, that if I need help with something, regardless of the topic, Mike would be there to lend a helping hand.  I think that he showcases what an MVP should be and he deserves the award.

Some highlights:

  • Frequent SQL Saturday Speaker
  • Involved with organizing past SQL Saturday events in Denver
  • Former VP of Events of the Denver SSUG
  • Answers questions on #sqlhelp & #poshhelp via Twitter
  • Avid blogger
  • Speaks at various user groups
  • PASS Summit speaker

Nominate Someone

As I have done previously, I challenge you to nominate someone in your circle that is worthy.  Even if it isn’t for the MVP award, nominate them for something.  If they are a PASS Volunteer, the outstanding PASS Volunteer award is another excellent choice.   Blog about their achievements and get them recognized.  I assure you it’ll make a difference to them and our community.

If you do want to nominate someone for the Microsoft MVP award, you will need to know their email address and can submit the nomination here.

Posted in Community | Tagged , | Leave a comment

Resolved: A Duty to Act

If you are a member of PASS, you probably got an email from our President, Thomas LaRock.  I interpreted his email as essentially a reminder that when attending PASS events we should all be professional and responsible for our actions.   The email was very well written and wasn’t pointed towards just men or women, but all of us.

Along with Tom’s email, PASS Board member Wendy Pastrick bravely put out a post about one of her own personal experiences at the 2015 PASS Summit.  I consider Wendy a friend and I’m very proud that she reported her incident.  It’s not easy to do that but that was the right thing to do.   Wendy deserves a lot of kuddos for doing that.  Well done Wendy.

In a Previous life…

Those that know me, know that in a former life I was a volunteer fire fighter & EMT.  In that role, I would sometimes be placed into a situation where I didn’t have a choice if I reported something or not.  For instance, any suspected evidence of child or elder abuse; I was required under state and federal laws to report it to authorities.  Not reporting it wasn’t an option. Granted, the actions of individuals at recent PASS events are not necessarily on the same plane as child abuse, but nevertheless I think that it should be handled in the same fashion.

Additionally, in one of my other former lives I was a bouncer.  I know, big shock, right? Anyhow, being in the role of people depending on me to protect others makes me want to jump in a take action when I hear of situations like this. I’d like to think that I’m the type of guy that can be trusted and will take care of business when needed. Not to mean that I would initiate physical fights (although I was in a couple) but rather I know how to handle people in such a way to resolve the issue.

Some History

That all being said. At the 2014 PASS Summit, I was notified by a friend that she had received questionable social media messages. She showed me the messages and I too agreed it was of an unprofessional nature.

At the time, I was a Regional Mentor.   I had a duty to act and to uphold the code of conduct that was passed by the Board of Directors.   So to make a long story short, I consulted another regional mentor that I trust (with my life) and we agreed that it was our job to report the incident.  So we did.

We both recognized it was NOT our job to be the judge and jury.

It was NOT our job to confront the perpetrator.

It WAS our job to make the issue known to the proper authorities. Note that my friend was very capable of talking to correct people on her own, however she was unsure whether she was over reacting and was worth reporting. So we took action on her behalf, we found out about the incident, we acted.

My Resolve

Let me be honest.  I’m tired of hearing of my good friends having issues with mis-conduct by members of PASS.  I am also tired of feeling like I don’t have any way to help and I know others do to. I’m a firm believer that things cannot be fixed if the people that can fix it aren’t made aware of it. Therefore I resolve that:

  • If you bring it to my attention, I will report it to the proper PASS authorities, whether you want me to or not.
  • If I observe such actions directly, I will report it to the proper PASS authorities.
  • I will make myself available to anyone who finds themselves in an uncomfortable situation, male or female. Just get my attention and I am there.

Just so I am clear.  I will not physically engage anybody in any way shape or form.  I do not and will not condone any type of physical intervention unless it’s a matter of safety.

To My Fellow Men

Given that most of the perpetrators are most likely men I have a few tips for you from my bouncer days.  From talking to my female friends that attended Summit one of their biggest issues was men’s roaming hands in a crowd. I’m not a small guy (I’ve been called a bull ox a time or two) so I know a thing or two about moving through a crowd:

  • Put your hands up.  If you have to move through a crowd of people, both men and women, put your hands up so that you don’t “accidentally” brush up against someone.
  • If you have to get someone to move, say “Excuse me”.  If they don’t hear you, say it louder.
  • Get the attention of people that they might be talking to.  Most individuals when they see you trying to get by will get the attention of the person in your way for them to move.
  • Find an alternate path through the crowd.  Crowds are like waves of grain in the wind.  They move and flow as people move around and enjoy the event.  If a particular path is congested with people, back off and look for a different path.
  • As a last resort, if you have to touch someone to get their attention, tap them on the shoulder while again saying “Excuse Me”.  With a single index finger.  Not your whole hand.  A finger.  DO NOT TOUCH THEM anywhere else.


Fellow #sqlfamily member Erin Stellato put out a fantastic post about being the change. Her article is well written and is spot on.   We all can be the change that is needed.  Be that change.

It’s our job as PASS members as well as just being human to make it a better place for everybody.  I take great pride in our #sqlfamily as well as being a member and I am here for anyone who needs me.


Posted in Community | Tagged , | 5 Comments

MVP of The Month

If you follow Grant Fritchey (B|T) at all, you know that every month he picks a “Speaker of the Month”.  Grant attends SQL events over the course of a month.  Out of all of the sessions he attends during that month, he will pick a winner and write a blog post about it.

I applaud him and think this a great way to showcase speakers, both new and old, to the general public.

As a side note, if you are not following Grant, you should be.  He’s a MVP, on the PASS Board of Directors and he knows a thing or two about SQL Server.

Not Everyone is a MVP But They Can Be

There are a ton of individuals out there that do a lot of work for the SQL community and have yet to achieve MVP status.  They, like me, love to share and give back to others. Having been nominated myself, I can tell you that knowing that someone took the time to nominate you and to recognize your efforts is a complete honor and a huge motivation to do more.

With that being said, much like Grant’s Speaker of the Month, I’m going to do a nominate “MVP of the Month” for 2016.  Essentially, each month next year I will nominate one individual that I think is deserving of the award.  I will take note of individuals speaking physically or virutally, at SQL Saturdays or other events.  They may not be a speaker but contribute and standout, via Twitter, help in online forums or any other medium that showcases their “MVPness” I deem appropriate.

After nominating the individual, I will blog about that person as to why I think they deserve the famed MVP award.  Start watching my blog in January for my first selection!

Nominate Someone

I can speak from firsthand experience that being nominated is indeed an honor.  If you know of someone that has shown the values of being an MVP and isn’t, take the time to nominate them.  I assure you that you will make their day.

I challenge you to nominate someone in your circle.  Even if it isn’t for the MVP award, nominate them for something.  If they are a PASS Volunteer, the outstanding PASS Volunteer award is another excellent choice.   Blog about their achievements and get them recognized.  You will be amazed on how much it can make a difference.

If you do want to nominate someone for the Microsoft MVP award, you will need to know their email address and can submit the nomination here.

Need more convincing?  Fellow SQL Family member Mark Broadbent (B|T) has similar advice.  He has a great blog post about it here.  Do not just take my word for it.

Go recognize someone.



Posted in Community | Tagged | 3 Comments

Idera ACE 2016

Idera_new_logo If you have been around the SQL Community long enough, you probably have heard of a software company called Idera.  They make a wide range of useful SQL Server tools.   What you might not know, is that they are also heavily invested into the SQL Community.  One way they invest is with their Idera ACE program which they have re-launched for 2016.

ACE’s, which stands for Advisors & Community Educators, are essentially members of the community who have a passion for sharing their knowledge and helping the community at large.

Idera helps these members pursue their passion by sponsoring their travel to events such as SQL Saturday’s as well as offering guidance on improving soft skills.

Iidera_ace‘m a 2016 Idera ACE

I am humbled to announce that I will be an Idera ACE for 2016.  This is truly an amazing opportunity for me which lets me continue giving back to the community.

As an added benefit, the program will help me to get to events that I might not have otherwise been able to. As most my speaking engagements are done out of my own pocket, having Idera to help offset some of the expense is an amazing gift.

There are a couple of other key benefits that I really like about the program.

  1.  To be part of the program, I don’t have to sell their products.  I will, however, thank Idera in my slides & presentations for sponsoring me. I don’t claim to be an expert in their tools, but I’ll be able to learn more about them.  This program is about the community, not increasing the bottom dollar.
  2. Beta testing.  As an Idera ACE I may get the chance to participate in some beta testing of their tools.  I’ll potentially have the ability to help improve their tools for data professionals everywhere.  I find that very exciting.
  3. I’m hoping to get to incorporate some of their tools in my presentations.  One of their tools in particular, Virtual Database, is a really useful product that I have implemented in a unique way to solve a couple of issues.  I’m looking forward to show that to others and playing with that even more.

Thanks Idera!

Idera is investing in me to further my involvement with the SQL Community and for that I am truly grateful.  This is a fantastic opportunity and I have zero intention of letting you down.

Posted in Community | Tagged , | 2 Comments