Category Archives: Powershell for SQL

Where are my system databases?

Hi Friends,

In last post, we saw that we can very easily use powershell to connect to SQL Server instance and see all the available databases (http://www.sarabpreet.com/?p=288), but by default powershell maintains security and hide all system level databases. Now the question arises how to list\view system databases in powershell.

Well to do this, all you have to do is use an extra switch called FORCE, this forces powershell to show all content.

let’s see how to use this.

If we use the  normal get-childitem cmdlet it will just display the user databases like the below screenshot.

default behaviour

Now let’s use the force switch, Like: get-childitem –force, this will change the default behavior and show all Databases including System Databases.

with force switch

Enjoy!

Happy Learning.

Thanks,
Sarabpreet Singh

How to Connect to SQL Server using Powershell

Hi Friends,

In this blog I’ll show you how to connect to SQL Server using Powershell Step-by-Step. There are basically two ways to connect to SQL Server,

A. Using sqlps utility from Powershell.exe

B. Using SSMS context menu option

 

Let’s review both:

1. Go to command prompt\cmd and type Powershell.exe & press enter.

clip_image001

This will open the Powershell console for you,

2. Now you can execute SQLPS to start SQLPS utility and load SQL specific snappins. You can also start SQLPS directly from your command prompt by writing sqlps.exe. To know more about the SQLPS utility and Snappins visit: http://www.sarabpreet.com/?p=203

clip_image002

3. Notice the change of drive letter from PS to PS SQLSERVER, so that means Powershell has a SQLServer Drive which is being used by SQLPS utility to connect to SQL Server. (More on this in future posts)

4. Now as you know we can browse all drive contents with a command dir which is an alias for Get-ChildItem So we can do the same in SQLSERVER drive also, since the behavior will remain same.

Note: I am using cd, dir which are alias to set-location & get-childitem respectively. This is just to make it easy to understand, going forward in the future posts we’ll be using Set-location cmdlet only.

5. Issue a dir command and you’ll get all objects you can access. For this post we’ll just concentrate on SQL which is there to connect to SQL Server Database Engine.

6. Now you can execute a cd command to go in SQL folder.

clip_image004

7. Again do a dir to get name of the Server. Once you get the Server name set-location to the server so that you can see all available SQL instances on the Server.

8. Finally we can see all available SQL instances on the server, On this machine I’ve got two SQL Server Instances installed one is DEFAULT and the other is a Named instance, named as PROD02.

clip_image006

9. Now let’s change the location by using either cd or set-location to connect to SQL Server Instance, here in this example I am connecting to PROD02 SQL Instance.

10. Once you are connected to SQL Instance you can see all contents (or I should say all objects under SQL instance) in the same hierarchy.

clip_image008

11. To connect to a database, all I need to do is just set-location to Databases folder and all available databases will be exposed as an object\folder. You can see all databases by the same old command dir. Smile

clip_image010

Wait a min, Where are my System Databases? (http://www.sarabpreet.com/?p=294)

Using SSMS

You can directly connect to this DB in Powershell if you access Powershell by the context menu of SSMS (SQL Server Management Studio).

1. Open SSMS and connect to the SQL Server Instance you wish to connect.

2. Now Right click on the DB you want to connect and choose Start Powershell.

clip_image011

Notice that the SQL Server automatically connected you to the DB you’ve chosen in SSMS.

clip_image012

Happy Learning!

Thanks,
Sarabpreet Singh

Quotes in Powershell

In General Quotes are considered to be there just to let interpreter know that the value enclosed within quotes should be treated as a single string. But in reality there are many other uses and types of quotes supported by Powershell. The two types of Quotes are Single Quotes and Double Quotes.

Let’s say we need to pass a value to a parameter which contains special characters, for this purpose you can use any of these it doesn’t make any difference, both will let you do the same thing.

clip_image002[5]

In the above example, I used the quotes to let interpreter treat both words as a single string, a common example with File Paths and both quotes worked.

Back Quote Character ( ` ) aka Back tick

This is usually the upper leftmost key below escape. This comes to rescue the situations where you want to quote a single character.

clip_image004[5]

Variable Expansion

Double Quote has a unique use\feature which is not there with single quote, if the string enclosed in double quotes contains a variable reference starting with a “$”, it will be replaced or expanded by the string representation of the value stored in the variable. It will be more clear by the following example:

clip_image006[5]

As you can see in the example above when we enclosed the variable $a in single quotes the Write-Host Command treated it just as a simple string but when we enclosed the variable $a in double quotes both commands or I should say cmdlets expanded the value which was there in the variable and used it while executing the cmdlet.

How to Suppress Expansion?

Now let’s say we want to suppress expansion at one place for a single variable but not for rest of the variables in the output to achieve this we need to again use the back quote or back tick. Let’s see how to do this.

clip_image008[5]

In the above example see at the first occurrence of $a and $b I specifically used the back tick so that the interpreter doesn’t expand & replace the variable values.

New Line Character ( `n )

In case you want to move a part of output to a new line you can use this special sequence. You can achieve this with a back tick and n. you can place this in either single or double quotes. Please refer the example below:

clip_image010[5]

Horizontal Tab ( `t )

If you want to get a horizontal tab in your output you can use the Horizontal Tab special Sequence. Please refer the example below:

clip_image012[5]

Thanks,
Sarabpreet Singh Anand

SQL Server Management Objects (SMO)

SMO is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server. SQL Server Replication Management Objects (RMO) is a collection of objects that encapsulates SQL Server replication management.

SQL Server Management Objects (SMO) are objects designed for programmatic management of Microsoft SQL Server. You can use SMO to build customized SQL Server management applications. Although SQL Server Management Studio is a powerful and extensive application for managing SQL Server, there might be times when you would be better served by an SMO application.

The SMO object model extends and supersedes the Distributed Management Objects (SQL-DMO) object model. Compared to SQL-DMO, SMO increases performance, control, and ease of use. Most SQL-DMO functionality is included in SMO, and there are various new classes that support new features in SQL Server.

Because SMO is compatible with SQL Server 2000, SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2, you can easily manage a multi-version environment. But SMO does not support compatibility level 60, 65 or 70. If you use SMO with a database set to compatibility level 60, 65 or 70, you will not be able to manage the database by using SMO.

The SMO namespace is Microsoft.SqlServer.Management.Smo. SMO is implemented as a Microsoft .NET Framework assembly. This means that the common language runtime from the Microsoft .NET Framework version 2.0 must be installed before using the SMO objects. The SMO assemblies are installed by default into the Global Assembly Cache (GAC) with the SQL Server SDK option. The assemblies are located in C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\.

Thanks,
Sarabpreet Singh Anand

SQL with its own Powershell?

SQL Server 2008 and powershell are strongly integrated. In fact, SQL Server 2008 comes with its own Powershell. Microsoft SQL Server Powershell also known as SQLPS is basically a custom shell built to be used just for SQL Server. SQLPS comes preconfigured with all SQL Server extensions.

Technically speaking SQLPS is a mini-custom-powershell which has multiple Snappins created for SQL Server with its own custom cmdlets (pronounced as command-lets). SQLPS is a great tool to Administer, Monitor or Automate your SQL tasks provided you don’t go beyond SQL, means you can’t add other snappins to SQLPS. This makes it a “CLOSED” shell.

SQLPS uses two Snappins SqlServerCmdletSnapin100 & SqlServerProviderSnapin100. We can use Get-PSSnapins cmdlet to see all loaded\configured snapppins.

If you’ve any exposure to Linux or command prompt, Learning Powershell is very easy. Microsoft did a fabulous job and tried hard to provide the same look and feel + your (few) favorite commands are also compatible in powershell.

Yes, that means if you are coming from Windows background and love to use command prompt you can adopt powershell and start using just like another command prompt with commands like dir, cd and the same is true for Linux experts since powershell supports ls, man etc. Microsoft did this using Aliases Dir, ls are Alias for Get-ChildItem.

Thanks,
Sarabpreet Singh Anand