MS SQL Server

Nice Articles

http://blogs.technet.com/b/heyscriptingguy/archive/2013/05/06/10-tips-for-the-sql-server-powershell-scripter.aspx

Restore DB

RESTORE DATABASE [DBNAME] FROM  DISK = N'Drive:\Path to file and filename.extension' WITH  FILE = 1,  MOVE N'logicaldatafilename' TO N'Drive:\new data filepath and filename.mdf',  MOVE N'logical logfilename' TO N'Drive:\new log filepath and filename.LDF', NOUNLOAD,  STATS = 10

MS SQL Server

Add-PSSnapin SqlServerProviderSnapin100 Add-PSSnapin SqlServerCmdletSnapin100 Get-Command -Module SqlServerCmdletSnapin100 Get-Command -Module SqlServerProviderSnapin100

get-psdrive SQLServer

If the instance is default just use Invoke-SQLCMD -SeverInstance Servername otherwise it will fail

If ($InstanceName = "Default"){     Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance $ServerName } Else {     Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance $ServerName\$InstanceName }

$ServerName="SQL2008" $DBName="BlahDB" $BackupPath="f:\backup\" + $DBName + ".bak" $query= "backup database " + $DBName + " to disk = `'" + $BackupPath + "`' with init"  Invoke-Sqlcmd -ServerInstance $ServerName -Query $query

#Using SQL Provider #http://msdn.microsoft.com/en-us/library/cc281947.aspx CD SQLServer: CD SQL  CD $ServerName cd default\Databases  CD SQLServer:\SQL\$ServerName\default\Databases Dir  $databases= gci SQLServer:\SQL\$ServerName\default\Databases | select name   New-PSDrive -Name $ServerName -PSProvider SqlServer -Root SQLServer:\SQL\$ServerName\default\Databases cd $ServerName`: e New-PSDrive -Name $ServerName -PSProvider SqlServer -Root SQLServer:\SQL\$ServerName\default\Databases -Credential (Get-Credential)

#SQL Convert Date Time

http://www.connectsql.com/2011/04/sql-server-basics-todate-function-in.html

http://linesofcode.net/snippets/45

DECLARE @MyDate DATETIME SELECT @MyDate = '01/JAN/09' SELECT CONVERT(VARCHAR(20),@MyDate,106) where dateadd(second, [Time]/1000 - 7*60*60, '19700101') > dateadd(day,-7,getdate())

Select dateadd(second, [Time]/1000 - 7*60*60, '19700101') as FormatTime  from blah blah blah SELECT LEN(ColumnName) AS Length, * FROM Table SELECT SUBSTRING(ServerName, 37, 3) AS ProductCode

#SQL query for Alerts with a "Log" Source ID in the last 7 days with a "GBL" product code $LogGBLAlertsSQL =@" SELECT dateadd(second, [Time]/1000 - 7*60*60, '19700101') as FormatedTime     ,SUBSTRING(ServiceID, 37, 3) AS ProductCode, *    FROM [Alerts_TBL]    WHERE [SourceID] = 7      and dateadd(second, [Time]/1000 - 7*60*60, '19700101') > dateadd(day,-7,getdate())  and SUBSTRING(ServiceID, 37, 3) = 'gbl' "@  $CommandStartTime = Get-Date $LogGBLAlerts = Invoke-Command -Session $SQLSession -ArgumentList $LogGBLAlertsSQL  -ScriptBlock {     param($LogGBLAlertsSQL)     Invoke-Sqlcmd -ServerInstance SQLServerName -Query $LogGBLAlertsSQL } $CommandStopTime = Get-Date $CommandRunTime = $CommandStopTime -$CommandStartTime Write-host "Command completed in $([Math]::Round($CommandRunTime.TotalMinutes,0)) Minutes"

Get DefaultFile Path

I was able to find the default file property but was frustrated that it was empty. 

http://sqlblog.com/blogs/allen_white/archive/2009/02/19/finding-your-default-file-locations-in-smo.aspx

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')  | out-null $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "Localhost" $fileloc = $s.Settings.DefaultFile $logloc = $s.Settings.DefaultLog if ($fileloc.Length -eq 0) {     $fileloc = $s.Information.MasterDBPath } if ($logloc.Length -eq 0) {     $logloc = $s.Information.MasterDBLogPath }

Get list of Database Details

SELECT d.name as DBName, m.name as LogicalName, m.file_id, m.physical_name AS current_file_location

FROM sys.master_files m, sys.databases d

where m.database_id = d.database_id

and m.database_id > 4

$StartTime = Get-Date Add-PSSnapin SqlServerCmdletSnapin100 Add-PSSnapin SqlServerProviderSnapin100 $servername = "sqlserver" $instance = "default"  $databases = gci sqlserver:\sql\$servername\$instance\databases | select name foreach ($db in $databases) {     $dbname = $db.name     if(Test-Path "sqlserver:\sql\$servername\$instance\databases\$dbname")      {         $SQLResults = Invoke-Sqlcmd -ServerInstance $ServerName -Query "select name, filename, groupid from [$dbname].sys.sysfiles"         $DBMdfInfo = $SQLResults  where {$_.groupid -eq "1"}         $DBLdfInfo = $SQLResults  where {$_.groupid -eq "0"}         $db | Add-Member  -membertype noteproperty -name MDFLogicalName -Value $($DBMdfInfo.name)         $db | Add-Member  -membertype noteproperty -name MDFFileName -Value $($DBMdfInfo.filename)         $db | Add-Member  -membertype noteproperty -name LDFLogicalName -Value $($DBLdfInfo.name)         $db | Add-Member  -membertype noteproperty -name LDFFileName -Value $($DBLdfInfo.filename)         Write-Host "$dbname"         #Build SQL restore query text         #$SQLRestoreQuery += "restore "     }      }  $databases | ft $databases | Export-Csv -NoTypeInformation C:\Temp\DatabaseInfo_$servername.csv  #$dbname = "fatest" #get-item sqlserver:\sql\$servername\$instance\databases\$dbname | select name, LogFiles  $StopTime = Get-Date $ElapsedTime =$StopTime - $StartTime  Write-host "Script completed in $([Math]::Round($ElapsedTime.TotalSeconds,0)) Seconds `r`n" #$message += "Script completed in $([Math]::Round($ElapsedTime.TotalSeconds,0)) Seconds `r`n"

Playing with GUI to get list of Databases

########################################################################

# Code Generated By: SAPIEN Technologies, Inc., PrimalForms 2009 v1.1.11.0

# Generated On: 8/5/2011 1:46 PM

# Generated By: Kevin Curran

# Organization: Disorganized

########################################################################

#----------------------------------------------

#region Application Functions

#----------------------------------------------

function OnApplicationLoad {

#Note: This function runs before the form is created

#Note: To get the script directory in the Packager use: Split-Path $hostinvocation.MyCommand.path

#Note: To get the console output in the Packager (Windows Mode) use: $ConsoleOutput (Type: System.Collections.ArrayList)

#Important: Form controls cannot be accessed in this function

#TODO: Add snapins and custom code to validate the application load

# Add-PSSnapin SqlServerProviderSnapin100

# Add-PSSnapin SqlServerCmdletSnapin100

return $true #return true for success or false for failure

}

function OnApplicationExit {

#Note: This function runs after the form is closed

#TODO: Add custom code to clean up and unload snapins when the application exits

$script:ExitCode = 0 #Set the exit code for the Packager

}

#endregion

#----------------------------------------------

# Generated Form Function

#----------------------------------------------

function GenerateForm {

#----------------------------------------------

#region Import Assemblies

#----------------------------------------------

[void][reflection.assembly]::Load("System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")

[void][reflection.assembly]::Load("System.Drawing, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")

[void][reflection.assembly]::Load("mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")

[void][reflection.assembly]::Load("System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")

[void][reflection.assembly]::Load("System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")

#endregion

#----------------------------------------------

#region Generated Form Objects

#----------------------------------------------

[System.Windows.Forms.Application]::EnableVisualStyles()

$form1 = New-Object System.Windows.Forms.Form

$listbox1 = New-Object System.Windows.Forms.ListBox

$combobox1 = New-Object System.Windows.Forms.ComboBox

$button1 = New-Object System.Windows.Forms.Button

$InitialFormWindowState = New-Object System.Windows.Forms.FormWindowState

#endregion Generated Form Objects

#----------------------------------------------

# User Generated Script

#----------------------------------------------

$FormEvent_Load={

#TODO: Initialize Form Controls here

$ServerName="SQL2008"

}

$handler_button1_Click={

#TODO: Place custom script here

$databases= gci SQLServer:\SQL\$ServerName\default\Databases | select name

#$dbtext=foreach ($database in $databases) {$database.name + "`r`n"}

#$textbox1.Text = $dbtext

foreach ($database in $databases)

{

$combobox1.Items.Add($database.name)

$listbox1.Items.Add($database.name)

}

$listbox1

}#end handler_button1_Click

#----------------------------------------------

# Generated Events

#----------------------------------------------

$Form_StateCorrection_Load=

{

#Correct the initial state of the form to prevent the .Net maximized form issue

$form1.WindowState = $InitialFormWindowState

}

#----------------------------------------------

#region Generated Form Code

#----------------------------------------------

#

# form1

#

$form1.Controls.Add($listbox1)

$form1.Controls.Add($combobox1)

$form1.Controls.Add($button1)

$form1.ClientSize = New-Object System.Drawing.Size(409,431)

$form1.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation 

$form1.Name = "form1"

$form1.Text = "Primal Form"

$form1.add_Load($FormEvent_Load)

#

# listbox1

#

$listbox1.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation 

$listbox1.FormattingEnabled = $True

$listbox1.Location = New-Object System.Drawing.Point(12,43)

$listbox1.Name = "listbox1"

$listbox1.SelectionMode = [System.Windows.Forms.SelectionMode]::MultiSimple 

$listbox1.Size = New-Object System.Drawing.Size(222,147)

$listbox1.TabIndex = 2

#

# combobox1

#

$combobox1.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation 

$combobox1.FormattingEnabled = $True

$combobox1.Location = New-Object System.Drawing.Point(12,15)

$combobox1.Name = "combobox1"

$combobox1.Size = New-Object System.Drawing.Size(121,21)

$combobox1.TabIndex = 1

#

# button1

#

$button1.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation 

$button1.Location = New-Object System.Drawing.Point(264,13)

$button1.Name = "button1"

$button1.Size = New-Object System.Drawing.Size(98,23)

$button1.TabIndex = 0

$button1.Text = "Get Databases"

$button1.UseVisualStyleBackColor = $True

$button1.add_Click($handler_button1_Click)

#endregion Generated Form Code

#----------------------------------------------

#Save the initial state of the form

$InitialFormWindowState = $form1.WindowState

#Init the OnLoad event to correct the initial state of the form

$form1.add_Load($Form_StateCorrection_Load)

#Show the Form

return $form1.ShowDialog()

} #End Function

#Call OnApplicationLoad to initialize

if(OnApplicationLoad -eq $true)

{

#Create the form

GenerateForm | Out-Null

#Perform cleanup

OnApplicationExit

}