Tag Archives | MSSQL

Quickly install the SQL powershell toolls on your local machine

I wanted to quickly install the 2012 powershell tools on to my machine. I could’t find a simple summary, so here goes:

Visit this site:

Download the following:

Microsoft® Windows PowerShell Extensions for Microsoft® SQL Server® 2012
Microsoft® SQL Server® 2012 Shared Management Objects
Microsoft® System CLR Types for Microsoft® SQL Server® 2012

Wherever you downloaded the above files to:

PS C:\Temp> .\SQLSysClrTypes.msi /qr /norestart
PS C:\Temp> .\SharedManagementObjects.msi /qr /norestart
PS C:\Temp> .\PowerShellTools.MSI /qr /norestart

Import-Module SQLPS -DisableNameChecking 

That should do it.

PowerShell code to query a table, and put the XML results into a SharePoint Document Library

If you look at this prior post, I showed how you can use an xml file in a document library as a source for an input box’s jQuery autocomplete. I wanted to automate the creation of these XML files and upload them to a SharePoint Document Library. For example, I wanted to query Microsoft CRM for all our contacts, and have them appear as an autocomplete for a search input box. I wrote the following PowerShell script to automate this process:

Function JBM-SQL-XMLQueryResultsToSharePointDocLibrary{
PARAM([parameter(Mandatory = $true)]$SQLServerName,[parameter(Mandatory = $true)]$DatabaseName,[parameter(Mandatory = $true)]$Query,[parameter(Mandatory = $true)]$DocLibraryURL,[parameter(Mandatory = $true)]$FullPathFileName)
$FileName=$FullPathFileName.split("\")[$FullPathFileName.split("\").Count -1]
$xml=Invoke-Sqlcmd -ServerInstance $SQLServerName -database $DatabaseName -query "$Query" | ConvertTo-Xml -NoTypeInformation -As String
[xml]$output=$xml -replace '<Property Name="([^"]+)">([^<]+)</Property>', '<$1>$2</$1>' -replace '<Property Name="([^"]+)"\s*/>','<$1/>'

$webclient = New-Object System.Net.WebClient;
$webclient.UseDefaultCredentials = $true

I had to use this thread’s method to change the XML produced by ConvertTo-Xml. The default output puts the field name in to a Property Name tag.

Example: Default = <Property Name=”Version”>0</Property> . I wanted it to be = <Version>0</Version>

In theory, this can be used for any SQL query.

My PowerShell command to backup SQL server

Below is my PowerShell code to backup SQL servers. This will create a folder in the destination with the ServerName, then a subfolder with the date, and then a subfolder with the hour. You can backup a single database or all of them. You must have the PowerShell SQL snap ins installed:

Add-PSSnapin -name SqlServerProviderSnapin110 -ErrorAction SilentlyContinue
Add-PSSnapin -name SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue

Maybe this code will help someone:

Function JBMURPHY-SQL-BackupDB() {

if ($All){
$DatabaseName=$(Invoke-Sqlcmd -server $ServerName "SELECT name FROM sys.databases")
elseif($DatabaseName -eq $NULL){
write-host "You must use the -DatabaseName parameter"
$DatabaseName=$(Invoke-Sqlcmd -server $ServerName "SELECT name FROM sys.databases WHERE name = '$DatabaseName'")

foreach ($db in $DatabaseName){
    $folderDate=$(get-date -uformat "%Y-%m-%d")
    $folderHour=$(get-date -uformat "%H")
    new-item "$Destination\$ServerName\$folderDate\$folderHour" -type directory -force
    $sqlcmd="BACKUP DATABASE [$dbName] TO DISK = N'$Destination\$ServerName\$folderDate\$folderHour\$dbName.bak' WITH NOFORMAT, NOINIT,  NAME = N'$($dbName) FullBackup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10"
    Write-Host $sqlcmd
    invoke-sqlcmd -query "$sqlcmd" -Server $ServerName -QueryTimeout 1200

How we recovered from a Microsoft SQL 2005 suspect msdb database

I am not a MS SQL Server guru. Honestly, it is a “set it and forget it” technology for me. After our last patching episode, we ended up with a SQL Server Agent offline, and our msdb file was listed as suspect. Seems like there are 2 ways to recover from this. 1 restore a backup, or two recreate with this method. Since we had a backup from the night before, we started with this method.

  1. First, we restored the msdb file from Symantec Backup Exec to another SQL server as a user database.  Since it was the middle of the day, and we could not take down the SQL server, we wanted to get a copy of the the MSDBData.mdf and MSDBlog.ldf files. This was the quickest way to do it.
  2. We stopped the SQL server, moved the suspect msdb files aside, and placed the restored versions in the right place.
  3. Started the SQL Server, and SQL Server Agent came back online. All seems happy