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 »

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” .

One word. WOW. *Jaw drop*
Watch this Google Developer Preview at Google I/O. See for yourself.

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

ListAll SQL services

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

Another way to do this:

#note ComputerManagement works on SQL Server 2005
#on SQL Server 2008 this has to be ComputerManagement10
Get-WmiObject `
-namespace rootMicrosoftSqlServerComputerManagement `
-class SqlService | Select-Object ServiceName, DisplayName, SQLServiceType, State

List Stopped SQL services

#list stopped SQL Server services
$computername = "APHRODITE"
Get-WmiObject win32_service -computername $computername |
select name, state |
where {
   ($_.name -like "SQLAGENT*" -or $_.Name -like "*SQL*") `
    -and $_.State -match "Stopped"
    }

List Running SQL services

#list stopped SQL Server services
$computername = "APHRODITE"
Get-WmiObject win32_service -computername $computername |
select name, state |
where {
   ($_.name -like "SQLAGENT*" -or $_.Name -like "*SQL*") `
    -and $_.State -match "Stopped"
    }

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)

-- using sp_helptext
sp_helptext 'dbo.your_object_name'

Alternative 2: syscomments (T-SQL)

-- using syscomments
SELECT  
FROM sys.syscomments
WHERE OBJECT_NAME(id) = 'your_object_name'

Alternative 3: OBJECT_DEFINITION (T-SQL)

-- using built in function OBJECT_DEFINITION
SELECT OBJECT_DEFINITION(OBJECT_ID('your_object_name'))

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

Alternative 1: INFORMATION_SCHEMA.COLUMNS (T-SQL)

DECLARE @colName NVARCHAR(30)
SET @colName = 'name'
SELECT 
	TABLE_CATALOG	AS 'Database',
	TABLE_SCHEMA	AS 'Schema',
	TABLE_NAME		AS 'Table',
	COLUMN_NAME		AS 'Column'
FROM 
	INFORMATION_SCHEMA.COLUMNS
WHERE 
	COLUMN_NAME LIKE '%' + @colName + '%'

Alternative 2: sys.columns (T-SQL)

SELECT 
	sys.tables.name		AS 'Table',
	sys.columns.name	AS 'Column'
FROM 
	sys.columns 
	INNER JOIN sys.tables 
	ON sys.tables.object_id = sys.columns.object_id
WHERE 
	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

Yay!

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

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

DECLARE @dividend INT
DECLARE @divisor INT
SET @dividend = 1
SET @divisor = 0
SELECT @dividend/@divisor
/* 
Error:
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.
Read the rest of this entry »