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

}

Subpages (1): SQL Restore

Comments

Grr	Search this site