Welcome to the MidnightDBA Website!

Welcome to MidnightDBA.com! Here is the place we put our free technology tutorials. We cover various aspects of SQL Server and PowerShell, and whatever else strikes our fancy. You’ll also find recordings of our classes – we speak at user groups and conferences internationally – and of our live weekly IT webshow, DBAs@Midnight. As long as you’re here, check out our blogs, and our other sites: we review books and products on ITBookworm.com, and we offer our career workshop for sale at DBARoadmap.com

Our Latest Videos

Change FillFactor

Filmed: 10/19/2014

Length: 00:04:21 minutes

Learn how to change the FillFactor both at the database and at the table levels.

Click here to watch the video!
Click here to download the video!

Include and Exclude

Filmed: 10/19/2014

Length: 00:03:16 minutes

There are different ways to include and exclude both databases and tables. Here I'll show you exactly how to efficiently manage the objects you want to process.

Click here to watch the video!
Click here to download the video!

Print Statements

Filmed: 10/19/2014

Length: 00:03:27 minutes

You don't have to alter the procedure in order to print reindex statements so you can run indexes manually. This is an excellent feature that we use all the time to help troubleshoot specific index issues.

Click here to watch the video!
Click here to download the video!

Regex Exclude

Filmed: 10/19/2014

Length: 00:03:56 minutes

We have advanced exclusions in Minion Reindex. You can do anything you want with our powerful regex exclusion feature. Come see how it works.

Click here to watch the video!
Click here to download the video!

Reorder Objects

Filmed: 10/19/2014

Length: 00:04:58 minutes

You can process databases and tables in any order you like. Let me show you how much power and flexibility you have with Minion Reindex.

Click here to watch the video!
Click here to download the video!

Settings Tables

Filmed: 10/19/2014

Length: 00:17:25 minutes

You can control almost anything with the settings tables in Minion Reindex. Here I'll show you how you can completely customize your settings without creating any extra jobs.

Click here to watch the video!
Click here to download the video!

Move DBs with Regex

Filmed: 9/2/2014

Length: 00:10:55 minutes

Use the power of Regex to format move statements for hundreds of DB files all at once. This will not only standardize your processes, but make them much more stable as well. This blog post has the demo code: Demo Code

Click here to watch the video!
Click here to download the video!

Change Excel Delimiter

Filmed: 8/16/2014

Length: 00:04:40 minutes

Here I'm going to show you how to changed the delimiter when you output Excel data to a CSV. By default you can only use a comma as a delimiter, and changing it isn't obvious. This isn't the kind of thing that comes in handy very often, but when it does you'll love knowing this trick.

Click here to watch the video!
Click here to download the video!

What is a Stored Procedure

Filmed: 3/11/2014

Length: 00:10:18 minutes

Here Jen explains what a stored procedure is, and how to create a basic one with no parameters. She also compares it with other objects stored in the DB.

Click here to watch the video!
Click here to download the video!

Script PK/FKs in Powershell

Filmed: 5/10/2013

Length: 00:07:17 minutes

Here I show you how to script out objects that belong to tables like primary keys, forgeign keys, constraints, and indexes with powershell. It's really a lot easier than you think and it allows you to reproduce these objects as often as you like.

Click here to watch the video!
Click here to download the video!

Object Properties in Powershell

Filmed: 4/19/2013

Length: 00:12:03 minutes

This is a great video that shows you how to look at the properties collection in objects and explains what the different properties mean. So you can see whether a property is writeable and tell when it's dirty and you need to use the alter() method. It's not the kind of thing you'll use all the time, but it's really good to have it when you do.

Click here to watch the video!
Click here to download the video!

Get into SQL

Filmed: 1/9/2013

Length: 00:06:44 minutes

Have you ever found yourself locked out of SQL because your account doesn't have rights and you don't know the sa password? Here I show you how to get access so you can make yourself a sysadmin.

Click here to watch the video!
Click here to download the video!

Mouth Wide Shut: Coherent Interviewing

Filmed: 10/18/2012

Length: 1:13:38 minutes

Sean and Jen lend their unique style of teaching to talk to you about the ins and outs of passing a technical interview. You can't afford to miss this session if you plan to stay in IT.

Click here to watch the video!
Click here to download the video!

Change Startup Params in Powershell

Filmed: 6/1/2012

Length: 8:40 minutes

I heard someone today say that you can't change startup parameters in Powershell. So I made this vid to set the record straight.

Click here to watch the video!
Click here to download the video!

Drop Tables in Powershell

Filmed: 5/25/2012

Length: 5:49 minutes

Today I'm dropping tables based off of a regex expression and by rowcount. Good stuff here. The power of the shell shines through with the easy regex expression.

Click here to watch the video!
Click here to download the video!

Server Configuration Settings in Powershell

Filmed: 5/25/2012

Length: 10:07 minutes

I love making vids on the trickier stuff and this one is it. Here we're talking about duplicating sp_configure in Powershell. It's pretty easy, but there's a trick to it.

Click here to watch the video!
Click here to download the video!

Truncate Tables in Powershell

Filmed: 5/25/2012

Length: 4:10 minutes

Here I show you how to truncate a mass of tables in the same schema. Of course, you don't have to limit it to a single schema if you don't want.

Click here to watch the video!
Click here to download the video!

Fix AutoShrink in Powershell

Filmed: 5/24/2012

Length: 9:15 minutes

Here I show you how to turn off AutoShrink on a DB and then how to make sure it's turned off on all your DBs.

Click here to watch the video!
Click here to download the video!

Get SQL Server Root Dir in Powershell

Filmed: 5/24/2012

Length: 7:31 minutes

Here we're finding the root install directory for SQL Server.

Click here to watch the video!
Click here to download the video!

Attach DBs in Powershell

Filmed: 5/23/2012

Length: 13:15 minutes

This continues the series on detach/attach for DBs. Along the way I show you how to deal with a powershell provider bug, work with the stringCollection object, and use all 4 overloads for the AttachDatabase method.

Click here to watch the video!
Click here to download the video!

Calling Detach Methods in Powershell

Filmed: 5/22/2012

Length: 7:28 minutes

Detaching DBs in Powershell is pretty easy, which is why you should learn how to do it. It takes less typing than the t-sql version. However, there are also methods for discovering which files belong to the DB that was detached and that can be invaluable when you're trying to attach a DB on a big server and you don't know where all the files may be.

Click here to watch the video!
Click here to download the video!

Get IP and DNS in Powershell

Filmed: 5/22/2012

Length: 9:12 minutes

Getting IP address and DNS info from your servers in a useful format isn't as straightforward as you would think. Here I'm going to show you how to get that info painlessly and in a way you can use for all your automated processes.

Click here to watch the video!
Click here to download the video!

Change Schema in Powershell

Filmed: 5/18/2012

Length: 8:26 minutes

Changing object schemas is easy enough in T-SQL when it's a single object, but it get considerably more complicated when you've got several objects to change, or even several thousand. This is where Powershell comes in. Here I show you how to very easily change the schema of as many objects as you like in just a few seconds. And I throw in some regex in there for good measure. Let's have some fun.

Click here to watch the video!
Click here to download the video!

Change SQL Server Permissions in Powershell: Part 1

Filmed: 5/17/2012

Length: 24:42 minutes

Here I show you how to change permissions in SQL Server using Powershell. There are 2 methods I discuss and I even show you some of the problems you can run into. This is the 1st in a series so be sure to watch the 2nd one too. In This video I also reference a couple other vids. Those are here:
Scripting DB Objects in Powershell (Advanced Topics)
Cycle Error Log on all Servers using Powershell

Click here to watch the video!
Click here to download the video!

Change SQL Server Permissions in Powershell: Part 2

Filmed: 5/17/2012

Length: 10:47 minutes

Continuing from the 1st video, here I show you how to capture useful errors from the method call. I explain how to Trap the errors and loop through InnerException to make sure you get all of them. If you saw the last vid, you can't miss this one.

Click here to watch the video!
Click here to download the video!

Read SQL Server Error Logs in Powershell

Filmed: 5/15/2012

Length: 18:09 minutes

Would you like to work with the SQL Server error logs in Powershell? Well here I'm gonna show you how. Not only do we pull in the current log, but I also show you how to get all of the historical logs for all the instances on the server as well. Good stuff.

Click here to watch the video!
Click here to download the video!

Cycle Error Log on all Servers using Powershell

Filmed: 5/14/2012

Length: 18:11 minutes

Here I show you how to use the Carrier Method for using powershell to cycle all your error logs. This is an excellent way to get started using powershell, and I use it even though I know it fairly well.

Click here to watch the video!
Click here to download the video!

Largest SQL Server Installs

Filmed: 3/16/2012

Length: 1:20:51 minutes

Kaylan Yella brings us another excellent session, this time on the largest SQL installs. If you've ever wanted to have some ammo for those people in your office who blame the bad DB performance on some sort of SQL Server threshold instead of their bad coding, then here it is. Kal shows us what the best in the world are doing with SQL Server and trust me, your install is nowhere close to reaching a threshold.

Click here to watch the video!
Click here to download the video!

Dealing with Sql_variant in SSIS

Filmed: 2/14/2012

Length: 7:41 minutes

Every now and then you come across something you don't want to deal with that you're forced to handle in your SSIS packages. Here I'll discuss techniques for handling the Sql_variant data type in your packages so your data loads properly.

Click here to watch the video!
Click here to download the video!

Change Agent Job Properties in Powershell

Filmed: 2/9/2012

Length: 18:21 minutes

You may have occasion to change the job step properties on multiple agent jobs and here I show you how. The possibilities are almost endless here and I explore a few of them with you. You can see the full script in the accompanying blog post: http://www.midnightdba.com/DBARant/?p=791

Click here to watch the video!
Click here to download the video!

Best Habit for Coders

Filmed: 1/1/2012

Length: 5:02 minutes

There's a step you can make in your code to improve it many times over. It's an easy step to take, but it can make all the difference. You shouldn't overlook this step so come learn how to make all your code tons better.

Click here to watch the video!
Click here to download the video!

Querying Dev Metadata

Filmed: 12/31/2011

Length: 6:33 minutes

Sometimes you need to get info on objects you've created. Here you can see how you can query for this info, and even learn a cool trick for greatly simplifying the process.

Click here to watch the video!
Click here to download the video!

Change Users Password

Filmed: 8/29/2011

Length: 4:02 minutes

Users don't need special permissions to change their own passwords, but there is a minor trick to it. Here I'll show you 3 ways that ordinary users can changed their own passwords without any elevated privileges.

Click here to watch the video!
Click here to download the video!

UNION and UNION ALL

Filmed: 7/29/2011

Length: 5:23 minutes

Here I talk about UNION and UNION ALL, and the different rules about using ORDER BY, GROUP BY, SELECT INTO, and INSERT INTO with a UNION/UNION ALL query.

Click here to watch the video!
Click here to download the video!

Server Properties

Filmed: 7/28/2011

Length: 11:19 minutes

Have you ever wanted to be able to view instance-level stats or change certain values without having to use the GUI? Here I'll show you how to do all your boxes with Powershell.

Click here to watch the video!
Click here to download the video!

Insert Into vs. Select Into

Filmed: 7/14/2011

Length: 13:01 minutes

Knowing how and when to use Insert Into or Select Into can quite often make or break your load process. Here I show you how to tell the difference between them and what their effects are.

Click here to watch the video!
Click here to download the video!

Windows Crash Dump Analysis

Filmed: 7/8/2011

Length: 6:41 minutes

If Windows has ever bluescreened on you, then you know how important it can be to find the issue. Windows creates a crash dump file whenever it bluescreens and here I'm going to show you how to use that dump file to figure out what caused the bluescreen.

Click here to watch the video!
Click here to download the video!

Best Backup Class Evar: 5 of 6

Filmed: 6/16/2011

Length: 1:09:02 minutes

The 5th in the backup class at NTSSUG.

Click here to watch the video!
Click here to download the video!

Best Backup Class Evar: 6 of 6

Filmed: 6/16/2011

Length: 45:48 minutes

The 6th in the backup class at NTSSUG.

Click here to watch the video!
Click here to download the video!

Best Backup Class Evar: 4 of 6

Filmed: 5/19/2011

Length: 30:20 minutes

The 4th in the backup class at NTSSUG.

Click here to watch the video!
Click here to download the video!

Database Performance

Filmed: 3/17/2011

Length: 1:46:57 minutes

Paul and Kim Tripp from SQLSkills.com came to teach us their award-winning presentation on DB performance.

Click here to watch the video!
Click here to download the video!

Best Backup Class Evar: 3 of 6

Filmed: 3/17/2011

Length: 23:03 minutes

The 3rd in the backup class at NTSSUG.

Click here to watch the video!
Click here to download the video!

Best Backup Class Evar: 2 of 6

Filmed: 2/25/2011

Length: 11:19 minutes

The 2nd in the backup class at NTSSUG.

Click here to watch the video!
Click here to download the video!

Waits and Queues

Filmed: 1/20/2011

Length: 33:27 minutes

Thomas LaRock from Confio came to give us this really good talk on waits and queues. And as an added bonus, the entire PASS board was here to listen. OK, we forgot to start the recorder but you only miss like 15mins of the presentation. The good news is all the demos are there.

Click here to watch the video!
Click here to download the video!

Best Backup Class Evar: 1 of 6

Filmed: 1/20/2011

Length: 57:31 minutes

Here's the first of a series of backup/restore classes taught at the NTSSUG. This series is meant to cover everything about backups... or at least as much as we can squeeze into 6 sessions. You'll learn how to put together an enterprise backup solution, tune your backups for maximum performance, and manage them across your company no matter how many servers you have. The file used for this class can be found here.

Click here to watch the video!
Click here to download the video!

Hack-attak a damaged DB

Filmed: 1/5/2011

Length: 15:37 minutes

What do you do if you have a set of data and log files you can't attach because the log is damaged? Here I show you a way to attach it, fix it, and get going again. Paul Randall also discussed this in his blog here: Hack-attack a damaged DB

Click here to watch the video!
Click here to download the video!

Sum DB Sizes with Powershell

Filmed: 1/4/2011

Length: 08:54 minutes

Quite often it can be useful to not only get a list of DB sizes, but also sum it up in the last line. This can be useful for estimating backup file sizes, server sizing and more. Here I'll show you a couple ways to sum up DB sizes so take your pick.

Click here to watch the video!
Click here to download the video!

Replace sp_MSForEachDB with Powershell

Filmed: 12/29/2010

Length: 14:15 minutes

A user request came in to write something in powershell that would replace sp_MSForEachDB. I took it a step further and showed you how to make it even more flexible for an enterprise environment.

Click here to watch the video!
Click here to download the video!

Monitor Backups with Powershell

Filmed: 9/30/2010

Length: 34:37 minutes

I know you guys are just begging for an easy way to monitor your backups and alert when one hasn't been backed up recently. Here's a great way to do it in powershell, and it's easy as pie.

Click here to watch the video!
Click here to download the video!

Audit Service Accounts with Powershell

Filmed: 9/29/2010

Length: 00:17:03 minutes

How would you like to be able to audit your service accounts with powershell so you can tell how many boxes are running on the same account? Well now you can, and it's very easy. Come let me show you how to write a script that you can use to audit all of the SQL service accounts in your environment.

Click here to watch the video!
Click here to download the video!

Get Table Stats with Powershell

Filmed: 6/2/2010

Length: 18:17 minutes

Let's get stats for every table in the DB and put them in a file so we can send them to a customer or view them ourselves. It's also easy enough to put in a table to query.

Click here to watch the video!
Click here to download the video!

SQLSaturday 35: 1 of 3

Filmed: 5/31/2010

Length: 15:00 minutes

Come see our first LIVE taping ever. We have a good time, give away some great prizes and even manage to get a little techie.

Click here to watch the video!
Click here to download the video!

SQLSaturday 35: 2 of 3

Filmed: 5/31/2010

Length: 15:07 minutes

Part 2 of SQLSaturday 35.

Click here to watch the video!
Click here to download the video!

SQLSaturday 35: 3 of 3

Filmed: 5/31/2010

Length: 11:13 minutes

Part 3 of SQLSaturday 35.

Click here to watch the video!
Click here to download the video!

T-SQL Code Sins

Filmed: 4/15/2010

Length: 50:53 minutes

SPEAKER: Jen McCown. Do you know how to set yourself for good coding practices? Well Jen does. Come see the advice she gives on how to set yourself up for success.

Click here to watch the video!
Click here to download the video!

Ground Zero SSIS: Class 3

Filmed: 4/15/2010

Length: 1:10:24 minutes

Here in another live presentation to NTSSUG we discuss where to hold your query inside your packages, as well as get into flatfile error handling.

Click here to watch the video!
Click here to download the video!

Manage DB Files in Powershell

Filmed: 4/4/2010

Length: 13:51 minutes

I'm excited to be able to film this one because it has the potential to save you so much time and effort. I'm showing you how to manage your DB files with powershell so you can change autogrowth, or whatever property you like. This is an extremely effective way to ensure that the DB files on all your servers are setup the way you want.

Click here to watch the video!
Click here to download the video!

Data Mining 101

Filmed: 3/18/2010

Length: 50:30 minutes

SPEAKER: Andrew Minkin. Drew stopped by to teach all of us mortals about data mining. There's so much more to cover in this topic so maybe we'll be able to talk him into coming back.

Click here to watch the video!
Click here to download the video!

Ground Zero SSIS: Class 2

Filmed: 3/18/2010

Length: 59:10 minutes

In this 2nd class we discuss how to decide whether you should architect your table loads in single or multiple packages. We also start on file loads.

Click here to watch the video!
Click here to download the video!

T-SQL: The Almost Question

Filmed: 3/2/2010

Length: 14:58 minutes

We need a query to get rows that fulfill *almost all* of a set of requirements. How do we work that out? Watch this video, and I'll tell you. Related blog: http://midnightdba.itbookworm.com/midnightdba/blog/ Download the code: http://midnightdba.itbookworm.com/midnightdba/PublicDownload/Almost.txt

Click here to watch the video!
Click here to download the video!

Start Agent Jobs in Powershell: Part 2

Filmed: 2/24/2010

Length: 22:34 minutes

Starting SQL Agent jobs in powershell part 2.

Click here to watch the video!
Click here to download the video!

Start Agent Jobs in Powershell

Filmed: 2/23/2010

Length: 11:25 minutes

Starting SQL Agent jobs in powershell is easy, but there's a trick to it that's not obvious. Here I'll show you how to start jobs both locally and remotely.

Click here to watch the video!
Click here to download the video!

Beginning Execution Plans

Filmed: 2/3/2010

Length: 11:03 minutes

Here's a level zero introduction to tuning queries. I go over execution plans, sp_help, and clustered primary key indexes.

Click here to watch the video!
Click here to download the video!

Clean Package Design

Filmed: 1/27/2010

Length: 16:37 minutes

This is a nice little primer on designing clean packages that are easier to support. Get rid of the sea of arrows that afflict your processes.

Click here to watch the video!
Click here to download the video!

Ground Zero SSIS: Class 1

Filmed: 1/21/2010

Length: 1:01:53 minutes

This is the 1st class I'm teaching at the NTSSUG for ground zero SSIS. I hope to have 6 in all, but it may get stretched out further.

Click here to watch the video!
Click here to download the video!

Check if File Exists

Filmed: 1/10/2010

Length: 3:04 minutes

Inspired by a user question I decided to make a quick vid on how to use a script task to check that a file exists. No frills in this one, just a quick walkthrough of the code.

Click here to watch the video!
Click here to download the video!

Designing Efficient Functions

Filmed: 12/28/2009

Length: 11:40 minutes

Here I get on my cert soapbox again. I was going through a practice cert exam and came across an answer to a question that could use a major improvement. I made the improvement and here I show you what and why.

Click here to watch the video!
Click here to download the video!

Optimize Query by Intervals

Filmed: 12/1/2009

Length: 14:35 minutes

In this vid, I walk you through tuning a query full of date manipulation (which is performance death). I show you how to free the functions, free the optimizer, and happify your code...plus we talk a little date math. See the accompanying blog at http://midnightdba.itbookworm.com/midnightdba/blog/post/Optimized-Query-by-Hour2c-Day2c-Week2c-or-Month.aspx

Click here to watch the video!
Click here to download the video!

OR is bad, IF is good

Filmed: 12/1/2009

Length: 3:34 minutes

Here I tell you about a common mistake - using OR instead of IF – and show you that IF is better.

Click here to watch the video!
Click here to download the video!

Clustering 101

Filmed: 11/19/2009

Length: 1:29:30 minutes

SPEAKER: Kaylan Yella. We were fortunate enough to get someone of his skill to teach us about clustering. The basics take a lot of time so we didn't get to any demos during the usergroup session, but Kal was nice enough to film the demos on his own afterwards and they're posted right after this one. So start here and learn the basics, then go to the other vids and learn how to put it all together. Thanks Kal.

Click here to watch the video!
Click here to download the video!

Clustering: Part 2

Filmed: 11/19/2009

Length: 1:27:19 minutes

Here we have the 2nd class by Kal. This time he was cool enough to take time out of his busy schedule and record this and the next one at home for us. This is demo-rich and he shows you how to actually install SQL on a cluster.

Click here to watch the video!
Click here to download the video!

Clustering: Part 3

Filmed: 11/19/2009

Length: 32:32 minutes

Here we have the 3rd class by Kal. This time he was cool enough to take time out of his busy schedule and record this at home. We really appreciate all of this extra work he's put into our group.

Click here to watch the video!
Click here to download the video!

Add User to Windows Group with Powershell

Filmed: 9/14/2009

Length: 13:11 minutes

I love this video because it's so unusual to find something like it in the DBA community. We're quite often called on to do things outside of our direct job descriptions and this is something I have to do a lot. So I'm showing you how to add a user to a Windows group so you don't have to TS into a lot of boxes to add someone.

Click here to watch the video!
Click here to download the video!

Change DB Compatibility Level in Powershell

Filmed: 9/13/2009

Length: 6:02 minutes

Here I show you 2 easy ways to change the DB compatibility level in powershell. Otherwise you have to use a T-SQL cursor and that's not only much harder, it's just no fun. Trust me... use powershell for stuff like this and you'll be much happier.

Click here to watch the video!
Click here to download the video!

Get a Specific Method in Powershell

Filmed: 9/13/2009

Length: 3:37 minutes

Sometimes the list of properties and methods is too big to sift through when you do a get-member on an object. Here I'll show you how to cut down the list to a more manageable size so you can find exactly what you're looking for.

Click here to watch the video!
Click here to download the video!

Build .Net Objects in Powershell

Filmed: 9/8/2009

Length: 14:04 minutes

I just love the topic of this video. Here we show you how to create .NET code with powershell. While we create an .aspx page, you can build any type of .Net object dynamically. Use this process freely because it's just cool and easy and useful and full of awesome!

Click here to watch the video!
Click here to download the video!

Who Should Manage Backups

Filmed: 9/7/2009

Length: 18:56 minutes

SORRY GUYS!!! This was about a 45min video and a glitch with the computer lost the first part of it so you only get the last 19mins. We thought about recording it again, but we honestly don't remember what we said so I made the command decision to just post it as-is.

Click here to watch the video!
Click here to download the video!

Troubleshooting Simple Flatfile Load Errors

Filmed: 9/7/2009

Length: 11:23 minutes

In this video I put together a small SSIS flatfile-to-SQL package, and troubleshoot a couple of errors.

Click here to watch the video!
Click here to download the video!

T-SQL: CASE Statement

Filmed: 9/7/2009

Length: 11:44 minutes

Here’s an intro to case statements, in the form of a conversation with Sean. I cover simple case and searched case, and the special use of null. BTW, I really like this video, and I don’t say that very often.

Click here to watch the video!
Click here to download the video!

TSQL: Grouping Basics

Filmed: 9/7/2009

Length: 10:01 minutes

Here’s a ground level intro to grouping, with a lovely 80s arcade theme. This is a reshoot of the original Grouping Basics video (now retired).

Click here to watch the video!
Click here to download the video!

Intro to ASP.NET

Filmed: 9/3/2009

Length: 31:21 minutes

Intro to ASP.NET This is a decent intro to ASP.NET.  Personally I don't think it's the best one you'll ever see, but it's ok.  You'll see a pretty good intro to getting started writing pages in .NET and you'll get up and running on a couple easy data-driven pages.

Click here to watch the video!
Click here to download the video!

Insert Non-duplicate Rows

Filmed: 9/3/2009

Length: 11:53 minutes

Here we work our way through a coding scenario—inserting rows—that should illustrate the process of solving a problem from the simplest case to your intended result.

Click here to watch the video!
Click here to download the video!

Index Scripting Problems

Filmed: 8/21/2009

Length: minutes

This is just a fun vid. Again, we're sitting here late at night feeling a little punchy so we're a little goofier than usual.

Click here to watch the video!
Click here to download the video!

Grouping Sets

Filmed: 8/20/2009

Length: 17:32 minutes

Here Jen and I go over grouping sets the only way we can. I hope you guys enjoy us late at night.

Click here to watch the video!
Click here to download the video!

Find DB Files with Powershell

Filmed: 8/19/2009

Length: 2:10 minutes

Here I need to find the .MDF for a database so I can attach it, but I have no idea where the .MDF is.

Click here to watch the video!
Click here to download the video!

T-SQL: Over() and Partition By

Filmed: 8/19/2009

Length: 4:16 minutes

Show how to use OVER and PARTITION BY to get groups of data with aggregation.

Click here to watch the video!
Click here to download the video!

Backup File Extensions

Filmed: 8/10/2009

Length: 6:28 minutes

Here I talk about how you can use the file extensions to your advantage, but also have a little fun with it too.

Click here to watch the video!
Click here to download the video!

Pass Params to Scripts (Powershell)

Filmed: 8/10/2009

Length: 7:46 minutes

Wanna know how to pass parameters to scripts, and when to use each method?

Click here to watch the video!
Click here to download the video!

Deploy Packages

Filmed: 8/9/2009

Length: 30:21 minutes

Jen and I sit up very late one night and discuss how do deploy SSIS packages. If you don’t know anything at all about deploying packages or just want to learn a couple tricks, then you’ll love this. Plus we’re tired enough to goof around a bit.

Click here to watch the video!
Click here to download the video!

T-SQL: Find Last Day of Month

Filmed: 8/9/2009

Length: 4:16 minutes

Follow Jen as she steps you through 3 progressively elegant solutions for finding the last day of the month.

Click here to watch the video!
Click here to download the video!

Replace Text in File

Filmed: 4/28/2009

Length: 4:56 minutes

Here I show you how to replace a given string in a file and either write it back to the same file, or write the entire thing to a new file keeping your original in tact.

Click here to watch the video!
Click here to download the video!

Beginning Powershell for DBAs 3

Filmed: 4/27/2009

Length: 44:06 minutes

Here’s class 3 of 6 for the NTSSUG user group.

Click here to watch the video!
Click here to download the video!

Beginning Powershell for DBAs 4

Filmed: 4/27/2009

Length: 58:56 minutes

Here I continue with the 4th class in the series at the NTSSUG user group.

Click here to watch the video!
Click here to download the video!

Simple Backup Procedures

Filmed: 4/23/2009

Length: 19:35 minutes

This is a tutorial not only on how to turn your backup commands into SPs, but also the basics of stored procedures are discussed.

Click here to watch the video!
Click here to download the video!

Powershell Profiles

Filmed: 4/23/2009

Length: 8:15 minutes

Here I finally address how to connect to SQL Server from powershell proper by adding the SQL Server snapins. Then I show you how to add them to your profile so you don’t have to add them manually every time you start PowerShell.

Click here to watch the video!
Click here to download the video!

SSIS Basics

Filmed: 4/23/2009

Length: 1:06:23 minutes

This is a live presentation I gave recently. Here is a bare bones bottom of the barrel discussion on SSIS. This is for complete beginners. I’m explaining in this session, the simplest of basics on SSIS and BIDS. I give a couple of basics demos. This is the beginning of what I’ll build on in the next couple sessions as we get deeper into the specifics of this cool product. So if you know absolutely nothing about SSIS then this is the place for you.

Click here to watch the video!
Click here to download the video!

Relational Multiplication

Filmed: 4/13/2009

Length: 10:51 minutes

Here’s a quick talk on relational multiplication that gets us to take a closer look at what our joins are really doing.

Click here to watch the video!
Click here to download the video!

Relational Division

Filmed: 4/9/2009

Length: 19:41 minutes

Here we discuss relational division, the counterpart to relational multiplication (cross joins). We’ll hit a couple of examples, using subqueries to implement two different kinds of set division.

Click here to watch the video!
Click here to download the video!

Beginning Powershell for DBAs 1

Filmed: 2/24/2009

Length: 1:27:38 minutes

This is an excellent video that explains what schemas are and why you should care.

Click here to watch the video!
Click here to download the video!

Beginning Powershell for DBAs 2

Filmed: 2/24/2009

Length: 38:33 minutes

This is the 2nd video in the schema series. Here I give you a really good reason to adopt schemas in your architectures. Hope you enjoy.

Click here to watch the video!
Click here to download the video!

SSIS Basics 2: Flatfile Olympics

Filmed: 11/4/2008

Length: 1:02:15 minutes

Here we’re just going to jump into SSIS and do some real practical work. We’re going to learn all about taking things to/from multiple files. This is not to be missed because there are some really good concepts here that will carry over to many other tasks in SSIS. I specifically chose these operations because they teach such a wide variety of skills. So come learn how to get a gold medal in flatfiles.

Click here to watch the video!
Click here to download the video!

Building a Powershell BCP Routine

Filmed: 9/30/2008

Length: 31:21 minutes

Here I show you how to build a script that will BCP an entire group of tables and even log the progress. You can make the routine as simple or as rich as you like. These are the building blocks of such a process and will take you far in your endeavors. There are several techniques taught in this video so pay attention to the details and you’ll come away with a richer knowledge of powershell.

Click here to watch the video!
Click here to download the video!

Commenting Tribal Knowledge in SSIS Packages

Filmed: 9/21/2008

Length: 29:24 minutes

There is a lot of tribal knowledge that accumulates in any organization. Here I discuss some ways to document your SSIS packages so you can start to record some of this information. Your packages will be more supportable, easier to update, and you’ll take hours or sometimes days off of a re-design or support effort. You won’t be sorry you started doing this.

Click here to watch the video!
Click here to download the video!

Grant Schema Permissions in Powershell

Filmed: 9/20/2008

Length: 17:51 minutes

This is another excellent video that shows the power of powershell in SQL administration. I love this video because it’s a very common scenario and keeps you from writing a SQL cursor. This is one of my favorites.

Click here to watch the video!
Click here to download the video!

Grant DB Permissions

Filmed: 9/17/2008

Length: 9:28 minutes

This is a beautiful bit of script that allows you to set DB permissions for a user. This is particularly useful for adding a user to multiple DBs on the server. And the code to accomplish this is much shorter than its T-SQL counterpart. If you need a bit more explanation about how this is put together then look at the video for granting schema permissions with Powershell.

Click here to watch the video!
Click here to download the video!

Change SQL Server Job Owner

Filmed: 9/11/2008

Length: 18:28 minutes

This is a really cool method for changing SQL job owners or any job property I would imagine. It really simplifies it from using SQL syntax. So learn this method and it’ll be your friend for years to come.

Click here to watch the video!
Click here to download the video!

Compression Basics

Filmed: 9/10/2008

Length: 12:23 minutes

I’m going over the basics of how to implement data compression here. I’m not really touching on where and why you’d want to do it, but how to once you’ve made the decision.

Click here to watch the video!
Click here to download the video!

Powershell with –NoExit

Filmed: 8/25/2008

Length: 5:13 minutes

Here I show you how to get around the problem of your Powershell window closing down when you call it from either the run window or from other programs. If you get errors or want to see something that was printed on the screen it’s quite often impossible unless you startup PowerShell or dos first. This way you can call your scripts in a way that allows you to see the results no matter what. It’s a very simple technique, but very useful.

Click here to watch the video!
Click here to download the video!

Scripting DB Objects in Powershell (Advanced Topics)

Filmed: 8/23/2008

Length: 19:55 minutes

Here I get past the basics of using the Script() method and take you to how to recreate some of the scripts you can create in SSMS by instantiating a scriptingOptions object and passing it to the method.

Click here to watch the video!
Click here to download the video!

Working with PSDrive in PowerShell

Filmed: 8/7/2008

Length: 13:33 minutes

Here I go over what PSDrive is and how to work with it. I also go into aliases again as well as how to find help in powershell. We’re still covering important basics here because I can write scripts for you all day long, but you’ll never go far unless you can understand them yourself.

Click here to watch the video!
Click here to download the video!

Scripting DB Objects in PowerShell

Filmed: 7/24/2008

Length: 21:44 minutes

Continuing with PowerShell I thought I’d show you something really cool you can do that would make a difference in your shop right away. So here I’m showing you how to script your tables, etc in PowerShell so you can automate them and add them to your processes. This is an excellent example of how cool and useful this scripting language is. I hope it makes you as excited about PowerShell as it does me.

Click here to watch the video!
Click here to download the video!

PowerShell Basics

Filmed: 7/23/2008

Length: 20:24 minutes

Here I start the first in an exciting new series of vids on PowerShell both in Windows and SQL. I go over how to download PowerShell and some of the command basics. It’s just an intro so I’ll get into specific tasks in later vids.

Click here to watch the video!
Click here to download the video!

Forcing Package Failures

Filmed: 6/27/2008

Length: 13:20 minutes

This one was by user request to show how you can compare the counts of 2 tables and use it to force the package to fail if the counts don’t match. This comes in handy when comparing the counts of the source and destination tables after your load. The scenario is specific but can be modified to anything you like.

Click here to watch the video!
Click here to download the video!

Creating Files with ForEach Loop

Filmed: 6/25/2008

Length: 8:29 minutes

Here I take the intro one step further and show you how to create multiple txt files based off of a looping variable in the ForEach loop container. This is a very common task and I see this question quite a bit in the forums, so I documented it.

Click here to watch the video!
Click here to download the video!

Introduction to Sequence Containers

Filmed: 6/25/2008

Length: 6:29 minutes

This video takes a look at the basics of using the Sequence Container in SSIS. There’s not a lot to it, but it’s a good intro if you don’t know what it is.

Click here to watch the video!
Click here to download the video!

Connection strings in Package Config Files

Filmed: 6/24/2008

Length: 7:45 minutes

Here I show you how to put your data source connection string inside a package configuration XML file. This was inspired by a forum post by a user so I decided to make a video for him.

Click here to watch the video!
Click here to download the video!

Introduction to ForEach Loops

Filmed: 6/23/2008

Length: 11:07 minutes

Here I show you the basics of how to work with ForEach loops. Good stuff.

Click here to watch the video!
Click here to download the video!

BCP With Hyperbac

Filmed: 6/18/2008

Length: 9:57 minutes

Here I show you how to automatically compress BCP operations with Hyperbac. It’s just a cool bonus you get when you do you database backups with this utility.

Click here to watch the video!
Click here to download the video!

Derived Columns

Filmed: 5/22/2008

Length: 13:06 minutes

Here I show you how to work with derived columns in SSIS and use it to not only marry columns, but also troubleshoot data flows. This is a nice intro to this versatile transform.

Click here to watch the video!
Click here to download the video!

Row Count Transform

Filmed: 5/22/2008

Length: 11:05 minutes

Here I discuss how to use the row count transform in SSIS and give you some good advice for naming and working with the variable associated with it.

Click here to watch the video!
Click here to download the video!

SSIS Using Variables in Script Tasks

Filmed: 5/21/2008

Length: 7:11 minutes

Here I talk about how to setup the script task to work with variables, and how to access them through code once the script can see them.

Click here to watch the video!
Click here to download the video!

SSIS Variable Scope

Filmed: 5/21/2008

Length: 4:36 minutes

Learn how SSIS scopes variables. It’s not a complicated topic, but worth covering.

Click here to watch the video!
Click here to download the video!

Backup with Init

Filmed: 5/20/2008

Length: 6:39 minutes

In this video I discuss the init option for the backup command. You'll come away knowing how to use it and what it's for.

Click here to watch the video!
Click here to download the video!

Execute SQL Task with Parameters

Filmed: 5/20/2008

Length: 7:56 minutes

This video discusses how to pass parameters to queries and SPs inside the Execute SQL Task. It doesn’t teach the basics of the task. It assumes you already know how to use it. This video is only concerned with passing parameters.

Click here to watch the video!
Click here to download the video!

MultiFlatFile Connection Manager

Filmed: 5/20/2008

Length: 6:12 minutes

Here I talk about how to use the multiflatfile connection manager in SSIS to loop through flatfiles.

Click here to watch the video!
Click here to download the video!

SSIS Data Cleansing

Filmed: 4/19/2008

Length: 33:58 minutes

Fix data errors with derived cols and conditional split transformations.

Click here to watch the video!
Click here to download the video!

SSIS text file errors

Filmed: 4/19/2008

Length: 7:47 minutes

Dealing with SSIS txt file errors. You will learn how to properly redirect outputs to maximize your scrubbing efforts.

Click here to watch the video!
Click here to download the video!

BCP in errors

Filmed: 4/16/2008

Length: 17:48 minutes

Learn how to diagnose BCP import problems.

Click here to watch the video!
Click here to download the video!

BCP in

Filmed: 4/14/2008

Length: 5:15 minutes

Learn how to BCP data into a table.

Click here to watch the video!
Click here to download the video!

BCP out

Filmed: 4/11/2008

Length: 17:04 minutes

Learn how to BCP data out of a table.

Click here to watch the video!
Click here to download the video!

Remote Backups

Filmed: 3/20/2008

Length: 3:36 minutes

Learn how to do a backup across the LAN.

Click here to watch the video!
Click here to download the video!

RESTORE with move

Filmed: 3/20/2008

Length: 12:07 minutes

This one covers restore with move syntax to restore DBs while moving the files to a different location.

Click here to watch the video!
Click here to download the video!

Basic Backup

Filmed: 3/19/2008

Length: 4:05 minutes

Learn how to perform basic backups with SQL syntax.

Click here to watch the video!
Click here to download the video!