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…
I’m currently working on a PowerShell/SQL set of scripts to ease SQL Server Migrations. A few of the scripts have been posted to Microsoft’s ScriptCenter. Recently, one of the ScriptCenter visitors asked: Can this script be used to backup all…
A buddy of mine was running into issues importing a 1 GB CSV of Longitudes and Latitudes available from geonames.org. The CSV contains over 9 million rows, and no matter what he did, DTS/SSIS would not import the data because…