Archive for the ‘ SMO ’ Category

Re-Post: SQL Server and PowerShell WebCasts

I created a 2 part webcast on SQL Server and PowerShell last November/December, and I just wanted to re-post it in my site.

SQL Server PowerShell: Part I

Posted on November 25, 2009

Overview: This video is part 1 of a 2 part video series that goes through PowerShell basics with demonstrations on how to use PowerShell with SQL Server.

SQL Powershell Part One authored by Donabel Santos (@sqlbelle) on Vimeo.

Author: @sqlbelle

Recorded On: 17″ MacBook Pro

Software: Telestream’s ScreenFlow

SQL Server PowerShell: Part II

Posted on December 3, 2009

Overview: This video is part 2 of a 2 part video series that demonstrates how to use PowerShell with SQL Server, using the SSMS built-in mini shell, and using .NET and SMO.

SQL Powershell Part Two Donabel Santos (@sqlbelle) on Vimeo.

Created for the Edmonton PASS user group by Donabel Santos at Black Ninja Software.

Author: @sqlbelle
Recorded On: 17″ MacBook Pro
Software: Telestream’s ScreenFlow

VN:F [1.9.22_1171]
Rating: 9.9/10 (7 votes cast)
VN:F [1.9.22_1171]
Rating: -1 (from 1 vote)

VANPASS Presentation and Demo Files on SQL Server and PowerShell

I did a presentation today for VANPASS on PowerShell and SQL Server – thank you to those who attended! It was a great lively crowd :) Thanks to Idera too – for the freebies – and to Black Ninja Software for the pizza and pop!

Here are the files as promised: VANPASS – SQL Server and PowerShell – Donabel Santos

The zipped file contains the presentation and all of the samples (ones that I demo’d, and ones I didn’t have time to demo). The demos are:

  • Demo 01 – Common PowerShell Tasks – Complete.PS1
  • Demo 02 – PowerShell Basics – Complete.PS1
  • Demo 03a – SQL Server 2008 Mini Shell – Complete.PS1
  • Demo 03b – Server and Database Properties.PS1
  • Demo 03c – SQL Server Services and Jobs.PS1
  • Demo 03d – Scripting SQL Server Objects.PS1
  • Demo 03e – Searching for Objects.PS1
  • Demo 03f – Basic DDL and DML Import.PS1
  • Demo 03f – Basic DML.PS1
  • Demo 03g – Backup and Restore.PS1
  • Demo 04 – SQL Server PSX – Complete.PS1
  • Demo 05 – SQL Server PSSnapin – Complete.PS1
  • Demo – SSIS.PS1
  • Demo – SSRS.PS1
  • Out-Report from Chad Miller

I have used a lot of resources when I was creating all these samples, and I have tried to enumerate them in my presentation. And I also just realized I had a lot of samples and was only able to get through 2/3 of them.

I enjoy presenting at VANPASS, it is always a great crowd. Please feel free to drop me a line, or comment on my blog, or message me on twitter(sqlbelle) – if you have other SQL Server PowerShell questions, or if you had questions at the presentation that I wasn’t able to address.

Thanks to Richard Baumet and Scott Stauffer for inviting me to speak.
And thanks to Idera too – I love using your PowerShell Plus!

VN:F [1.9.22_1171]
Rating: 9.9/10 (8 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

SQL Server PowerShell : Search for SQL Server Objects Using PowerShell

I have posted previously 3 different ways of searching for SQL Server Objects (How to Search for Columns in SQL Server ).

Here’s a fourth one!

Check out how powerful and flexible PowerShell is when you need to look for a database object. In the script below, I only search databases, tables, columns, and indexes. But in reality, really, sky is the limit!
Read the rest of this entry »

VN:F [1.9.22_1171]
Rating: 8.0/10 (6 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Here is a simple script to audit your SQL Server Instance Properties.

Note that the property names are not hardcoded. We query each of these properties, and use those to display the property values.

If you prefer, you can also query directly each of the properties of an instance. If this is the case, just specify your server object and then the property name. For example:

$serverObject.BackupDirectory

The list of properties can be found at the end of the post.
Read the rest of this entry »

VN:F [1.9.22_1171]
Rating: 10.0/10 (8 votes cast)
VN:F [1.9.22_1171]
Rating: +2 (from 2 votes)

A follow-up to my previous post: How to Restore SQL Server Databases Using SMO and PowerShell

In this post I will show you how you can restore your database to :

1. the existing database (same database name)
2. a different database (different database name, different mdf and ldf)

As with the previous post, to do a SQL Server restore in SQL Server, you will need to use the SMO SqlBackup method. In SQL Server 2008, you will need to load Microsoft.SqlServer.SmoExtended assembly otherwise, you will get the following error:

Cannot find type [Microsoft.SqlServer.Management.Smo.Backup]: make sure the assembly containing this type is loaded.

Other assemblies you may want to load are:


[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
#Need SmoExtended for smo.backup
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

Read the rest of this entry »

VN:F [1.9.22_1171]
Rating: 9.0/10 (26 votes cast)
VN:F [1.9.22_1171]
Rating: +2 (from 2 votes)

PowerShell makes it easier to manage even your database backups and restore.

To do a SQL Server backup in SQL Server, you will need to use the SMO SqlBackup method. In SQL Server 2008, you will need to load Microsoft.SqlServer.SmoExtended assembly otherwise, you will get the following error:

Cannot find type [Microsoft.SqlServer.Management.Smo.Backup]: make sure the assembly containing this type is loaded.

Other assemblies you may want to load are:


[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
#Need SmoExtended for smo.backup
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

Also another point to note is the types of backup you can do. BackupActionType specifies the type of backup. Valid values for this option are Database, Files, Log

Here’s the script. This script is for one specific database. If you want to use this for several database, you will just need to use this code inside a loop.
Better yet, put this in a function, and call this in a loop. I will try to do that sometime soon.
Read the rest of this entry »

VN:F [1.9.22_1171]
Rating: 9.5/10 (34 votes cast)
VN:F [1.9.22_1171]
Rating: +8 (from 8 votes)

SQL Server PowerShell : Basics – Connecting to SQL Server

PowerShell is a great tool that allows DBAs and Developers alike to script, manage and automate SQL Server tasks. Most of the objects and tasks will require SMO (SQL Server Management Objects).

Basic Steps

1. Set Execution Policy

Depending on what you need to do, you may need to set the Execution Policy in your Powershell console. In my case, I will be using it to access SQL Server for administrative purposes, so I will be setting mine to have Unrestricted access to my server. Make sure you review the security guidelines in your organization before you set your environment to Unrestricted

PS> Set-ExecutionPolicy Unrestricted

2. Load assemblies

First, you will need to load SQL Server related assemblies. For SMO, you will usually want to load the following assemblies:

  • Microsoft.SqlServer.Smo
  • Microsoft.SqlServer.SqlEnum
  • Microsoft.SqlServer.SmoEnum
  • Microsoft.SqlServer.ConnectionInfo

When you load the assemblies, you have to use the following format:

[Reflection.Assembly]::Load("Microsoft.SqlServer.Smo, `
      Version=9.0.242.0, Culture=neutral, ` 
      PublicKeyToken=89845dcd8080cc91")

A couple of things to note here:

  • the backtick is the line continuation character, ie if your line of code spans multiple lines, you have to use a backtick
  • you can get the version of the assembly either by :
    • going to the GAC (C:\Windows\assembly), right click on assembly and click on properties
    • use reflector

SQL Server SMO assembly

Read the rest of this entry »

VN:F [1.9.22_1171]
Rating: 8.5/10 (26 votes cast)
VN:F [1.9.22_1171]
Rating: +2 (from 2 votes)

How to Connect to an Instance Name Using SMO

1. Create your Visual Studio Project

2. Add the following references in your project

  • Microsoft.SqlServer.ConnectionInfo
  • Microsoft.SqlServer.Smo
  • Microsoft.SqlServer.SmoEnum
  • Microsoft.SqlServer.SqlEnum

3. Include the following using statement:

using Microsoft.SqlServer.Management.Smo;

4. Add your server object. Normally, you would use the following format in your Server object:

String serverName  = "JUBILEE";
Server srv         = new Server(serverName);
Database db        = srv.Databases["AdventureWorks"];
txtServerInfo.Text = srv.Information.VersionString +
                     System.Environment.NewLine;

However, if you have an instance name, you have to specify the SERVER\INSTANCE name in your Server object. In C# code:

String serverName = “JUBILEE” + @”\” + “SQL01“;

Server srv         = new Server(serverName);
Database db        = srv.Databases["AdventureWorks"];
txtServerInfo.Text = srv.Information.VersionString +
                     System.Environment.NewLine;

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)
`