Blog Archives

Getting Total Number of Rows Copied in SqlBulkCopy Using PowerShell

Getting the total number of inserted rows for SqlBulkCopy should be easier, but I believe the easiest (and fastest) is by using reflection and some C# code within your PowerShell script. Thanks to user601543 at stackoverflow, I was able translate

Posted in PowerShell, SQL Server

Simplifying SQL Server Migrations using PowerShell

Recently, I did a webcast for the PowerShell Virtual Chapter of PASS on Simplifying SQL Server Migrations using PowerShell. If you’re planning for a SQL Server migration, I wrote a bunch of scripts that can help, and put them all

Posted in PowerShell, SQL Server

Sudden High CPU Utilization and Query Performance Decrease | When Query Plans Go Bad

Each morning when I get into the office, I check performance graphs in SolarWinds Database Performance Analyzer (DPA – formerly Confio Ignite).  Monday mornings are usually the most important, because I run full system maintenance on a specific monitored database

Posted in SQL Server

Get SQL Server Product Keys for Local and Remote Servers using PowerShell

I just released a new PowerShell script, Get-SQLServerKeys.ps1. The script works works with several versions of SQL Server (2005-2014), and can use Central Management Server, a text file, or just a string of servers as input. Like many of the

Posted in PowerShell, SQL Server

Using .NET DataTable Computes and Column Expressions in PowerShell

As a SQL Server DBA, I’m used to SQL being able to perform aliasing, super fast subselects, and easy aggregation. My most recent project, Invoke-Locate.ps1, however, uses SQLite because of its portability and speed when performing simple selects and inserts.

Posted in PowerShell, SQL Server

High-Performance Techniques for Importing CSV to SQL Server

If you’ve ever tried to use PowerShell’s Import-CSV with large files, you know that it can exhaust all of your RAM. Previously, I created a script on ScriptCenter that used an alternative tecehnique to import large CSV files, and even

Posted in PowerShell, SQL Server

Professional PowerShell-based SQL Server SMO Recipes

Last year, I finally started working with SMO (SQL Management Objects) within PowerShell and I absolutely love it. Below are a few recipes that I created and use extensively throughout my SQL Management scripts, which you can find on ScriptCenter.

Posted in PowerShell, SQL Server

Enumerate file structure within detached MDF database file using SMO

While researching how connect to a detached MDF and read the internal file structure of the internal files, including LDF files, I kept seeing suggestions for using the undocumented DBCC command DBCC checkprimaryfile. Initially, my code looked like this:

Posted in PowerShell, SQL Server