SQL Server PowerShell : How to Backup SQL Server Databases Using SMO and PowerShell

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:

[powershell collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

[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.
Continue reading

Practical PowerShell Video Series

If you are curious about PowerShell, or just starting to learn the new scripting language from Microsoft, you will want to check out the Practical PowerShell Video Series from Idera.

Here’s an excerpt about this video series.

This series, presented by PowerShell author and expert Don Jones, provides IT professionals with the knowledge and skills required to automate key administrative tasks on a variety of Windows platforms using PowerShell.

The series includes:

Part 1: Getting to Know PowerShell
Part 2: PowerShell for Active Directory
Part 3: PowerShell for Exchange Server 2007
Part 4: PowerShell for SQL Server 2008
Part 5: PowerShell for Windows Servers and Clients

All examples in this video series are created and edited using Idera’s PowerShell Plus Professional Edition

I use PowerShell Plus a lot when scripting PS, and I have to say it makes my life a lot easier. I will also be using PSP in my presentation at DevTeach in Vancouver on June 9, 2009 called “PowerShell’d: How to PowerShell Your Way with SQL Server” .

SQL Server PowerShell : How to List SQL Server Services using PowerShell

Catch: this lists any service that has “SQL” in it, so can potentially list MySQL services too 🙂

ListAll SQL services

[powershell collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

#list stopped SQL Server services
$computername = “APHRODITE”
Get-WmiObject win32_service -computername $computername |
select name, state |
where {
($_.name -like “SQLAGENT*” -or $_.name -like “*SQL*”) `

Continue reading

How to Get Definition for Stored Procedures, UDFs and Triggers using T-SQL

If you want to list the definition for a stored procedure, User Defined Function, or Trigger, you can use one of the following ways:

Alternative 1: sp_helptext (T-SQL)

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]
— using sp_helptext

sp_helptext ‘dbo.your_object_name’


Alternative 2: syscomments (T-SQL)

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]
— using syscomments

SELECT [text]
FROM sys.syscomments
WHERE OBJECT_NAME(id) = ‘your_object_name’



[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]
— using built in function OBJECT_DEFINITION



How to Search for Columns in SQL Server

There are several ways to look for a column in your SQL Server database.


[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

SET @colName = ‘name’

COLUMN_NAME LIKE ‘%’ + @colName + ‘%’


Alternative 2: sys.columns (T-SQL)

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

sys.tables.name AS ‘Table’,
sys.columns.name AS ‘Column’
INNER JOIN sys.tables
ON sys.tables.object_id = sys.columns.object_id
sys.columns.name LIKE ‘%’ + @colName + ‘%’


Alternative 3: Object Search (SSMS)

In SQL Server 2000, there was an “Object Search” tool in Query Analyzer to search for objects.

To get to this tool in SQL Server 2000, either
– Go to Tools > Object Search, or
– Press F4

Unfortunately this was removed in SQL Server 2005.

But back again in SQL Server 2008:
– Go to View > Object Explorer Details
– Type object name in the Search bar
– Press Enter to Search


DevTeach 2009 in Vancouver

Have you registered to DevTeach yet?

If not, why not?
You can save if you register in groups! Register 2 attendees and get one free!

(Following is an excerpt from DevTeach.com site)
Need to convince your boss? Here are 10 ways to convince your boss :

  • The speakers, mostly independent consultant, provide training on issue and know how from real projects in the fields. This Conference is providing training that I cannot get any other way.
  • The Educations Stimulus package. Register 2 attendees and get a free registration! Contact Jean-René Roy for more details
  • The industry is evolving so fast that it’s hard to follow and the only ways you can stay up-to-date is to attend a conference every year.
  • You will find in your conference bag a version of Visual Studio 2008 Professional, ExpressionTM Web 2 and the Tech-Ed Conference DVD Set. Over 1000$ of software!
  • Attending a conference put me in contact with the best mind in the industry. It is the best way to network with writers, trainers, Regional Director and MVP.
  • By the time I find the time to read my books, it is obsolute. Conference saves time by catching up on important area of interest in the industry.
  • Conference typically causes you to think outside the box and see techniques, tricks, and technologies that normally won’t be covered in a training class.
  • Frequently the most interesting conversations happen late night at the conference hotel bar. 🙂
  • This conference is offering over 119 sessions in three days. It’s providing the most content at a very low cost.
  • DevTeach has the most MVP, RD and the famous writers in all the independent conferences. This conference is having over 50 speakers
  • DevTeach is providing free wireless internet access on site which helps me stay in touch with the office.
  • For the local this conference is a great deal. No Hotel, No Passport, No being away from family, No flights, No Dinner Expenses, and No Foreign Currency Exchange needed

Download PowerPoint Slide

Handling Division By Zero Scenarios in T-SQL

Sometimes it is inevitable to encounter scenarios that will give division by zero errors

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

DECLARE @dividend INT
DECLARE @divisor INT

SET @dividend = 1
SET @divisor = 0

SELECT @dividend/@divisor

Msg 8134, Level 16, State 1, Line 7
Divide by zero error encountered.


What you can do is you can code around it, so your users and your app do not get this error.
Continue reading