Excellent Free Ebooks from RedGate

SQL Server eBook Selection
1. Protecting SQL Server Data by John Magnabosco
2. The Art of High Performance SQL by Grant Fritchey
3. The Art of XSD by Jacob Sebastian
4. Two Minute SQL Server Stumpers Vol. 5

DBA Ebooks Bundle
1. Mastering SQL Server Profiler
2. Brad’s Sure Guide to SQL Server 2008
3. Brad McGehee’s DBA Best Practices
4. How to Become an Exceptional DBA (2nd edition)
5. SQL Server Tacklebox

The Best of Simple Talk .NET
1. The Best of Simple Talk .NET – Jit N’ Run vol.2

You can download them from this link

Jacob Sebastian is a SQL Server XML Guru!

Check out his collection for XQUery Labs. This is the first 12 of his series, and right now he has 43 and counting:

XQuery Sample Scripts

* XQuery Lab 1 – Transforming rows to columns
* XQuery Lab 2 – An example using OUTER APPLY
* XQuery Lab 3 – Filtering specific nodes
* XQuery Lab 4 – Joining XML Nodes with a Relational Table
* XQuery Lab 5 – Working with Namespaces
* XQuery Lab 6 – Processing Header-Detail information
* XQuery Lab 7 – Extracting a comma separated list of values
* XQuery Lab 8 – How to update the attribute value of an XML variable?
* XQuery Lab 9 – How to delete an attribute from an XML variable?
* XQuery Lab 10 – How to insert an attribute to an XML variable
* XQuery Lab 11 – How to insert an element to an XML variable
* XQuery Lab 12 – Different ways of reading values from an XML variable

Jacob Sebastian has also released a free ebook via RedGate – The Art of XSD – SQL Server XML Schema Collections

Check it out, all 483 pages!

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 »

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 »

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 »

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 »

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 root\Microsoft\SqlServer\ComputerManagement `
-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 [text]
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'))