Category: SQL Server

Presentation Materials from my sessions at SQL Saturday 423 in Baton Rouge

This past Saturday, I presented two sessions at SQL Saturday #423 in Baton Rouge. The code and slides that I used during my "PS C:\> Get-Started -With 'PowerShell for SQL Server'" presentation can be downloaded from here and the code and slides from my "PS C:\> Get-Started -With 'PowerShell Desired State Configuration'" presentation can be downloaded from here. The code from both sessions reference PowerShell functions that are part of script modules that can be downloaded from my Read more [...]

Determine who deleted SQL Server database records by querying the transaction log with PowerShell

Have you ever had a scenario where records from one or more SQL server database tables mysteriously came up missing and no one owned up to deleting them? Maybe it was an honest mistake or maybe a scheduled job deleted them. How do you figure out what happened without spending thousands of dollars on a third party product? You need to determine what happened so this doesn't occur again, but the immediate crisis is to get those records back from a restored copy of the database. How do you determine Read more [...]

Query SQL Server from PowerShell without the SQL module or snapin

There are several different ways to query a SQL Server from PowerShell but most often you'll find that they're dependent on the SQL PowerShell module or snapin. To eliminate that dependency, you can query a SQL Server from PowerShell using the .NET framework. There are several different options to accomplish this including using a DataReader or a DataSet and there are plenty of tutorials on those topics already on the Internet so there's no reason to duplicate that information here. Most of the Read more [...]

Function to Import the SQLPS PowerShell Module or Snap-in

The SQL Server 2014 basic management tools have been installed on the Windows 8.1 workstation that's being used in this blog article. When attempting to import the SQLPS (SQL Server PowerShell) module on your workstation, you'll be unable to import it and you'll receive the following error message if the PowerShell script execution policy is set to the default of restricted: Import-Module : File C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS\Sqlps.ps1 Read more [...]

Video: PS C:\> Get-Started -With ‘PowerShell for SQL Server’

I presented a beginner level session for the PowerShell Virtual Chapter of PASS yesterday titled "PS C:\> Get-Started -With 'PowerShell for SQL Server'". The session is entry level PowerShell and designed for those people who haven't yet embraced PowerShell because I often hear "How do I get started with PowerShell"? My new answer: Watch this video. The first two thirds or so of the presentation is more or less generic because the basics for someone Read more [...]

Get-Started with PowerShell for SQL Server Webinar on Wednesday June 17th

Know of anyone who is interested in PowerShell but not sure how to Get-Started? Next Wednesday, June 17th (One week from today), I'll be presenting a session on how to "Get-Started with PowerShell for SQL Server" for the PowerShell Virtual Chapter of PASS: The presentation begins at 11am CDT / noon EDT and I hope you'll join me. This is a beginner (intro) level session which will be presented via a Webinar. Sign up here. Update 6/18/2015 The video and presentation materials from this Read more [...]

Change the Recovery Model of a SQL Server database with the PowerShell SQL PSProvider

I recently set out to change the recovery model of a SQL Server database with PowerShell. There seems to be lots of information available on how to accomplish this task with PowerShell through SMO (SQL Server Management Objects) and using T-SQL wrapped inside the Invoke-Sqlcmd cmdlet. I even found lots of information about how to view the recovery model with the PowerShell SQL Server PSProvider, but when it came to actually changing the recovery model via the PSProvider, there was little if any information Read more [...]

PowerShell: When Best Practices and Accurate Results Collide

I'm a big believer in trying to write my PowerShell code to what the industry considers to be the best practices as most are common sense anyway, although as one person once told me: "Common sense isn't all that common anymore". I would hope that even the most diehard best practices person would realize that if you run into a scenario where following best practices causes the results to be skewed, that at least in that scenario it's worth taking a step back so you can see the bigger picture. I Read more [...]

PowerShell Track at SQL Saturday #328 in Birmingham Alabama this Weekend!

Do you work with technology and live within driving distance of Birmingham Alabama? Then you should consider attending SQL Saturday #328 this weekend. There’s an entire track dedicated to PowerShell and while a few of the sessions in that particular track will focus on SQL Server, there's also a session on PowerShell Fundamentals for SharePoint, a session on Stupid PowerShell Tricks by PowerShell MVP Jim Christopher, and a session on the new features in PowerShell version 5 that I’ll be presenting. I'll Read more [...]

Using PowerShell to discover information about your Microsoft SQL Servers

Earlier this week, I had an article published by PowerShell Magazine on the subject of "Using PowerShell to discover information about your Microsoft SQL Servers". Here's a brief description about that article: "In this article, I’ll be discussing how PowerShell can be used to retrieve just about any information that you would want to know about modern versions of SQL Server that you currently have running in your environment. This article isn't meant to be a deep dive, it’s meant to get Read more [...]

How to Create Reusable PowerShell Functions for Microsoft SQL Server Database Administration

In my last blog article on "Getting Started with Administering and Managing Microsoft SQL Server with PowerShell", I left off by introducing you to how to query information about your SQL Server using PowerShell with SQL Server Management Objects (SMO). In this blog article, I'll pick up where I left off and write a reusable PowerShell function to return information about SQL Server files and file groups while making the use of SMO transparent to the user of the function. I'm going to start Read more [...]

Use PowerShell to Create Active Directory User Accounts from Data Contained in the Adventure Works 2012 Database

This past Saturday, I presented a session at PowerShell Saturday 003 in Atlanta. Towards the end of the presentation, I created 290 Active Directory user accounts by using the information for employees contained in the Adventure Works 2012 database. This is actually a PowerShell script that I whipped up Friday night at the hotel after the speaker dinner. I populated some demographic information by joining multiple tables together from that particular database. There is more demographic information Read more [...]

Use Data Stored in a SQL Server Database to Create Active Directory User Accounts with PowerShell

I need a few Active Directory users created in my test environment so I thought why come up with fake information when I could use information that I already have in a SQL Server database? The Employees table in the Northwind database looks like an easy enough candidate since all the data I need is in one table. This is about the concept and not about seeing how complicated I can make this process. Here's the type of information I'll pull out of this database to use for the Active Read more [...]

Querying a SQL Server Database from PowerShell

A few days ago, I wrote a PowerShell script on my computer that would ultimately be used on a different computer to automate a specific task. One of the things this script did was to query a SQL Server database which worked fine on my computer. After moving the script, it didn't take long to figure out that the other computer didn't have the necessary SQL PowerShell snap-in or module. My goal was to install the minimum features of SQL Server 2008 R2 to be able to run Transact SQL from PowerShell Read more [...]

Installation of SQL Server Features from the Command Line

I'm installing a line of business application that requires the SQL Server Management Studio and Client Tools Connectivity be installed as a prerequisite. I want to script this instead of having to manually click through the GUI for the installation since this will be installed on several servers. I've mounted the SQL Server 2008 R2 ISO on "Z" drive. The following command will produce the desired results: At first, I thought that the CONN parameter was incorrect Read more [...]

Installing SQL Server 2012 RC0 on Server Core

Last week, I published a blog on Installing SQL Server Denali CTP3 on Server Core and then SQL Server 2012 RC0 was made available for download so I thought I’d write an updated blog since the issues I ran into with the installation seem to be resolved. One of the new features in SQL Server 2012 is official support from Microsoft for installation on Server Core. There’s an MSDN article on “Install SQL Server 2012 on Server Core” and another MSDN article on “Install SQL Server 2012 from Read more [...]