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…
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…
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.…
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…
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.…
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:
# SQL Server is required to read the file contents
$servername = "sqlserver\instance"
$mdf = "S:\DATA\mydb.mdf"
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $servername
# Use the undocumented DBCC checkpriamryfile function to automatically determine
# where the files are located. If you moved the files, please see below.
$sql = "DBCC checkprimaryfile(N'$mdf',3)"
$dataset = $server.ConnectionContext.ExecuteWithResults($sql)
Microsoft ConnectM isn’t letting me sign in to post this workaround, but I just ran into a bug with the OleDbProviderSettings within SMO. To get around it, I used $server.Settings.OleDbProviderSettings instead of $server.OleDbProviderSettings
Tonight, I published Invoke-Locate.ps1 to ScriptCenter because I was tired of mounting my Windows drive to my Linux servers to quickly and reliably find files. Originally, it was going to be a small script, but then I decided to trick…