Jeremiah Peschka, SQL Server Developer

Let’s say that you want to clear out your old backup history. Why would you want to do that? Let’s also assume that your maintenance plans (yes, maintenance plans) have not been cleaning up after themselves.

If this has happened over a small period of time, you can just do this:

EXEC SP_DELETE_BACKUPHISTORY DATEADD(m, -1, GETDATE());

But what if you have a lot of backup history? Trying to delete two years of backup history will slow things down incredibly on your server and take a very very long time. To the point where your boss will come over and frantically say ‘Hey, why is the server refusing connections?’

Instead you’ll want to use something like this:

DECLARE @oldest_date AS DATETIME;
 
IF OBJECT_ID('tempdb..#dates') IS NOT NULL
  DROP TABLE #dates;
 
SELECT DISTINCT DATEADD(d, 0, DATEDIFF(d, 0, backup_start_date)) AS d
INTO #dates
FROM dbo.backupset
 
SELECT @oldest_date = DATEADD(d, 2, MIN(d))
FROM #dates
WHERE d < DATEADD(mm, -1, GETDATE())
 
EXEC SP_DELETE_BACKUPHISTORY @oldest_date;

Why isn’t this in a loop? Because I have a sinus infection and I didn’t feel like being that clever while I’m working from home. That’s why. You probably should be clever and put this in a loop. And put a nice little wait at the end of it that waits for a minute or two before repeating the whole thing. Me? I just sit here and zone out for a few minutes. The point is, this works.

If you’ll excuse me, I’m going to get back to watching day time TV and pressing F5 every 3 minutes.

The Columbus Give Camp is only two weeks away but it’s still not too late for both charities and volunteers to sign up.

GiveCamp is a weekend-long event where software developers, designers, and database administrators donate their time to create custom software for non-profit organizations. This custom software could be a new website for the nonprofit organization, a small data-collection application to keep track of members, or a application for the Red Cross that automatically emails a blood donor three months after they’ve donated blood to remind them that they are now eligible to donate again. The only limitation is that the project should be scoped to be able to be completed in a weekend.

During GiveCamp, developers are welcome to go home in the evenings or camp out all weekend long. There are usually food and drink provided at the event. There are sometimes even game systems set up for when you and your need a little break! Overall, it’s a great opportunity for people to work together, developing new friendships, and doing something important for their community.

At GiveCamp, there is an expectation of “What Happens at GiveCamp, Stays at GiveCamp”. Therefore, all source code must be turned over to the charities at the end of the weekend (developers cannot ask for payment) and the charities are responsible for maintaining the code moving forward (charities cannot expect the developers to maintain the codebase).

The deadline is July 8th, so get your charity proposals and volunteer submissions in!

I know of three presentations coming up in the next month that I thought I’d share with everyone, if only because I’m involved with all three.

The first is Andy Leonard’s presentation on Incremental Loads, Change Data Capture, and SSIS 2008 in scenic Columbus, OH. The event takes place at 6:30PM on July 9th at Battelle for Kids. Lest you think Andy will be in Columbus, he will be presenting via LiveMeeting. This means that you can attend via LiveMeeting!

Presentation the second is Aaron Alton’s presentation on Simple Data Change Tracking in SQL Server. This is a virtual presentation that is happening on July 14th at 2PM Eastern.

Finally, on July 23rd I will be presenting at the Central Ohio .NET Developers Group. The event starts at 6:00 PM and it located at Microsoft building on Polaris Parkway in Columbus, Ohio. My presentation is titled From Tables to Objects: Making Your Database Work With You - it’s all about using database features to enhance the quality of your ORM. There is no LiveMeeting available for this, but I will either be recording via Camtasia or else will record it after the fact.

SQL Server

Deploying Databases From Visual Studio Team System Database Edition Grant Fritchey has written an article about using Vistual Studio Team System Database Edition to deploy databases and database changes. If you haven’t taken a look at Data Dude, now is the time to do so, it’s a great product!

Declarative Database Development This is a nice introduction into how to develop using Data Dude (VSTS Database Edition).

Development

Fluent NHibernate And You Josh over at Computerist Solutions has a quick introduction via code snippets to Fluent NHibernate.

Stuff & Things

Implement Advanced “Siestas” for Improved Sleep I’m a big fan of naps. Any excuse to use them to improve my life is a fine by me.

Networking for the Shy Entrepreneur

You know what? I don’t actually know how many days are left in the contest. All I know is that Bill Graziano just sent me an important email that I need to pass on to all 5 of my readers who aren’t my mom. (Hi mom)

There isn’t a lot of time left, but you still have a chance to win your PASS Summit registration, hotel, or one of a number of other cool prizes. If you don’t win, you can still go to PASS and spend time with me, or Brent Ozar, or Tim Ford, or Thomas LaRock or Blythe Morrow (Blythe is part of why PASS works). But, if eating waffles, bacon, or having treadmill races isn’t your cup of tea, there are a number of other reasons you might want to go to PASS this year.

Allow me to enumerate:

• More than 160 top-quality sessions scheduled across 5 tracks and 14 pre/post-conference seminars (I’m an alternate presenter, bet you didn’t know that!)
• Great chances to network with awesome people. There are even sessions to help you improve your networking skills like this one with Don Gabor.
• A great lineup of top experts, authors, presenters, thought-leaders, and community influencers, including Kalen Delaney, Paul Randal, Kimberly Tripp, Itzik Ben-Gan, Kevin Kline, Greg Low, Louis Davidson, Steve Jones, Andy Warren, Brian Knight… and the list goes on and on (look, I stole that last line from some PASS copy, it’s 12:05 AM Eastern time, I’m not going to be all that original).

So, here’s how the contest works:

1. Write 250 words or less about the best thing you learned at PASS.
2. Send your submission or a link to your blog to contact@sqlpass.org with the subject line ‘Best Thing I Learned at PASS Summit

It’s really that easy. Check it out if you don’t believe me.

Point is, I’m going to be there.

Now, to find out how much time you have left, you need to

1. open up SQL Server Management Studio (Express Edition works just fine).
2. Open up a new window and paste the following code into the new query window:

   SELECT DATEDIFF(dd, GETDATE(), '20090701');

3. Press F5 or CTRL + E or click Execute

You can enter three times, so enter three times. Three chances to win!

If you need help convincing your boss to send you, check out this great ROI or send them a letter or just send them the link to the conference agenda.

One thing I learned at PASS: it’s possible to include references to Krull, ballet, ice cream, and bacon in the same conversation.