Every SQL Server DBA is intimately familiar with SQL Server Management Studio (SSMS) and/or previous tools such as Query Analyzer. What some of us are less familiar with are the various options of executing T-SQL via command line or from script files. There are situations where using these methods are very necessary or even required. For example, if you need to bring your database instance online in single user mode you can only create one connection and SSMS generates multiple connections. Because of this feature of SSMS you must access instances in single user mode with a method, such as command line, that only generates a single connection.
In this blog series I will be covering the basic usages of the below listed command line execution methods.
- Part 1 – Introduction
- Part 2 – SQLCMD.exe using command prompt
- Part 3 – .NET objects using Powershell
- Part 4 – SQLPSX using Powershell (SQL Server 2000 and above)
- Part 5 – SQLPS using Powershell (SQL Server 2012 / 2014)
For those with little time to read
Chad Miller has a short blog post Getting Data out of SQL Server with Powershell which would take less than five minutes to read. His post was written in 2009 and, therefore, doesn’t include the SQLPS module information (Part 5 of my blog) but I always recommend getting multiple perspectives, especially when a leading expert is one of the perspectives. So, I encourage everyone to read my series and Chad’s quick dive.